带子查询的querybuilder

kmbjn2e3 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(366)

我需要编写一些复杂的查询生成器表达式。sql看起来像：

SELECT query,popularity,nb_words
FROM gemini_suggestion    
WHERE query IN    
(
    SELECT * FROM    
    (   
        SELECT query    
        FROM gemini_suggestion    
        GROUP BY query    
        HAVING COUNT(query) > 1    
    ) AS subquery    
);

我查到的代码是：

$queryBuilder = $em->createQueryBuilder();
$queryBuilder->select('a')
    ->from($this->entity['class'], 'a')
    ->where($queryBuilder->expr()->In('a.query', $subQuery->getDQL()));

$subQuery2 = $em->createQueryBuilder()
    ->select('b.query')
    ->from($this->entity['class'], 'b')
    ->groupBy('b.query')
    ->having('count(b.query)>1')
;

$subQuery = $em->createQueryBuilder();
$subQuery->select('a')
    ->from(...)
;

任何帮助都很好！

mysql php doctrine query-builder

来源：https://stackoverflow.com/questions/52760053/doctrine-querybuilder-from-with-subquery

1条答案

按热度按时间

nwlqm0z11#

你可以试试这样的。我假设您有一个名称空间实体，其中包含所有实体类。

// Subquery to get the queries greater than 1
$subQueryBuilder = $this->entityManager->createQueryBuilder();
$subQueryBuilder->select('gemini_suggestion.query')
                ->from('Entities\gemini_suggestion', 'gemini_suggestion')
                ->groupBy('gemini_suggestion.query')
                ->having($subQueryBuilder->expr()->gt('COUNT(gemini_suggestion.query)', 1));

// Main query where you use the subquery to filter out the records you want
$queryBuilder = $this->entityManager->createQueryBuilder();
$queryBuilder->select('query, popularity, nb_words')
             ->from('Entities\gemini_suggestion', 'gemini_suggestion')
             ->where($queryBuilder->expr()->in('gemini_suggestion.query', $subQueryBuilder->getDQL()));

$result = $queryBuilder->getQuery()->getResult();
return $result;

赞(0）回复(0）举报 2021-06-18

我来回答

带子查询的querybuilder

1条答案

相关问题

热门标签

最新问答