CODEWITHSUNDEEP
sqllaravel-4eloquentquery-builder
user3921996
user3921996

Reputation: 149

Laravel order by conditions eloquent or sql

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

Upvotes: 5

Views: 17372

Answers (2)

pady
pady

Reputation: 279

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);

Upvotes: 7

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81187

->orderByRaw(
     "CASE WHEN expiration >= {$time} THEN edit ELSE created_at END DESC"
)

edit: your example shows that you want something else to what you asked for. The order of your set will be it1, it2, it3. To understand the behaviour try this:

select 
  name, 
  case when expiration >= '2015-03-17' then edit else created_at end as order_value 
from YOUR_TABLE 
order by case when expiration >= '2015-03-17' then edit else created_at end desc;

it will show you the value that is taken for the order by clause:

| name | order_value         |
| it1  | 2015-03-16 15:42:40 |
| it2  | 2015-03-16 15:37:27 |
| it3  | 2015-03-16 14:52:19 |

So, I suppose you in fact need to order by IS_EXPIRED and then by the date? Then you need two case clauses, or something like this:

->orderByRaw(
     "CASE WHEN expiration >= {$time} THEN concat('1 ', edit)
           ELSE concat('0 ',created_at) END DESC"
)

Upvotes: 0

Related Questions