StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMost efficient way to do a conditional mysql join on multiple tables? (Yii php framework)
text
Body
copied!<p>I have five tables:</p> <ol> <li>tab_template</li> <li>template_group</li> <li>group</li> <li>user_group</li> <li>user</li> </ol> <p>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).</p> <p>I want to query for all of the tab_templates that are either:</p> <ol> <li>In the same group as the user</li> <li>Or in a Public group</li> </ol> <p>Here is my current query:</p> <p>SELECT * FROM <code>tab_template</code> <code>t</code><br> LEFT JOIN <code>template_group</code><br> ON <code>template_group</code>.<code>tab_template_id</code>=<code>t</code>.<code>id</code><br> LEFT JOIN <code>group</code><br> ON <code>template_group</code>.<code>tab_template_id</code>=<code>group</code>.<code>id</code><br> LEFT JOIN <code>user_group</code><br> ON TRUE<br> WHERE<br> <code>group</code>.<code>private</code>=0<br> OR<br> (<code>template_group</code>.<code>group_id</code>=<code>user_group</code>.<code>group_id</code><br> AND<br> <code>user_group</code>.<code>user_id</code>=2)<br> GROUP BY <code>t</code>.<code>id</code>; </p> <p>It works, and it's not SUPER slow per se, but it's hacky the way I join in the user_group table. </p> <p><strong>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.</strong></p> <p>I know that instead of the third LEFT JOIN with the ON TRUE condition I could add another table to the FROM clause (FROM <code>tab_template</code> <code>t</code>, <code>user_group</code> <code>ug</code>)... 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: <a href="http://www.yiiframework.com/doc/api/CDbCriteria" rel="nofollow noreferrer">http://www.yiiframework.com/doc/api/CDbCriteria</a> 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.</p> <p>Any help would be greatly appreciated! Thanks</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload