We can use multiple joins to query across more than two tables:
$query ->select(array('a.*', 'b.username', 'b.name', 'c.*', 'd.*')) ->from($db->quoteName('#__content', 'a')) ->join('INNER', $db->quoteName('#__users', 'b') . ' ON (' . $db->quoteName('a.created_by') . ' = ' . $db->quoteName('b.id') . ')') ->join('LEFT', $db->quoteName('#__user_profiles', 'c') . ' ON (' . $db->quoteName('b.id') . ' = ' . $db->quoteName('c.user_id') . ')') ->join('RIGHT', $db->quoteName('#__categories', 'd') . ' ON (' . $db->quoteName('a.catid') . ' = ' . $db->quoteName('d.id') . ')') ->where($db->quoteName('b.username') . ' LIKE \'a%\'') ->order($db->quoteName('a.created') . ' DESC');
Notice how chaining makes the source code much more readable for these longer queries.
In some cases, you will also need to use the AS clause when selecting items to avoid column name conflicts. In this case, multiple select statements can be chained in conjunction with using the second parameter of $db->quoteName.
$query ->select('a.*') ->select($db->quoteName('b.username', 'username')) ->select($db->quoteName('b.name', 'name')) ->from($db->quoteName('#__content', 'a')) ->join('INNER', $db->quoteName('#__users', 'b') . ' ON (' . $db->quoteName('a.created_by') . ' = ' . $db->quoteName('b.id') . ')') ->where($db->quoteName('b.username') . ' LIKE \'a%\'') ->order($db->quoteName('a.created') . ' DESC');
A second array can also be used as the second parameter of the select statement to populate the values of the AS clause. Remember to include nulls in the second array to correspond to columns in the first array that you don't want to use the AS clause for:
$query ->select(array('a.*')) ->select($db->quoteName(array('b.username', 'b.name'), array('username', 'name'))) ->from($db->quoteName('#__content', 'a')) ->join('INNER', $db->quoteName('#__users', 'b') . ' ON (' . $db->quoteName('a.created_by') . ' = ' . $db->quoteName('b.id') . ')') ->where($db->quoteName('b.username') . ' LIKE \'a%\'') ->order($db->quoteName('a.created') . ' DESC');
Comments