Laravel orderBy with column value error

Question

I'm trying to sort results by column value but it doesn't work

$users = Comment::select([
            'id',
            'comment',
            'user_name',
            'product_id',
            'rating',
            'country',
            'status',
            'pin',
            'created_at',
        ])->where('shop_name',$shop)->where('product_id', $id)->with('images')->orderByRaw("IF(product_url = 'customer')  DESC")->orderByRaw("product_url = manually ASC")->orderBy('pin', 'desc')->orderBy('rating', 'desc')->with('pages')->get();

I added this code

->orderByRaw("IF(product_url = 'customer')  DESC")

and I get this error

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') DESC, product_url = manually ASC, pin desc, rating desc' at line 1 (SQL: select id, comment, user_name, product_id, rating, country, status, pin, created_at from comments where shop_name = and product_id = order by IF(product_url = 'customer') DESC, product_url = manually ASC, pin desc, rating desc)

You can include the IF result as a computed column, then order on that. — tadman

spencer7593 spencer7593 · Accepted Answer · 2018-05-15T01:50:04

MySQL IF function takes three arguments.

This expression is invalid:

  IF(product_url = 'customer')

because there is only one argument provided to the IF() function.

We could do this:

  IF(product_url = 'customer',1,0)

which is equivalent to the more ANSI-standards compliant

  CASE WHEN product_url = 'customer' THEN 1 ELSE 0 END

MySQL shorthand would also work

  ORDER BY product_url = 'customer'   DESC

which would be equivalent to

  ORDER BY CASE
           WHEN product_url = 'customer' THEN 1 
           WHEN product_url IS NOT NULL  THEN 0
           ELSE NULL
           END   DESC

Laravel orderBy with column value error

1 Answers