Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

yaeykay's avatar

Laravel order by starts_at and ends_at within current date

How can I pin to top one or more results that have starts_at and ends_at dates between the current date?

For exampe:

**Suppose Current Date: 2018-10-05**

|      name |            starts_at |              ends_at |           created_at |
|-----------|----------------------|----------------------|----------------------|
| Example A |               (null) |               (null) |  2018-10-05 00:00:00 |
| Example B |               (null) |               (null) |  2018-10-04 00:00:00 |
| Example C |           2018-10-01 |           2018-10-03 |  2018-10-03 00:00:00 |
| Example D |           2018-10-03 |           2018-10-05 |  2018-10-02 00:00:00 |
| Example E |           2018-10-05 |           2018-10-07 |  2018-10-01 00:00:00 |
| Example F |           2018-10-07 |           2018-10-09 |  2018-09-30 00:00:00 |

Since Example E have starts_at and ends_at between the current date, Example F has starts_at in future date, Example C and Example D has ends_at in past date, Example A and Example B does not have any, the order should be:

Example E
Example A
Example B
Example C
Example D
Example F

My current eloquent order by query is:

Example::orderByRaw('if(isnull(starts_at) >= curdate() and isnull(ends_at) <= curdate(), starts_at, created_at) desc');

And it just return:

Example A
Example B
Example C
Example D
Example E
Example F

I also created a sqlfiddle.

0 likes
5 replies
Snapey's avatar

Ssshh, no need to shout. Half of the world is in bed. Nobody is here to do your bidding.

I cannot think of a simple way to do this other than with three separate queries

1 like
yaeykay's avatar

I'll be waiting for others then :laughing:

yaeykay's avatar

BTW @Snapey , Could you please comment separate queries you thinking now?

Snapey's avatar

How can I pin to top one or more results that have starts_at and ends_at dates between the current date

How can something be between the current date ? The current date is 4th. How can something be between it?

Please or to participate in this conversation.