pattern help

[Destramic]

i have two CONCAT_GROUPS in my sql query, 1 being the users trades and the other being the trades required for the a particular task.

What im tyring to do is to find if the user has the correct trade for a particular task.

here is what ive got so far which does work, but doesnt match the words as i want, for instance if @user_trades = REPLACE('plumb,electrician', ',', '|'), it will still match for plumb

SELECT @user_trades := REPLACE('plumber,electrician', ',', '|'),   // regex
       @task_trades := 'painter,plumber',   // string
@task_trades REGEXP @user_trades

how can i make the regex match a trade 100% or nothing

 

thank you

[requinix]

Determine if they have the correct trade before the grouping.

What's the rest of the query?

[Destramic]

maybe i should post in the mysql section but i really feel this is a regex issue.

my query is just to count unallocated/uncompleted tasks and appointments

here is the query

SELECT SUM(task_appointment.count) AS `count`
       FROM ((SELECT COUNT(pt.property_task_id) AS `count`,
				     p.gas_service_expiry_date AS `priority_date`,
                     d.department,
                     GROUP_CONCAT(t.trade) AS `trades`,
                     pt.status
                     FROM property_tasks pt
				     INNER JOIN properties p ON p.property_id = pt.property_id
                     INNER JOIN task_types tt ON tt.task_type_id = pt.task_type_id
                     INNER JOIN trade_task_types ttt ON ttt.task_type_id = tt.task_type_id
                     INNER JOIN trades t ON t.trade_id = ttt.trade_id
                     INNER JOIN departments d ON d.department_id = pt.department_id
                     INNER JOIN property_appointments pa ON pa.property_task_id = pt.property_task_id
                     AND tt.task_type = "Gas Service" 
                     AND d.department = "Gas Servicing"
                     AND IF(psa.completed_timestamp, psa.completed_timestamp, psa.end_date) < NOW()
)
UNION ALL
(SELECT COUNT(pt.property_task_id) AS `count`,
        DATE_FORMAT(DATE_ADD(pt.created_timestamp, INTERVAL pt.priority_days DAY), "%Y-%m-%d") AS `priority_date`,
        d.department,
		GROUP_CONCAT(t.trade) AS `trades`,
        pt.status
        FROM property_tasks pt
        INNER JOIN properties p ON p.property_id = pt.property_id
		INNER JOIN task_types tt ON tt.task_type_id = pt.task_type_id
        INNER JOIN trade_task_types ttt ON ttt.task_type_id = tt.task_type_id
        INNER JOIN trades t ON t.trade_id = ttt.trade_id
        INNER JOIN departments d ON d.department_id = pt.department_id
		INNER JOIN property_appointments pa ON pa.property_task_id != pt.property_task_id
        AND tt.task_type != "Gas Service" 
        AND d.department != "Gas Servicing"
)) task_appointment
INNER JOIN (SELECT GROUP_CONCAT(d.department) AS `departments`,
                   GROUP_CONCAT(t.trade) AS `trades`
		    FROM users u
			INNER JOIN trade_departments td ON td.department_id = u.user_id
            INNER JOIN departments d ON d.department_id = td.user_id
            INNER JOIN user_trades ut ON ut.user_id = u.user_id
            INNER JOIN trades t ON t.trade_id = ut.trade_id
		    WHERE u.user_id = 1
) user
WHERE user.departments REGEXP REPLACE(task_appointment.department, ',', '|') > 0
AND user.trades REGEXP REPLACE(task_appointment.trades, ',', '|') > 0
AND status = "Active"
ORDER BY task_appointment.priority_date ASC

 

note the unions are almost simular but draw different rows

1st being gas servicing under certian conditions and 2nd anything else

 

thank you

[kicken]

This certainly seems like a Mysql problem rather than a Regex problem to me.

Can you post the relevant table structures?  Some of your joins don't make sense, eg:

INNER JOIN trade_departments td ON td.department_id = u.user_id
INNER JOIN departments d ON d.department_id = td.user_id

Why are you matching department_id to user_id?

