John Davidson

Message:

I've SQL query looking like this below:

SELECT t1.* FROM (

    SELECT m.*

    FROM game m

    WHERE m.date > :date

) AS t1

LEFT JOIN (

    SELECT m.*

    FROM game m

    LEFT JOIN bet b ON b.game_id = m.ID

    WHERE b.user_id = :user

) AS t2

ON t1.ID = t2.ID

WHERE t2.ID IS NULL

I am writing a small project in Symfony 5.2 with ORM 2.8 and I wonder how to pass the above query using queryBuilder.

Writing first subquery is simple

$this->createQueryBuilder('g')

    ->where('g.date > :val')

    ->setParameter('val', new \DateTime())

    ->getQuery()

    ->getResult();

But how put it in subquery, and left join other one?

---

My Answer:
> Here is how you can achieve the same result using the createQueryBuilder method in PHP:

php
$queryBuilder = $entityManager->createQueryBuilder();

$subQuery = $queryBuilder->select('m')
->from('Game', 'm')
->where('m.date = :date')
->getDQL();

$query = $queryBuilder->select('t1')
->from('(' . $subQuery . ')', 't1')
->setParameter('date', $date)
->getQuery();

$results = $query->getResult();


In this code snippet, we first create a subquery using the createQueryBuilder method to select all columns from the Game entity where the date matches a specified value. We then use this subquery in the main query to select all columns from the subquery result. Finally, we set the parameter for the date and execute the query to get the results.