一个具体的例子说明如何在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");