If you want to find out what users can perform the trades needed for a property_task you should be joining those tables via the common trades.  At the end you can GROUP_CONCAT if you want, but that shouldn't be part of your joining process.

 

[Destramic]

thank kicken for seeing my errors, what on earth was i donig with the regex! (head shaking)

ive made the changes to my query now which gets the users assigned department and task type id's and compares it with the the ids from the appointments

 

SELECT SUM(task_appointment.count) AS `count`
FROM ((SELECT COUNT(pt.property_task_id) AS `count`,
				     p.gas_service_expiry_date AS `priority_date`,
                     d.department_id,
		             tt.task_type_id,
                     pt.status
                     FROM property_tasks pt
				     INNER JOIN properties p ON p.property_id = pt.property_id
                     INNER JOIN task_types tt ON tt.task_type_id = pt.task_type_id
                     INNER JOIN trade_task_types ttt ON ttt.task_type_id = tt.task_type_id
                     INNER JOIN trades t ON t.trade_id = ttt.trade_id
                     INNER JOIN departments d ON d.department_id = pt.department_id
                     INNER JOIN property_appointments pa ON pa.property_task_id = pt.property_task_id
                     WHERE tt.task_type = "Gas Service" 
                     AND d.department = "Gas Servicing"
                     AND IF(pa.completed_timestamp, pa.completed_timestamp, pa.end_date) < NOW()
			)
UNION ALL
(SELECT COUNT(pt.property_task_id) AS `count`,
        DATE_FORMAT(DATE_ADD(pt.created_timestamp, INTERVAL pt.priority_days DAY), "%Y-%m-%d") AS `priority_date`,
        d.department_id,
		tt.task_type_id,
        pt.status 
        FROM property_tasks pt
        INNER JOIN properties p ON p.property_id = pt.property_id
		INNER JOIN task_types tt ON tt.task_type_id = pt.task_type_id
        INNER JOIN trade_task_types ttt ON ttt.task_type_id = tt.task_type_id
        INNER JOIN trades t ON t.trade_id = ttt.trade_id
        INNER JOIN departments d ON d.department_id = pt.department_id
		INNER JOIN property_appointments pa ON pa.property_task_id != pt.property_task_id
        WHERE tt.task_type != "Gas Service" 
        AND d.department != "Gas Servicing"
)) task_appointment
INNER JOIN (SELECT u.user_id,
                   GROUP_CONCAT(d.department_id) AS `department_ids`,
                   GROUP_CONCAT(tt.task_type_id) AS `task_type_ids`
		    FROM users u
			INNER JOIN trade_departments td ON td.user_id = u.user_id
            INNER JOIN departments d ON d.department_id = td.department_id
            INNER JOIN user_trades ut ON ut.user_id = u.user_id
            INNER JOIN trades t ON t.trade_id = ut.trade_id
            INNER JOIN trade_task_types ttt ON ttt.trade_id = t.trade_id
            INNER JOIN task_types tt ON tt.task_type_id = ttt.task_type_id
) user
WHERE status = "Active"
AND task_appointment.department_id IN (user.department_ids)
AND task_appointment.task_type_id IN (user.task_type_ids)
AND user.user_id = 1
ORDER BY task_appointment.priority_date ASC

 

it comes back with the correct count which is good...any final thouhts?

 

thank you again

[benanamen]

Every time I have seen the use of UNION on the forums it ALWAYS involved bad database design. You might want to post your DB Schema for us to review. One thing that doesn't get talked about much is the use of database views. A view allows you to simplify complex queries among a few other benefits.

[Destramic]

the first select fetches properties from my properties table by thier gas expiry date, ie gas service, which is listed as a pre-task (no property_task entry). when this task is assigned to a trade it will have a property_task_id. so im selecting tasks which exists and dont yet exist for a gas service.

and the second select will select anything else for that particular deparment. eg. repair on boiler, fire etc.

it pull all the rows together, giving me the full list of task/pre-tasks for a selected department.

 

[benanamen]

A description is nice, but the schema will really tell whats going on.