0

Hello everyone i have some information and i want to order them by the bill number here is the array of data

[
    {
        "id": 162,
        "bill_number": "9",
        "created_at": "2020-09-15T16:21:47.000000Z",
        "updated_at": "2020-09-15T16:28:40.000000Z"
    },
    {
        "id": 161,
        "bill_number": "8",
        "created_at": "2020-09-15T16:06:56.000000Z",
        "updated_at": "2020-09-15T16:07:09.000000Z"
    },
    {
        "id": 164,
        "bill_number": "10",
        "created_at": "2020-09-15T16:28:51.000000Z",
        "updated_at": "2020-09-15T16:29:24.000000Z"
    },
    {
        "id": 151,
        "bill_number": "1",
        "created_at": "2020-09-15T15:18:47.000000Z",
        "updated_at": "2020-09-15T15:19:13.000000Z"
    }
]

basically its a punch of bills

i am retrieving them like this

    $paid_bills = Bill::where([
        ['grand_total' , '!=' , 'null'],
        ['status' , 'payed']
    ])->orderBy('bill_number' , 'desc')
    ->get();

the what i am getting is like this

9

8

10

1

the simple question is why it is ordering it like this and how to fix it. Thanks for your time

Note: i removed non-relevant data to save time

RYOK
  • 473
  • 7
  • 23

2 Answers2

2

you can use orderByRaw

and you can convert string column to integer by multiple it by 1 ...

 $paid_bills = Bill::where([
        ['grand_total' , '!=' , 'null'],
        ['status' , 'payed']
    ])->orderByRaw('bill_number*1 desc')
    ->get();
OMR
  • 11,736
  • 5
  • 20
  • 35
  • That feels a little hacky... Why not a proper cast? Like `orderByRaw('CAST(bill_number as UNSIGNED) DESC')`? – Tim Lewis Sep 15 '20 at 19:37
  • great thank you very much worked perfectly i will up your question but i will give the answered mark to hxk1r because he gave us the lead – RYOK Sep 15 '20 at 19:38
  • 1
    Please give checkmark to this answer, my answer is not correct at all by @TimLewis said. – hxk1r Sep 15 '20 at 19:40
0

Your bill_number is currently string. To order them in DESC, you should use them as integer.

Use "bill_number": 9, instead of "bill_number": "9", on your bills, and apply this to all of them.

Remove the quotation mark.

hxk1r
  • 87
  • 2
  • 11
  • i am getting the data from my database and the bill_number has String type because i need to use big numbers so how can i convert them in the where statement ? – RYOK Sep 15 '20 at 19:28
  • https://stackoverflow.com/questions/11808573/sql-order-string-as-number check this – Davit Zeynalyan Sep 15 '20 at 19:31
  • I'm not a pro about Laravel, but I'm kinda sure that ordering by DESC works only in integers. And your `bill_number` is string. I'm confident about that – hxk1r Sep 15 '20 at 19:32
  • 1
    @hxk1r That's partially incorrect; `ORDER BY column DESC` works with all kinds of columns types; strings, integers, dates, time, etc. You're correct that `bill_number` is a string, but this is not static data; you can't just say "remove the quotations", as it doesn't work that way. – Tim Lewis Sep 15 '20 at 19:34
  • yes that is true the data is stored as string which explains the weird behavior of order by but as @TimLewis mentioned i can't just convert types by removing the quotations – RYOK Sep 15 '20 at 19:36
  • @RYOK Follow-up, if you're going to be converting these strings to integers for ordering, is there a reason you can't just store them as integers? How big are these order numbers? – Tim Lewis Sep 15 '20 at 19:38
  • it can be 1 million 1 trillion i don't exactly know because it represents the number of bills created by a certain company – RYOK Sep 15 '20 at 19:40
  • 1 million is not a big number (in terms of database numbering), and you're unlikely to ever have a trillion records in your database. I understand the hesitation, but I'd suggest converting :) – Tim Lewis Sep 15 '20 at 19:41
  • 1
    hmm i think you got a point i will put it as bigInt or something similar Thanks A lot @TimLewis – RYOK Sep 15 '20 at 19:43