Most efficient way to do a conditional mysql join on multiple tables? (Yii php framework)

Question

I have five tables:

1. tab_template
2. template_group
3. group
4. user_group
5. user

Tab_template's are organized into groups with the template_group relational table. Users's are organized into groups with the user_group relational table. Group's can be public or private (using a tinyint boolean column in the table).

I want to query for all of the tab_templates that are either:

1. In the same group as the user
2. Or in a Public group

Here is my current query:

SELECT * FROM tab_template t
LEFT JOIN template_group
ON template_group.tab_template_id=t.id
LEFT JOIN group
ON template_group.tab_template_id=group.id
LEFT JOIN user_group
ON TRUE
WHERE
group.private=0
OR
(template_group.group_id=user_group.group_id
AND
user_group.user_id=2)
GROUP BY t.id;

It works, and it's not SUPER slow per se, but it's hacky the way I join in the user_group table.

The problem is that I need to JOIN the user_group table for a conditional check, but I only need to do that conditional check IF the group is not private.

I know that instead of the third LEFT JOIN with the ON TRUE condition I could add another table to the FROM clause (FROM tab_template t, user_group ug)... but I can't do that because the way Yii's ActiveRecord class works with the DcCriteria I can't modify that part of the statement. I can edit just about any other part of the query but not the FROM clause. Check out the API here: http://www.yiiframework.com/doc/api/CDbCriteria So that's why I am JOINing the user_group table the way I am. Some Yii experts might be able to help me solve that problem, but I'm not sure my query will be faster by FROMing the tables instead of the JOINing them anyway.

Any help would be greatly appreciated! Thanks

Answer 1

I don't have mySql on this machine, so I can't test this:

SELECT
    TT.col1,
    TT.col2,
    ...
FROM
    Tab_Templates TT
WHERE
    EXISTS
    (
        SELECT *
        FROM
            Template_Groups TG
        WHERE
            TG.tab_template_id = TT.id AND
            TG.private = 0
    ) OR
    EXISTS
    (
        SELECT *
        FROM
            User_Groups UG
        INNER JOIN Template_Groups TG2 ON
            TG2.tab_template_id = TT.id AND
            TG2.group_id = UG.group_id
        WHERE
            UG.user_id = 2
    )

Answer 2

My recommendation for situations like these is to use a UNION:

SELECT t.* 
  FROM TAB_TEMPLATE t
  JOIN TEMPLATE_GROUP tg ON tg.tab_template_id = t.id
  JOIN GROUP g ON g.id = tg.tab_template_id  
              AND g.private = 0
UNION 
SELECT t.* 
  FROM TAB_TEMPLATE t
  JOIN TEMPLATE_GROUP tg ON tg.tab_template_id = t.id
  JOIN GROUP g ON g.id = tg.tab_template_id  
              AND g.private != 0
  JOIN USER_GROUP ug ON ug.group_id = g.id

Way easier to read, which makes it easier to maintain.