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.