Method for creating a subquery using JDatabase

Question

At http://docs.joomla.org/Selecting_data_using_JDatabase, there isn't a documented method for writing a subquery using JDatabase.

https://gist.github.com/gunjanpatel/8663333 exemplifies one way of accomplishing this with (a few bits omitted):

$subQuery = $db->getQuery(true);
$query    = $db->getQuery(true);

// Create the base subQuery select statement.
$subQuery->select('*')
    ->from($db->quoteName('#__sub_table'))
    ->where($db->quoteName('subTest') . ' = ' . $db->quote('1'));

// Create the base select statement.
$query->select('*')
    ->from($db->quoteName('#__table'))
    ->where($db->quoteName('state') . ' = ' . $db->quote('1'))
    ->where($db->quoteName('subCheckIn') . ' IN (' . $subQuery->__toString() . ')')
    ->order($db->quoteName('ordering') . ' ASC');

// Set the query and load the result.
$db->setQuery($query);

This seems like a good, plausible approach, but is there a better one?

Answer 1

Yes, as far as i'm concerned, the way you built the subquery is the one adopted by the majority of joomla's extensions developers.

I use that same method on some of my extensions and custom extensions made for clients.

There is no "official" way of doing this, but doing it as you showed lets you use the query builder and still retain a good amount of readability

Answer 2

AFAIK there isn't a built in way to do easy subqueries, which is probably a deficiency in the system and should be corrected via PR.

However, I see no issue with your example - seems reasonable enough.

~~~

Here's an example in response to @DavidFritsch's comment below. The more that I think about it though, the better I like the more simple approach displayed in the OP. It's more clear what's going on.

$query = $this->db->getQuery(true)
  ->select('a.*')
  ->subQuery()
    ->select('b.*')
    ->from('#__table_b AS b')
    ->as('subQueryResult')
  ->endSubQuery()
  ->from('#__table_a AS a');

Answer 3

There is also a way to execute queries which contain subqueries using the Joomla Platform API. The basic idea on how to use subqueries is based on gunjanpatel.

Here is an example for executing queries on Nested Set Models:

SQL query:

-- Find the Immediate Subordinates of a Node
SELECT node.title, (COUNT(parent.id) - (sub_tree.depth + 1)) AS depth
FROM lubd3_usergroups AS node,
        lubd3_usergroups AS parent,
        lubd3_usergroups AS sub_parent,
        (
                SELECT node.id, (COUNT(parent.id) - 1) AS depth
                FROM lubd3_usergroups AS node,
                        lubd3_usergroups AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.id = 1
                GROUP BY node.id
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.id = sub_tree.id
GROUP BY node.id
-- not showing the parent node
HAVING depth = 1
-- showing the parent node
-- HAVING depth <= 1
ORDER BY node.lft;

and the transformed query to be executed by Joomla:

// Create the subQuery select statement.
// Nested Set Queries: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
// CROSS JOIN: http://www.informit.com/articles/article.aspx?p=30875&seqNum=5
$subQuery->select(array('node.id', '(COUNT(parent.id) - 1) AS depth'))
    ->from($db->quoteName('#__usergroups') . 'node')
    ->join('CROSS', $db->quoteName('#__usergroups', 'parent'))
    ->where($db->quoteName('node.lft') . ' BETWEEN  ' . $db->quoteName('parent.lft') . ' AND ' . $db->quoteName('parent.rgt') . ' AND ' . $db->quoteName('node.id') . ' = ' . $db->quote('1'))
    ->group($db->quoteName('node.id'))
    ->order($db->quoteName('node.lft'));

// Create the base select statement.
$query->select(array('node.title', '(COUNT(parent.id) - (sub_tree.depth + 1)) AS depth'))
    ->from($db->quoteName('#__usergroups') . 'node')
    ->join('CROSS', $db->quoteName('#__usergroups', 'parent'))
    ->join('CROSS', $db->quoteName('#__usergroups', 'sub_parent'))
    ->join('CROSS', '(' . $subQuery .') AS sub_tree')
    ->where($db->quoteName('node.lft') . ' BETWEEN  ' . $db->quoteName('parent.lft') . ' AND ' . $db->quoteName('parent.rgt')
    . ' AND ' . $db->quoteName('node.lft') . ' BETWEEN  ' . $db->quoteName('sub_parent.lft') . ' AND ' . $db->quoteName('sub_parent.rgt')
    . ' AND ' . $db->quoteName('sub_parent.id') . ' = ' . $db->quoteName('sub_tree.id'))
    ->group($db->quoteName('node.id'))
    ->having($db->quoteName('depth') . ' = ' . $db->quote('1'))
    ->order($db->quoteName('node.lft'));

// Set the query and load the result.
$db->setQuery($query);
$rowList = $db->loadAssocList();

echo "<pre>";
print_r($rowList);
echo "</pre>";

Answer 4

I'll offer my version of the snippet then explain my justification and include quotes from the Joomla Coding Standards manual (which will be quoteblock formatted).

$subquery = $db->getQuery(true)
    ->select("checkin")
    ->from("#__sub_table")
    ->where("subTest = 1");

$query = $db->getQuery(true)
    ->select("*")
    ->from("#__table")
    ->where([
        "state = 1",
        "subCheckIn IN ({$subQuery})"
    ])
    ->order("ordering");

$db->setQuery($query);

Use Query chaining to connect a number of query methods, one after the other, with each method returning an object that can support the next method, This improves readability and simplifies the resulting code.

• I write the innermost queries first and progress to the outermost query. This allows me chain all query building methods directly to the getQuery() method. Effectively, the variable name is only written once while building the individual query.
  Here's a terrific example of some heavy query nesting (when I thought it was cute to line up the chaining arrows).

• I try to avoid making multiple select() and/or where() calls within the same query because I have seen it lead to the confusion of less experienced developers. Because these methods accept arrays, I find it more readable and better coding practice to employ them.

• and finally the most controversial topic...

  Table names and table column names should always be enclosed in the quoteName() method to escape the table name and table columns. Field values checked in a query should always be enclosed in the quote() method to escape the value before passing it to the database. Integer field values checked in a query should also be type cast to (int).

  I am very conflicted on this stance. When I first came to Joomla last year, I thought, I'm not going to make useless calls (no benefit to the stability, security, readability of the query) on static values! However, my employer likes the idea of toeing the Joomla line, and I have to admit that I generally have a high appreciation for the rules, so I have been hosing down my queries with quote(), (int), and quoteName() which also means heaps of string concatenation (all properly spaced). The end results of my work being horrendously bloated query blocks which even I have a hard time eyeballing. The worst/longest lines that don't lend themselves to vertical stacking are the join() calls because of the tablename, the alias, ON, then one or more conditions which may or may not require quoting. I can appreciate that this policy is implemented with security in mind for novice developers, but I sure would like it if this policy was somehow tempered with the sensibility that not all Joomla coders are ignorant copy-pasters. I mean, have a look at how clean and brief the code looks without the needless calls.

• As for the mopping up:

  • I nearly never use * in my SELECT clauses
  • I don't ever call __toString()
  • I don't quote integers, I cast them as integers
  • I don't write ASC because that is the default sorting direction
  • I make every effort to not use mysql keywords when creating new table names and column names
  • As a matter of personal preference I tend to use double quoting on my method's string arguments to maintain uniformity, distinguish from mysql's single quoting, and so that I can enjoy variable interpolation which I typical write with "complex syntax".
  • I use informative variable names and commenting to aid in the readability of my nested queries, and my code generally
  • I test my code before it leaves my custody