Table Joins and Complex Conditions¶
All this simple Active Record-style access is nice, but sometimes you need to perform substantially more complex queries, with table joins. The procedure is much the same as in the basic queries. Whenever you are dealing with queries that involve more than one table, it is a good idea to get in the habit of using full table notation for field names, e.g. “users.firstname” instead of “firstname”. This can avoid substantial confusion when joined tables contain identically-named fields.
The set_tablename() method defines the primary table for the query (the significance of being “primary” comes into play when generating objects from the result array).
If you want to perform simple cross-joins, the add_tablename() method will append one or more databases to the query. Any time you employ additional tables you will also want to include a “where” clause that restricts the results to the appropriate matching rows in the secondary table. So, for example, you could get a list of users AND their preferences with something like this:
$oracle = new UserOracle(); // sets the master tablename to "users" in the constructor $oracle->add_tablename('user_prefs'); $oracle->set_selectfields('users.*,user_prefs.preference,user_prefs.value'); // We could have done $oracle->add_selectfields('user_prefs.preference,user_prefs.value'); too $oracle->set_where_clause('users_prefs.user_id = users.objectid'); $oracle->get_record(false); $results = $oracle->response_array;
The raw SQL query generated by the code above would be:
SELECT users.*, user_prefs.preference, user_prefs.value FROM users JOIN user_prefs WHERE user_prefs.user_id = users.objectid
and you would receive an array of results. However, the number of results would NOT correspond to the number of users. It would equal
number of users * number of preferences for each user
So you would have, for example,
If you’re very familiar with SQL, you would realize that the query above would return no results for a user if the user had NO preferences set. You would not even see their ID, first, or last name. To get results back from a JOIN that includes records with no rows in the joined table, you need to use a LEFT JOIN. A left join basically says “give me results for ALL users no matter what, and if they have no preferences, return NULL in the corresponding fields.”
The query above, rewritten as a left join, would look like this:
$oracle = new UserOracle(); $oracle->set_selectfields('users.*,user_prefs.preference,user_prefs.value'); $oracle->set_left_join('user_prefs ON users_prefs.user_id = users.objectid'); $oracle->get_record(false); $results = $oracle->response_array;
and the results might look like this:
because the user whose ID is 16 has no preferences set in the user_prefs table.