mysql - 将嵌套选择查询转换为 Drupal 7 db_select？

Question

我已经使用 Drupal 7 中的简单查询完成了我的数据库操作。但是我想要一些使用 Drupal 7 theme_table 的分页功能，因此我必须使用 Drupal 7 db_select ，但我无法这样做。

请尽快帮助我，下面是我想在db_select中转换的mysql查询:-

SELECT r.ROName, CONCAT(CONCAT(CONCAT(r.Address1, ' - ' , r.Pincode),' - ',d.Distname),' - ',s.StateName) as Address1,r.Phone1, r.Phone2, r.Fax, r.Phone3, r.Pincode, r.DistID,d.Distname, d.DistID, s.StateID, r.StateID, s.StateName 
FROM ROMaster r
JOIN DistrictMaster d ON r.DistID = d.DistID JOIN StateMaster s ON r.StateID = s.StateID  
WHERE SBUID =1 
ORDER BY r.ROName

Answer 1

我认为是这样的:

$query = db_select('ROMaster', "r");

$query->innerJoin("DistrictMaster", "d", "r.DistID=d.DistID");
$query->innerJoin("StateMaster", "s", "r.StateID = s.StateID");

$query->addField('r', 'ROName');
$query->addExpression('CONCAT(CONCAT(CONCAT(r.Address1, ' - ' ,
     r.Pincode), ' - ',d.Distname),' - ',s.StateName) as Address1')
$query->addField('r', 'Phone1');
$query->addField("r", "Phone2");
$query->addField("r", "Fax");
$query->addField("r", "Phone3");
$query->addField("r", "Pincode");
$query->addField("r", "DistID");
$query->addField("d", "Distname");
$query->addField("d", "DistID");
$query->addField("s", "StateID");
$query->addField("r", "StateID");
$query->addField("s", "StateNam");
$query->condition("SBUID", 1);
$query->orderBy("ORDER BY r.ROName");
$result = $query->execute();