Symfony2 subquery within Doctrine entity manager

Question

I need to perform this query:

SELECT * FROM (SELECT * FROM product WHERE car = 'large' ORDER BY onSale DESC) AS product_ordered GROUP BY type

In Symfony2 using the entity manager.

My basic query builder would be :

 $query = $em->getRepository('AutomotiveBundle:Car')
        ->createQueryBuilder('p')
        ->where('pr.car = ?1')
        ->andWhere('pr.status = 1')
        ->orderBy('pr.onSale', 'DESC')
        ->setParameter(1, $product->getName())
        ->groupBy('p.type')
        ->getQuery();

But I cannot work out how to add in a subquery to this.

Ive tried making a separate query and joining it like:

 ->andWhere($query->expr()->in('pr.car = ?1',$query2->getQuery()));

But I get:

Call to undefined method Doctrine\ORM\Query::expr()

Solution 1

One trick is to build two queries and then use getDQL() to feed the first query into the second query.

For example, this query returns a distinct list of game ids:

    $qbGameId = $em->createQueryBuilder();

    $qbGameId->addSelect('distinct gameGameId.id');

    $qbGameId->from('ZaysoCoreBundle:Event','gameGameId');

    $qbGameId->leftJoin('gameGameId.teams','gameTeamGameId');

    if ($date1) $qbGameId->andWhere($qbGameId->expr()->gte('gameGameId.date',$date1));
    if ($date2) $qbGameId->andWhere($qbGameId->expr()->lte('gameGameId.date',$date2));

Now use the dql to get additional information about the games themselves:

    $qbGames = $em->createQueryBuilder();

    $qbGames->addSelect('game');
    $qbGames->addSelect('gameTeam');
    $qbGames->addSelect('team');
    $qbGames->addSelect('field');

    $qbGames->addSelect('gamePerson');
    $qbGames->addSelect('person');

    $qbGames->from('ZaysoCoreBundle:Event','game');

    $qbGames->leftJoin('game.teams',   'gameTeam');
    $qbGames->leftJoin('game.persons', 'gamePerson');
    $qbGames->leftJoin('game.field',   'field');

    $qbGames->leftJoin('gameTeam.team',     'team');
    $qbGames->leftJoin('gamePerson.person', 'person');

    // Here is where we feed in the dql
    $qbGames->andWhere($qbGames->expr()->in('game.id',$qbGameId->getDQL()));

Kind of a long example but i didn't want to edit out stuff and maybe break it.

Solution 2

You can use DBAL for performing any sql query.

$conn = $this->get('database_connection');//create a connection with your DB

$sql="SELECT * FROM (SELECT * FROM product WHERE car =? ORDER BY onSale DESC) AS product_ordered GROUP BY type";   //Your sql Query                
$stmt = $conn->prepare($sql);    // Prepare your sql
$stmt->bindValue(1, 'large');    // bind your values ,if you have to bind another value, you need to write $stmt->bindValue(2, 'anothervalue'); but your order is important so on..
$stmt->execute(); //execute your sql
$result=$stmt->fetchAll(); // fetch your result

happy coding

Comments

[BobFlemming]

Struggling to decide who to accept as my answer. I actually went with this route as its smaller, but it doens't use the Query Builder like Cerads answer.

[Lukas Lukac]

Thx asish! and BTW just a small tip for other users who are not fluent in symfony2 yet... if you write this query already in the repository the connection you can get via $conn = $this->_em->getConnection();

[ssss]

The problem with this solution is that it returns raw data instead of entity objects :(

[Lukas Lukac]

I believe this solution is ignoring limit on subquery @cerad. E.g $qbGameId->setMaxResults(20) and when u print out $qbGames->getDQL() u won't see the limit on subquery.

[Lukas Lukac]

One possible solution: stackoverflow.com/questions/15877287/…