Laravel按条件雄辩或SQL命令 [英] Laravel order by conditions eloquent or sql

问题描述

我在数据库表中有这些时间戳列-到期，编辑，created_at.我需要按编辑"进行排序，如果项的到期"日期大于今天的日期，则需要按"created_at"进行排序.

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

或

$items = $items
->orderBy(DB::raw("CASE WHEN expiration >= $time THEN edit END"), 'DESC')
->orderBy('created_at', 'DESC')
->get();

变量$ time是正确的，所以我的问题出在查询中. 样本数据:

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

我需要按顺序-> it3，it1，it2

And i need in order -> it3, it1, it2

变量$ time = 2015-03-17

Variable $time = 2015-03-17

推荐答案

如果您要在by by子句中使用'Case'，则有两种方法: 假设用户想查看订购依据"搜索文本 即泰米尔纳德邦"

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: 通过条款将您的条件/情况传递给命令

---

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

(您的Laravel查询)

(Your Laravel Query)

->orderByRaw($orderByRowCase);

这篇关于Laravel按条件雄辩或SQL命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！