/ Get a db connection.
$db = JFactory::getDbo();
// Create a new query object.
$query = $db->getQuery(true);
// Select all articles for users who have a username which starts with 'a'.
// Order it by the created date.
// Note by putting 'a' as a second parameter will generate `#__content` AS `a`
$query
->select($db->quoteName(array('a.*', 'b.username', 'b.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');
// Reset the query using our newly populated query object.
$db->setQuery($query);
// Load the results as a list of stdClass objects (see later for more options on retrieving data).
$results = $db->loadObjectList();
The join method above enables us to query
both the content and user tables, retrieving articles with their author
details. There are also convenience methods for joins:
We can use multiple joins to query across
more than two tables:
$query
->select($db->quoteName(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($db->quoteName('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($db->quoteName(array('a.*', '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');
EmoticonEmoticon