一个具体的例子说明如何在JDatabase中使用子查询

使用子查询

典型代码如下：

<?php
// 得到数据库对象
$db       = JFactory::getDbo();

//创建两个查询对象
$subQuery = $db->getQuery(true);
$query    = $db->getQuery(true);

// 子查询SQL
$subQuery->select('*')
	->from($db->quoteName('#__sub_table'))
	->where($db->quoteName('subTest') . ' = ' . $db->quote('1'));

// 主查询SQL
$query->select('*')
	->from($db->quoteName('#__table'))
	->where($db->quoteName('state') . ' = ' . $db->quote('1'))
	->where($db->quoteName('subCheckIn') . ' IN (' . $subQuery . ')')
	->order($db->quoteName('ordering') . ' ASC');

// 设置查询
$db->setQuery($query);
try
{
	$result = $db->loadObjectList();
}
catch (RuntimeException $e)
{
	throw new RuntimeException($e->getMessage(), $e->getCode());
}

 代码示例2：

//创建一个基础查询
		$db    = $this->getDbo();
		$query = $db->getQuery(true);
		$user  = JFactory::getUser();
		
		//需要查询的字段
		$query->select(
			$this->getState(
				'list.select',
				'DISTINCT f.id,f.title,f.no,f.level'
			)
		);
		$query->from( '#__zmaxflow_fproject AS f');
		$query->where("f.flow_id IN(".implode(',',$flowId).")");
		$query->where("f.state!=9");
		
		$subQuery = $db->getQuery(true);
		$subQuery->select("DISTINCT flow_id,data_id")->from("#__zmaxflow_fdata_map");
		
		//获得来文单位和时间
		$query->select("m.data_id");
		$query->leftJoin("(".$subQuery.") AS m ON m.flow_id=f.id");