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

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

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *