Question about joined tables and prefilters

[cookn22]

This is a little long and involved, but this was the best way I could think of to explain my problem! I'm using the most recent rev of fabrik 1.0.6.

I have a 'client' table that's joined in a one-to-many relationship with a 'cases' table by client_id. There is also a 'case_color' table that has a 'case_id' which corresponds to a case, and there can be multiple 'case_color' entries per case. So, each individual client can have one or more cases, and each of these cases can have zero or more colors. (client_id case_id and color_id are all autoincrementing primary keys)

I want to set up a fabrik table that will show every case that a client has, but only the latest entry in the case_color columns per case (if there is an case_color entry at all).

Here are some SQL queries of the test data in my database, which might make this a little clearer. I'm using (and stuck with) MySQL4.0.18:

This query gets all the rows:

SELECT client.client_id, cases.case_id, case_color.color_id FROM client LEFT JOIN cases ON client.client_id = cases.client_id LEFT JOIN case_color ON cases.case_id = case_color.case_id

Gives you:

Array
(
[0] => Array
(
[client_id] => 18
[case_id] => 20
[color_id] =>
)

[1] => Array
(
[client_id] => 19
[case_id] => 21
[color_id] => 17
)

[2] => Array
(
[client_id] => 19
[case_id] => 21
[color_id] => 18
)

[3] => Array
(
[client_id] => 19
[case_id] => 22
[color_id] => 19
)

)

Now this query gets all cases per client but for the color_id it only gets the most recent color_id per case. I'm not very good with SQL and I figured this out through trial and error, to be honest, so I'm not sure why its working but it does!

SELECT client.client_id, cases.case_id, MAX(case_color.color_id) FROM client LEFT JOIN cases ON client.client_id = cases.client_id LEFT JOIN case_color ON cases.case_id = case_color.case_id GROUP BY cases.case_id

Array
(
[0] => Array
(
[client_id] => 18
[case_id] => 20
[MAX(case_color.color_id)] =>
)

[1] => Array
(
[client_id] => 19
[case_id] => 21
[MAX(case_color.color_id)] => 18
)

[2] => Array
(
[client_id] => 19
[case_id] => 22
[MAX(case_color.color_id)] => 19
)

)

Can I get a table that is like the second SQL query, with each case per client and the greatest color_id per case, by using prefilters or some other method? Ideally, the fabrik table would (visually, at least) be grouped by client_id and ordered by the case_id, but I don't know if that's feasible.


Sorry if this doesn't make much sense!

 

[cheesegrits]

You might be able to do this using a 'subquery' filter. Set your filter up like this:

WHERE
Field: client_id
Condition: IN
Value:

SELECT client_id FROM (SELECT client.client_id, cases.case_id, MAX(case_color.color_id) FROM client LEFT JOIN cases ON client.client_id = cases.client_id LEFT JOIN case_color ON cases.case_id = case_color.case_id GROUP BY cases.case_id)

Eval: subquery
Apply Filter to: Everybody (or whatever you need)

I don't have a similar table structure to test on, but I think the above might work.

-- hugh

 

[cookn22]

Hmm, I'm getting a syntax error. I think it's because I have MySQL 4.0 and not 4.1. Does the subquery filter require 4.1? I think the subquery functionality might be a 4.1 and up feature, but I'm not positive.

I'm going to shoot an e-mail off to the tech guys and see how feasible it would be to get an upgrade.

 

[cheesegrits]

Yes, subqueries require 4.1 or greater, as per:

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

"Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported"

-- hugh

 

[cookn22]

That's what I figured. I'm still waiting on a reply from the Unix guys on whether or not I can get MySQL upgraded to 4.1.x. I'll open up the thread again if I do get the upgrade and can try out your suggestion.

 

[cheesegrits]

Might as well shoot for 5.x while you are asking them for upgrades.

-- hugh