Search code examples
laravelpostgresqllaravel-7

Order by as Number, even if column type is string, Laravel

I have a string column, but I have only double numbers (age), and I want to do a order by, but the obvious problem is that i get wrong response, because is string. ex: (1,10,2,3,4,40,etc...). In my code $table = name of the table, sort_by, in this particular case is "age".

$query->orderBy("CAST(" . $table . $request->sort_by . "as NUMERIC)", 'ASC');

Error:

Undefined table: 7 ERROR: missing FROM-clause entry for table "CAST(people"↵LINE 1:

Solution

  • Use orderByRaw and cast the text number to an integer:

    $query->orderByRaw("age::int")

    Note that if you have a persistent need to use this text number column as actual numeric data, it might imply a design problem, and perhaps the column should really be integer in your Postgres table.