php 加入子查询与学说 2 DBAL

Join subquery with doctrine 2 DBAL

提问by aimfeld

I'm refactoring a Zend Framework 2application to use doctrine 2.5 DBAL instead of Zend_DB (ZF1). I have the following Zend_Db query:

我正在重构 Zend Framework 2应用程序以使用 Dotct 2.5 DBAL 而不是 Zend_DB (ZF1)。我有以下 Zend_Db 查询：

$subSelect = $db->select()
    ->from('user_survey_status_entries', array('userSurveyID', 'timestamp' => 'MIN(timestamp)'))
    ->where('status = ?', UserSurveyStatus::ACCESSED)
    ->group('userSurveyID');


$select = $db->select()
    // $selectColNames contains columns both from the main query and 
    // the subquery (e.g. firstAccess.timestamp AS dateFirstAccess).
    ->from(array('us' => 'user_surveys'), $selectColNames)
    ->joinLeft(array('firstAccess' => $subSelect), 'us.userSurveyID = firstAccess.userSurveyID', array())
    ->where('us.surveyID = ?', $surveyID);

This results in the following MySQL query:

这导致以下 MySQL 查询：

SELECT `us`.`userSurveyID`, 
    // More columns from main query `us`
    `firstAccess`.`timestamp` AS `dateFirstAccess`
FROM `user_surveys` AS `us`
LEFT JOIN (
    SELECT `user_survey_status_entries`.`userSurveyID`, 
            MIN(timestamp) AS `timestamp` 
    FROM `user_survey_status_entries` 
    WHERE (status = 20) 
    GROUP BY `userSurveyID`
) AS `firstAccess` ON us.userSurveyID = firstAccess.userSurveyID 
WHERE (us.surveyID = '10')

I can't figure out how to join the subquery using the doctrine 2.5 query builder. In the main query, I need to select columns from the subquery.

我不知道如何使用doctrine 2.5 查询构建器加入子查询。在主查询中，我需要从子查询中选择列。

I have read herethat doctrine does not support joining subqueries. If that's still true, can I write this query in another way using the SQL query builder of doctrine DBAL? Native SQL may not be a good solution for me, as this query will be dynamically extended later in the code.

我在这里读到，学说不支持加入子查询。如果这仍然是正确的，我可以使用doctrine DBAL 的SQL 查询构建器以另一种方式编写此查询吗？Native SQL 对我来说可能不是一个好的解决方案，因为这个查询将在代码的后面动态扩展。

回答by aimfeld

I've found a solution by adapting this DQL exampleto DBAL. The trick is to get the raw SQL of the subquery, wrap it in brackets, and join it. Parameters used in the subquery must be set in the main query:

我通过将此DQL 示例改编为 DBAL找到了解决方案。诀窍是获取子查询的原始 SQL，将其包装在方括号中，然后加入它。子查询中使用的参数必须在主查询中设置：

$subSelect = $connection->createQueryBuilder()
    ->select(array('userSurveyID', 'MIN(timestamp) timestamp'))
    ->from('user_survey_status_entries')
    // Instead of setting the parameter in the main query below, it could be quoted here:
    // ->where('status = ' . $connection->quote(UserSurveyStatus::ACCESSED))
    ->where('status = :status')
    ->groupBy('userSurveyID');

$select = $connection->createQueryBuilder()
    ->select($selectColNames)
    ->from('user_surveys', 'us')
    // Get raw subquery SQL and wrap in brackets.
    ->leftJoin('us', sprintf('(%s)', $subSelect->getSQL()), 'firstAccess', 'us.userSurveyID = firstAccess.userSurveyID')
    // Parameter used in subquery must be set in main query.
    ->setParameter('status', UserSurveyStatus::ACCESSED)
    ->where('us.surveyID = :surveyID')->setParameter('surveyID', $surveyID);

回答by Wilt

To answer this part of your question:

要回答您问题的这一部分：

I can't figure out how to join the subquery using the doctrine 2.5 query builder

我不知道如何使用 2.5 查询构建器加入子查询

You can make 2 query builder instances and use the DQL from the second one inside a clause of your first query. An example:

您可以创建 2 个查询构建器实例，并在第一个查询的子句中使用第二个中的 DQL。一个例子：

->where($qb->expr()->notIn('u.id', $qb2->getDQL())

Check examples hereor hereor find more using Google

检查的例子在这里或在这里或找到更多使用谷歌