CODEWITHSUNDEEP
mysqleloquent
William Martins
William Martins

Reputation: 442

MySQL | Eloquent - Order by first occurrence then by ID

I have the following table:

ID Main URL
1 0 foto1.jpg
2 1 foto2.jpg
3 0 foto3.jpg
4 0 foto4.jpg
5 1 foto5.jpg
6 0 foto5.jpg

I need to order following these rules:

  1. First - Most Recent Main Picture (Just one record, the other mains will be ordered by ID)
  2. ID (Most recent first)

So my table should look like this:

ID Main URL
5 1 foto5.jpg
6 0 foto5.jpg
4 0 foto4.jpg
3 0 foto3.jpg
2 1 foto2.jpg
1 0 foto1.jpg

If I order by main then by ID it will not work because ID 2 will be before ID 6 as it is also a main url.

How can I build this query using MySQL/Eloquent?

Upvotes: 0

Views: 20

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use a CROSS JOIN to a subquery that returns the ID of the most recent Main row. Then use the ID of this row in the ORDER BY clause of your query:

SELECT t1.*
FROM Table1 t1
CROSS JOIN (
   SELECT ID AS Most_Recent_Main_ID
   FROM Table1
   WHERE Main = 1
   ORDER BY ID DESC
   LIMIT 1  
) t
ORDER BY 
  CASE 
    -- Give precedence to the most recent main ID over all other rows
    WHEN ID = Most_Recent_Main_ID THEN 0
    ELSE 1
  END,
  ID DESC

Demo here

Upvotes: 1

Related Questions