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 or

Solution 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%"then1when `title` LIKE"%tamil%"then2when `title` LIKE"%nadu%"then3else4end');

---

2: Pass your condition/case to order by clause

$searchText = explode(" ", $searchingFor);
$orderByRowCase = 'case when title LIKE "%tamil nadu%" then 1 ';
foreach ($searchTextas$key => $regionSplitedText) {
   $Key += 2;
   $orderByRowCase .= ' when title LIKE "%' . $regionSplitedText . '%" then ' . $Key . '';
}
$orderByRowCase .= ' else 4 end';   

(Your Laravel Query)

->orderByRaw($orderByRowCase);

Solution 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, 
  casewhen expiration >='2015-03-17'then edit else created_at endas order_value 
from YOUR_TABLE 
orderbycasewhen expiration >='2015-03-17'then edit else created_at enddesc;

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

Solution 3:

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