Joomla MySQL subquery using JDatabase
<?php
// See https://joomla.stackexchange.com/questions/1/method-for-creating-a-subquery-using-jdatabase
/*******
** v1
*******/
$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);
/*******
** v2
*******/
$query = $this->db->getQuery(true)
->select('a.*')
->subQuery()
->select('b.*')
->from('#__table_b AS b')
->as('subQueryResult')
->endSubQuery()
->from('#__table_a AS a');