orderBy not working

@NoLAstNamE Here is the SQL query string.

select * from `events` order by `event_start_date` desc, `event_end_date` desc

@tisuchi The data should be retrieved ordered by event_start_date descending, then event_end_date descending.

But I get the data in a different order.

When I apply only one order criteria, it works, but with both criteria, it doesn't work.

It's with Laravel 7.x.

@vincent15000 If so, then can you try this?

Event::orderByRaw('event_start_date DESC, event_end_date DESC')->get();

@tisuchi I already tried this and it doesn't work better. Same result.

Could it be a MySQL configuration problem ?

@vincent15000 Interesting!

Is it possible to have the event_start_date and event_end_date are the same date?

@tisuchi Yes some lines have the same date for the start and the end date.

@vincent15000 I assume these kinds of records are creating conflicts.

In that case, you can set explicit order for the same date. For example:

Event::orderByRaw('CASE WHEN event_start_date = event_end_date THEN 0 ELSE 1 END')
               ->orderBy('event_start_date', 'desc')
               ->orderBy('event_end_date', 'desc')
               ->get();

@tisuchi No it doesn't work better.

@tisuchi I just understood the problem. It's a datetime field and the time is taken into account.

Is it possbile to order by date but only by date (without the time part) easily ?

Or do I have to select a formatted date with only the date and order by raw by using the formatted date ?

@vincent15000 If you want to sort by date only, then try this:

Event::orderByRaw('DATE(event_start_date) DESC, DATE(event_end_date) DESC')->get();

@tisuchi I tried with date_format, but it's not working. I didn't thought about date().

Thanks a lot, now it works ;).