Order by date in different columns in Laravel

Question

I can't seem to get how to order by month in Laravel 7. I use different columns for my month, day, year and time and instead of ordering it by month, it orders the months alphabetically.

Intended Result: January, February, March, April

Actual Result: April, February, January, March

This is my code:

$ledgers = Ledger::orderBy('month', 'DESC')
            ->orderBy('day', 'DESC')
            ->orderBy('year', 'DESC')
            ->orderBy('time', 'DESC')
            ->where('user_id', auth()->user()->id)
            ->paginate(8);

Well, if your `month` column is a `varchar` that contains the text of the month, of course it's going to order alphabetically... This seems like a pretty bad way to store dates. You should store a `date`, `datetime` or `timestamp`, and use functions like `DATE_FORMAT` to get the month, day, year, etc etc. — Tim Lewis, Jun 05 '20 at 16:25
Is the month a number or a string? If it's a string you will have to build custom sort logic. Is there a specific reason you don't use datetime instead of the separate columns? — MaartenDev, Jun 05 '20 at 16:25
It is a string. I'm still learning how to do backend and what my teacher told us to separate them. It was also easier to get the monthly and daily reports through it — Ven, Jun 05 '20 at 16:35

OMR · Accepted Answer · 2020-06-05T16:41:07.567

2

first of all: i must say that you should store your date info in database in datetime column like Tim Lewis said in comment ...

any way you can use order by fields , this kind of order make the result ordered according to the fields you provide ...

$ledgers = Ledger::orderByRaw('FIELD(month,'January','February','March','May', 'June','July','August','September','October','November','December')')
            ->orderBy('day', 'DESC')
            ->orderBy('year', 'DESC')
            ->orderBy('time', 'DESC')
            ->where('user_id', auth()->user()->id)
            ->paginate(8);

please see:

https://stackoverflow.com/a/9378709/10573560

edited Jun 05 '20 at 16:41

answered Jun 05 '20 at 16:32

OMR

11,736
5
20
35

Okay, if this doesn't work I'll probably try to convert everything to datetime. Can it be ordered desc? I tried adding `desc` but it doesn't work. – Ven Jun 05 '20 at 17:17
please see this, i have generic info about what you need: https://stackoverflow.com/a/40529488/10573560 – OMR Jun 05 '20 at 17:19
if you want to sort desc using field way, you should reverse the field values: December,November ... ex – OMR Jun 05 '20 at 17:21
sorry for the late reply, I can't seem to make groupBy working based from the references you gave so I just manually used `orderBy` – Ven Jun 06 '20 at 03:54
1

I'll accept this. Thank you so much for the help and time – Ven Jun 06 '20 at 03:55

Order by date in different columns in Laravel

1 Answers1