Laravel order by conditions eloquent or sql
11,973
If u want to use 'Case' in order by clause there were 2 ways: Suppose users want to view "Order By" searching text i.e. "tamil nadu"
1:
->orderByRaw('case
when `title` LIKE "%tamil nadu%" then 1
when `title` LIKE "%tamil%" then 2
when `title` LIKE "%nadu%" then 3
else 4 end');
2: Pass your condition/case to order by clause
$searchText = explode(" ", $searchingFor);
$orderByRowCase = 'case when title LIKE "%tamil nadu%" then 1 ';
foreach ($searchText as $key => $regionSplitedText) {
$Key += 2;
$orderByRowCase .= ' when title LIKE "%' . $regionSplitedText . '%" then ' . $Key . '';
}
$orderByRowCase .= ' else 4 end';
(Your Laravel Query)
->orderByRaw($orderByRowCase);
Author by
user3921996
Updated on June 11, 2022Comments
-
user3921996 almost 2 years
I have these timestamp columns in database table - expiration, edit, created_at. I need to order by 'edit', if item 'expiration' date is bigger than today's date, else I need to order by 'created_at'.
I am trying something like this, but it isn't working correctly
$items = $items ->orderBy(DB::raw("CASE WHEN expiration >= $time THEN edit ELSE created_at END"), 'DESC') ->get();
or
$items = $items ->orderBy(DB::raw("CASE WHEN expiration >= $time THEN edit END"), 'DESC') ->orderBy('created_at', 'DESC') ->get();
Variable $time is correct, so my problem is in query. Sample data:
id name created_at expiration edit 1. it1 2015-03-16 15:42:40 0000-00-00 00:00:00 2015-03-16 15:42:40 2. it2 2015-03-16 15:37:27 2015-03-16 00:00:00 2015-03-16 15:37:27 3. it3 2015-03-16 12:36:50 2015-03-27 00:00:00 2015-03-16 14:52:19
And i need in order -> it3, it1, it2
Variable $time = 2015-03-17