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!
You can use
Casewhich 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.
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).