加入子查询与学说 2 DBAL

2022-01-03 00:00:00 php mysql doctrine-orm zend-framework2 query-builder

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

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

$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);

这会导致以下 MySQL 查询:

This results in the following MySQL query:

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')

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

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 DBAL 的SQL 查询构建器以另一种方式编写此查询吗?Native SQL 对我来说可能不是一个好的解决方案，因为这个查询将在代码后面动态扩展.

I have read here that 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.

推荐答案

我通过调整这个DQL 示例 到 DBAL.诀窍是获取子查询的原始 SQL，将其包装在方括号中，然后加入它.子查询中使用的参数必须在主查询中设置:

I've found a solution by adapting this DQL example to 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:

$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);

相关文章