Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
66 lines (50 sloc) 3.58 KB

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,

ID First Last Pref Value
12 John Doe last_login 01/01/09
12 John Doe user_type Editor
12 John Doe eyes Blue
15 Susan Smith last_login 12/31/08
15 Susan Smith user_type User

Left Joins

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:

ID First Last Pref Value
12 John Doe last_login 01/01/09
12 John Doe user_type Editor
12 John Doe eyes Blue
15 Susan Smith last_login 12/31/08
15 Susan Smith user_type User
16 Bob Barker NULL NULL

because the user whose ID is 16 has no preferences set in the user_prefs table.

You can’t perform that action at this time.