Doctrine : how to make a nested query with the query builder?

Question

Good evening

I'm still a junior in SQL queries and the Doctrine query builder. My problem is simple, so simple that I've been searching for a few hours.

I'm in a symfony 4.4 LTS project. I have a table that represents the orders placed on an e-commerce website. There are 4 fields: id, custommer_id, created_at, status

What I want please:

the last 20 past orders that have the status = 'new' with ID descendant

If I make a SQL query

SELECT *
FROM order o
WHERE o.status = 'new'.
ORDER BY o.created_at DESC, o.id DESC
limit 20

It's a failure because I don't get the ids in descending order. I still have trouble understanding why.

Finally I found this solution in SQL:

   SELECT * 
        FROM 
        (
            SELECT * 
            FROM order o 
            WHERE o.status = 'new'. 
            ORDER by created_at DESC 
            LIMIT 20 
        ) table_order
        ORDER by id DESC

Cool!! I'm getting the last 20 commands by descending ID.

Now I have to do it with Doctrine's createQueryBuilder. I tried a lot of solutions without success. Can you please help me? Thank you in advance

Answer 1

Assuming your Entity class has the name Order:

$orders = $entityManager
    ->createQueryBuilder()
    ->from(Order::class, 'o')
    ->select('o')
    ->where('o.status = :newStatus')
    ->setParameter('newStatus', 'new')
    ->orderBy('o.created_at', 'DESC')
    ->addOrderBy('o.id', 'DESC')    
    ->getQuery()
    ->setMaxResults(20)
    ->getResult();

But with DQL it's even nicer:

$orders = $entityManager
    ->createQuery('
        SELECT o 
        FROM App\Entity\Order o
        WHERE o.status = :newStatus
        ORDER BY 
            o.created_at DESC AND
            o.id DESC
    ')
    ->setParameters(['newStatus' => 'new'])
    ->setMaxResults(20)
    ->getResult();

Answer 2

Thank you very much for your clear answers. I wasn't precise enough in my post. To put it in context, this is in the context of a technical test for my first job.

I received a symfo project with fixtures. When I run the fixtures, I get very inconsistent data in the 'Order' table. That is to say that the logic you propose Preciel in the choice of sorting by ID or created_At can't be retained. Let me explain:

Look at the last 9 commands in the Order table. Look at the IDs, they are in a very incoherent order. Is this done expressly to make the test more complex? I don't know.

!

If I reduce the last 5 orders of the table order with the status= 'new', I get this:

!

And I'm asked to sort them by descending ID, so I want to get this:

!

So the solutions you proposed do not rank in descending ID order.

goulash: You proposed this to me:

$orders = $entityManager
    ->createQueryBuilder()
    ->from(Order::class, 'o')
    ->select('o')
    ->where('o.status = :newStatus')
    ->setParameter('newStatus', 'new')
    ->orderBy('o.created_at', 'DESC')
    ->addOrderBy('o.id', 'DESC')    
    ->getQuery()
    ->setMaxResults(20)
    ->getResult(); 

I get that, so it's not good.

!

with a sql request like this, it's good:

  SELECT * 
        FROM 
        (
            SELECT * 
            FROM order o 
            WHERE o.status = 'new'. 
            ORDER by created_at DESC 
            LIMIT 20 
        ) table_order
        ORDER by id DESC

But I can't do it with the querybulder.

Thank you very much for your time.

Answer 3

Getting the last 20 orders by date or by id is just about the same...

The later the order, the bigger the id.

If an order made at 10:32:17 have the id 59,
then an order made after this one, lets say at 10:32:29,
will obvilously get the id 60.

Unless you got so many orders that they happen in the same second,
ordering by eitheir date or id is the same.

Just order them by id :

public function getLastNewOrders() {
    $qb=$this->_em->createQueryBuilder();

    $qb->select("order")
       ->from(Order::class, "order")
       ->andWhere($qb->expr()->eq("order.status", "new")
       ->orderBy("order.id", "DESC")
       ->setMaxResults(20);

    return $qb->getQuery()->getResult();
}