How to order the rows in Laravel based on a column's value?

user_8424993 0
Viewed 57

I have a table of products with city, state and country name.

If a user visits my shop, the products from his city should be displayed first, then the products from his state and finally the products from his country.

For a table,

+----+---------+-------------+------------+---------------+|

| id | product |    city     |   state    |    country    |

+----+---------+-------------+------------+---------------+

|  1 | guava   | Julian      | California | United States |

|  2 | apple   | London      | NA         | England       |

|  3 | orange  | Los Angeles | California | United States |

|  4 | grapes  | Zion        | Illinois   | United States |

|  5 | banana  | Canyon      | California | United States |

+----+---------+-------------+------------+---------------+

if a user from Los Angeles visits my shop, the expected results should be of order 3,1,5,3,2.

I read the laravel docs and also tried searching in stack overflow but couldn't find any apt answers.

My code for now is

 Products::where('city",$usercity)->orWhere('state',$userstate)
 ->orWhere('country',$usercountry)->get();

I don't have an idea on what to use for the orderBy part!

eloquent laravel mysql
1 Answers

You can use

Case

which returns a value on a specified condition, In your case (no pun intended) it returns the order of products.

We will use orderByRaw() to integrate the case statement.

But

you need to be careful because this will cause SQL injection And we don't want that so we use bindings:

Products::where('city',$usercity)
->orWhere('state',$userstate)
->orWhere('country',$usercountry)
->orderByRaw('
         CASE
            WHEN city = ? THEN 1 
            WHEN state = ? THEN 2
            WHEN country = ? THEN 3
            ELSE 4
        END',[$usercity,$userstate,$usercountry])->get();

And that will got you the desired result hopefully.

P.S: Follow Laravel naming conventions which says: Model's names should be singular not plural (Product not Products).

user_100848510
Related