3. Querying in QCubed / * QCubed Query: Conditional Joins

Conditional Joins with QQ::expand() and QQ::expandAsArray()

Sometimes, you find yourself in a situation when you want to issue a query for ALL items in a given table, and only some information in another table.

For example, let's say you have a list of persons, and a related list of logins. Only some of the persons have logins; some of the logins are disabled. Your task is to show the name of every person, and next to it, show their login information, but only if their login is actually enabled.

Before you found out about conditional joins, you had several options:

  1. Do a LEFT JOIN on the login table; write a database-specific, somewhat convoluted IF statement that might look like IF(login.is_enabled = 1, login.username, ""). But what if you want to show more than just that one column? Write an IF statement for every single output column... Ehh. Plus, not portable across databases.
  2. Get a list of all persons, then also get a list of all logins, then merge the two using PHP. Works with QQuery, but incurs an overhead of extra processing.

As you'd expect, there's a better way. When you use QQ::Expand, you can specify conditions on the table with which you want to join, and get only those values that you care about. QQ::Expand clauses produce a left join - so if a row of a table with which you are joining does not have a matching record, the left side of your join will still be there, and the right side will contain nulls.

Conditional joins only impact how tables are joined together, and so the conditions must apply only to the joined table.

Names of every person, their username, and open projects they are managing.

PROFILING INFORMATION FOR DATABASE CONNECTION #1: 2 queries performed. Please click here to view profiling detail