Home / Questions / 374196
Accepted
DAD
Asked: 2020-07-17 19:58:31 +0800 CST 2020-07-17 19:58:31 +0800 CST

How to do an order by on a column whose data type is enum?

  • 772

I have a table called proyectos
which has the following columns:

  • nombre
  • estado = [activo, inactivo, cerrado]
  • fecha_created
  • fecha_updated
  • c_users(fk_user)

what I want to achieve is that I get the proyectosaccording to the estadoand of form descendente fecha_created, so I did this:

$consulta = DB::table('proyecto')->where('c_users',Auth::user()->id)->orderBy('estado')->orderBy('fecha_created','desc')->get();

the query solves the problem for me orderBy('fecha_created','desc')but the problem is with orderBy('estado')what it generates, but just as I save it in the migrate: activo, inactivo, cerrado.

but I would like it to show me in the following way: activos cerrados inactivos

How could I generate the way I want according to the state?

mysql

1 Answers

  1. Best Answer

    If we rely on the MySQL documentation, it indicates that the best ways to do an ordering on a column of type ENUMare:

    • Enter the values ​​in the column in alphabetical order
    • Make sure the ordering is lexical rather than index by casting the values

    Example taken from the official documentation 1

    ORDER BY CAST(col AS CHAR) or ORDER BY CONCAT(col).

    For this case how are you in Laravel you should use the method

    orderByRaw(......)

    And compose the cast inside this method

    Reference

    • 3