0

I can`t rewrite SQL query to queryBuilder, for my task need only queryBuilder object. I have this SQL query, I need take from database per each user, orders which be last and have isPaid = 0. 

 SELECT
        *
    FROM
        orders o
    JOIN
        (
        SELECT
            owner_id,
            MAX(created_at) max_date
        FROM
            orders
        GROUP BY
            owner_id
    ) max_dates
    ON
        o.owner_id = max_dates.owner_id AND o.created_at = max_dates.max_date
    WHERE
        is_paid = 0
Pazenko Dmytro
  • 17
  • 1
  • 1
    so what have you tried so far? have you taken a look at dql at all? dql essentially is just "we exchange fields for properties and tables for objects". show some effort please ;o) – Jakumi Feb 03 '20 at 09:48
  • @Jakumi I learn some info about DQL, but I can`t rewrite my SQL query to DQL because in DQL join work for related rows and tables, thanks for your advice. – Pazenko Dmytro Feb 03 '20 at 09:54

1 Answers1

1

Since you join a subquery it might be a bit tricky to transfer this SQL query to DQL. Fortunately, you don't have to. Doctrine ORM allows you to perform a regular SQL query and then map the results back to an object, just like it would with DQL.

You can have a look at Native Queries and ResultSetMapping for this: https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/native-sql.html

In your case it could look something roughly like this in a repository find-method:

public function findLatestUnpaidOrders()
{
    $sql = '...'; // Your query

    $rsm = new ResultSetMappingBuilder($this->em);
    $rsm->addRootEntityFromClassMetadata(Order::class, 'order');

    $query = $this->em->createNativeQuery($sql, $rsm);
    // $query->setParameter('owner_id', $user->getId()); // if you later want to pass parameters into your SQL query

    return $query->getResult();
}
dbrumann
  • 16,803
  • 2
  • 42
  • 58