Eloquent orderBy issue with dates

@Tray2 Thanks

Imagine you have these events:

2023-01-01 2023-01-02
2023-01-02 2023-01-03
2023-01-03 2023-01-04
2023-01-04 2023-01-05
2023-01-05 2023-01-06

And would like to see your events in this order(let's say today we are the 2023-01-04) :

Past events at the back of the collection as their ending date is less than 2023-01-04:
2023-01-01 2023-01-02
2023-01-02 2023-01-03

Open events at the front in this order(as they have not yet passed the 2023-01-03)
2023-01-03 2023-01-04
2023-01-04 2023-01-05
2023-01-05 2023-01-06

It would be like putting the events which are still open at the front of the collection while putting the ones which have expired at the back. This way when the user lands on the page where all the events are showing up, only the open events show up at the top of the page.

I really did not think it would be so tricky. Maybe 2 dependent queries are needed?

Thanks

@LaraBABA something like this:

 Event::query()
    ->orderByRaw('ending_date < CURRENT_DATE()')
    ->orderBy('starting_date', 'asc')
    ->orderBy('ending_date',  'asc')
    ->paginate(15);

@LaraBABA Ok so you want the ones that hasn't an end_date first?

In some RDBMS you can order by it and specify nulls first or last, in mysql you can't but this is how you can work around that.

SELECT * 
FROM events
WHERE end_date IS NULL
ORDER BY starting_date
UNION ALL
SELECT *
FROM events
WHERE end_date IS NOT NULL
ORDER BY starting_date, end_date;

The first part would get all the ongoing events and the second all the ended events.

If this is what you are after, I suggest using a view for this. You can read about that here, you can also see how to do a union query in Eloquent if that is what you are after.

https://tray2.se/posts/database-design-part-2

@Tray2 Thank you so much, yes it is the kind of solutions I was actually looking for. Now gave me the principle, I have enough inforomation to find out how to work out the rest of the solution.

Thank you so much @Tray2 ! Good man :-)

@Tray2 And thanks for your link, looking at it now.

@tykus Sorry Tykus, I just seen your reply now, let me check it out on my site, Will post back

@tykus wow! It worked out of the box! Thank you so much @Tykus. Thank you to all of you.

@click Thanks for your help, I have explained it with a better example just above your question.