Laravel order by conditions eloquent or sql

Question

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

Dowdell

Answer 1

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

Stratiform

Answer 2

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

Bicipital

Can you elaborate? If you really want to order by X or Y, then this is what you need. Maybe you want to order by X first, then by Y? Show sample data and expected output. – Bicipital

I added to my question some data – Dowdell

Check the edit - you got something wrong. Rephrase the question when you know what you need in the end. – Bicipital

I need order by edit date when NOT_EXPIRED and then by created_at – Dowdell

My first query is like your example I think – Dowdell

I need items in order by expiration date, if expiration date is bigger or equal today's date. And after that I need all other records ordered by created_at date – Dowdell

Then use 2 cases - one for EXPIRED condition, second for the date OR concatenate the two like in the edit. – Bicipital