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

score 7 · Answer 1 · edited Dec 12 '18 at 10:37

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

Jarek Tkaczyk · Answer 2 · 2015-03-17T16:46:15.743

0

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

edited Mar 17 '15 at 16:46

answered Mar 17 '15 at 14:44

Jarek Tkaczyk

78,987
25
159
157

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. – Jarek Tkaczyk Mar 17 '15 at 15:15
I added to my question some data – user3921996 Mar 17 '15 at 15:32
Check the edit - you got something wrong. Rephrase the question when you know what you need in the end. – Jarek Tkaczyk Mar 17 '15 at 15:44
I need order by edit date when NOT_EXPIRED and then by created_at – user3921996 Mar 17 '15 at 15:49
My first query is like your example I think – user3921996 Mar 17 '15 at 15:56
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 – user3921996 Mar 17 '15 at 16:07
Then use 2 `cases` - one for EXPIRED condition, second for the date OR concatenate the two like in the edit. – Jarek Tkaczyk Mar 17 '15 at 16:46

score 0 · Answer 3 · answered Sep 08 '16 at 20:32

0

->orderByRaw("CASE WHEN expiration >=".$time." THEN edit END DESC")->get();

answered Sep 08 '16 at 20:32

anasmorahhib

807
9
14

Laravel order by conditions eloquent or sql

3 Answers3

Linked