Oct 3, 2018
5
Level 1
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.
Please or to participate in this conversation.