Joomla 使用子查詢

一個具體的例子說明如何在JDatabase中使用子查詢

使用子查詢

典型代碼如下：

<?php
// 得到數(shù)據(jù)庫對象
$db       = JFactory::getDbo();<br><br>//創(chuàng)建兩個查詢對象
$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');
 
// 設(shè)置查詢
$db->setQuery($query);
try
{
    $result =$db->loadObjectList();
}
catch (RuntimeException$e)
{
    throw new RuntimeException($e->getMessage(),$e->getCode());
}

 代碼示例2：

//創(chuàng)建一個基礎(chǔ)查詢
$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");