Change of database query to match change in specs

I built a CRM for a charitable organization a year ago, and am now tasked to make changes to it now that they have started to implement it and are changing some of their practices.

One of the changes is to move from a single care planner per client to several care planners per client. I am having issues changing my mySQL query to match the new requirements. The tables involved are ‘clients’ and ‘assessments’. I have added a lookup table to deal with the new many-to-one relationship, careplanners_clients. The coordinators-clients relationship will remain one-to-one.

This is my old query:

SELECT c.id AS client_id,
   	                               c.careplanner_id, 
					c.coordinator_id, 
					c.case_status, 
					c.account_num, 
					c.title, 
					c.first_name, 
					c.middle_name, 
					c.last_name, 
					a.id AS assessment_id
				FROM clients AS c 
				LEFT JOIN assessments AS a ON a.client_id = c.id
				WHERE (c.careplanner_id = $user_id
				OR c.coordinator_id = $user_id)
				AND c.case_status = 'Active'
				ORDER BY c.last_name ASC

I think I will need to replace the c.careplanner_id = $user_id with a JOIN careplanners_clients AS cc ON cc.careplanner_id = $user_id, but then how do I combine that with the OR c.coordinator_id = $user_id in the WHERE clause?

This is what I’ve tried (there is an error but I can’t find it):

SELECT c.id AS client_id, 
	c.careplanner_id, 
	c.coordinator_id, 
	c.case_status, 
	c.account_num, 
	c.title, 
	c.first_name, 
	c.middle_name, 
	c.last_name, 
	assessments.id AS assessment_id
FROM clients AS c 
LEFT JOIN assessments ON assessments.client_id = c.id
((JOIN careplanners_clients AS cc ON cc.careplanner_id = $user_id)
OR (WHERE c.coordinator_id = $user_id)) 
AND c.case_status = 'Active'
ORDER BY c.last_name ASC

okay, let’s back up one step

are you sure your old query is actually working?

because it uses both the c and clients table aliases, and i’m pretty sure that would generate an error

secondly, could you state in words what the new WHERE criteria should be? i.e. how to decide which clients?

finally, do you want the query to return ~all~ careplanner assessments for the selected clients, or only the assessments by the single careplanner who happens to be the logged-in $user_id?

Okay, I fixed the alias. Sorry about that. I did not use it that way. That was just a case of rushing and copying and pasting an older version and missing some of the changes.

I am looking for all active clients (including their assessments - each client only has one assessment done by a single care planner who is with a single coordinator) for a given user who is either the client’s care planner or the client’s coordinator, not any other user role. So for a logged in user, if they are a care planner or a coordinator, get all their clients. The given information is the logged in user’s id.

and show how many assessments for each client?

A client gets assessed only once. Sorry I didn’t make that clear.

can you see why i’m confused?

you have a one-to-many table for assessments, yes or no?

No. My junction table is for careplanners_clients. So I have a clients table, an assessments table and a careplanners_clients junction table.

But I have a LEFT JOIN for assessments because I do not want the clients who have not yet been assessed.

I have found one error - there is no longer a careplanner_id in the clients table.

okay, i think i get it now

back later unless someone else fixes your query first

in the meantime, could you confirm how many of each client’s careplanners to return in the query?

The $user_id is the id of either a care planner or a coordinator (2 of the 6 roles used in the users table). So given a certain $user_id, I want a list of all that user’s clients. So I am only referring to one care planner, or one coordinator. I am not returning care planners, I am returning clients. Hope that clarifies the scenario.

I think I got the right query. This is giving me the expected results:

SELECT clients.id AS client_id, 
	clients.case_status, 
	clients.account_num, 
	clients.title, 
	clients.first_name, 
	clients.middle_name, 
	clients.last_name, 
	assessments.id AS assessment_id
FROM clients
LEFT JOIN assessments ON assessments.client_id = clients.id 
JOIN careplanners_clients ON careplanners_clients.client_id = clients.id 
WHERE (careplanners_clients.careplanner_id = $user_id 
AND careplanners_clients.client_id = clients.id
OR clients.coordinator_id = $user_id)
AND clients.case_status = 'Active'
ORDER BY clients.last_name ASC

One thing I’m confused about here, is why I had to have careplanners_clients.client_id = clients.id in a JOIN and also in a WHERE clause to make it work.

because without it, the default precedence evaluation of your ANDs and ORs in the WHERE clause don’t work correctly

pull that extra condition out of the WHERE clause, and use parentheses to clarify the ANDs and ORs logic

FROM clients INNER JOIN careplanners_clients ON careplanners_clients.client_id = clients.id LEFT OUTER JOIN assessments ON assessments.client_id = clients.id WHERE ( careplanners_clients.careplanner_id = $user_id OR clients.coordinator_id = $user_id ) AND clients.case_status = 'Active'

another way of writing the same thing is –

FROM clients INNER JOIN careplanners_clients ON careplanners_clients.client_id = clients.id LEFT OUTER JOIN assessments ON assessments.client_id = clients.id WHERE $user_id IN ( careplanners_clients.careplanner_id , clients.coordinator_id ) AND clients.case_status = 'Active'

2 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.