Skip to content

Instantly share code, notes, and snippets.

@gunjanpatel
Last active November 14, 2022 12:26
Show Gist options
  • Save gunjanpatel/8663333 to your computer and use it in GitHub Desktop.
Save gunjanpatel/8663333 to your computer and use it in GitHub Desktop.
Write subquery in Joomla 3 using JDatabase method.

Here is an example of how to write subquery in Joomla! 3 using JDatabase method.

<?php
// Initialize variables.
$db       = JFactory::getDbo();
$subQuery = $db->getQuery(true);
$query    = $db->getQuery(true);

// Create the base subQuery select statement.
$subQuery->select('subCheckIn')
	->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 . ')')
	->order($db->quoteName('ordering') . ' ASC');

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

try
{
	$result = $db->loadObjectList();
}
catch (RuntimeException $e)
{
	throw new RuntimeException($e->getMessage(), $e->getCode());
}

// Or write Inline subquery this way
$query->select('*')
	->from($db->quoteName('#__table'))
	->where($db->quoteName('state') . ' = ' . $db->quote('1'))
	->where($db->quoteName('subCheckIn') . ' IN (' . function(){
		return $db->getQuery(true)
			  ->select('subCheckIn')
			  ->from($db->quoteName('#__sub_table'))
			  ->where($db->quoteName('subTest') . ' = ' . $db->quote('1'))
	} . ')')
	->order($db->quoteName('ordering') . ' ASC');

By 6|_||\|74|\| |>4731

@jameswadsworth
Copy link

Very useful. Thanks

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment