php - 我如何在 zend 查询中转换这个多个子查询？

Question

我有以下查询，它在 mysql 中给出了所需的输出，现在我想用 zend 查询语言实现它，它有不同的方法来实现查询..

SELECT A.NAME , B.PAYMENT , C.TOTALPROJ , D.TOTALTASK , T.ACTIVETASK , H.HOUR
FROM USERMASTER AS A 
LEFT OUTER JOIN
(
SELECT A.U_ID ,  SUM(A.TOTALTIME * B.RATE) AS PAYMENT
FROM 
(
SELECT U_ID , PROJECT_ID , 
SUM(TIME_TO_SEC(CASE WHEN endtime is null then timediff (starttime,starttime) 
ELSE timediff (endtime,starttime) END )) / 3600 AS TOTALTIME
FROM LOGMASTER AS A
WHERE PROJECT_ID IS NOT NULL
GROUP BY U_ID , PROJECT_ID
) AS A 
INNER JOIN PROJECTTOUSER AS B ON A.PROJECT_ID = B.PROJECT_ID AND A.U_ID = B.U_ID 
GROUP BY B.U_ID
) AS B ON A.ID = B.U_ID

LEFT OUTER JOIN 
(
  SELECT U_ID , COUNT(*) AS TOTALPROJ FROM PROJECTTOUSER GROUP BY U_ID
) AS C ON A.ID = C.U_ID

LEFT OUTER JOIN 
(
  SELECT ASSIGNED_TO , COUNT(*) AS TOTALTASK FROM TASKTOTARGET GROUP BY ASSIGNED_TO
) AS D ON A.ID = D.ASSIGNED_TO

LEFT OUTER JOIN 
(
  SELECT ASSIGNED_TO,COUNT(*) AS ACTIVETASK FROM TASKTOTARGET WHERE 
IS_ACTIVE = 0    GROUP BY ASSIGNED_TO
) AS T ON A.ID = T.ASSIGNED_TO

LEFT OUTER JOIN
(
  SELECT U_ID, SEC_TO_TIME(SUM(TIME_TO_SEC(CASE WHEN endtime is null then
 timediff (starttime,starttime) ELSE timediff (endtime,starttime) END ))) AS HOUR 
FROM LOGMASTER WHERE INSERT_DATE >= '2013-08-20' AND INSERT_DATE <='2013-08-31' 
GROUP BY U_ID
) AS H ON A.ID = H.U_ID

因此，如果有人可以指导我如何在 zend 中创建此查询，那么它将非常有帮助，并且不胜感激

Answer 1

每个子查询都会成为一个新的 Zend_Query，然后您可以像使用表一样使用它并将其传递给主查询。

例如:

$h = new Zend_Db_Select()
    ->from('LOGMASTER', array('U_ID', 'HOUR' => new Zend_Db_Expr('SEC_TO_TIME(SUM(TIME_TO_SEC(CASE WHEN endtime is null then
 timediff (starttime,starttime) ELSE timediff (endtime,starttime) END ))))')
    ->where("INSERT_DATE >= '2013-08-20'")
    ->where("INSERT_DATE <= '2013-08-31'")
    ->group('U_ID');

$mainQuery = new Zend_Db_Select()
      ->from('a' => 'USERMASTER', array('NAME'))
      ->joinLeft($h, 'A.ID = H.U_IS', array('HOUR'));

您可以将每个子查询创建为其自己的对象，然后可以将它们连接到主查询中。连接函数的最后一个参数是子查询中的哪些列应添加到主查询中。

借助 ZF 的流畅界面，您可以继续连接表/查询，直到构建整个查询。

http://framework.zend.com/manual/1.12/en/zend.db.select.html