Symfony2 Subquery in Doctrine Entity Manager

Question

I need to execute this query:

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

In Symfony2, using entity manager.

My main query designer:

  $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 can’t figure out how to add this to the subquery.

Ive tried to make a separate request and join it like:

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

But I get:

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

Answer 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 separate list of game identifiers:

  $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 dql for more 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 did not want to edit the material and may have broken it.

Answer 2

You can use DBAL to execute 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