CODEWITHSUNDEEP
phpmysqldoctrinequery-builder
Michal Skibicki
Michal Skibicki

Reputation: 11

Doctrine QueryBuilder from with subquery

I need to write some complex query builder expression. The sql looks like:

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

The code im stucked at is:

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

Any help would be nice!

Upvotes: 1

Views: 1127

Answers (1)

user3267053
user3267053

Reputation: 166

You can try something like this. I'm assuming you have a namespace Entities where you have all your entities classes. 

// 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;

Upvotes: 2

Related Questions