Laravel where if 语句

Laravel where if statement

提问by Froxz

I came to a problem with select from DB. Basically what I am trying to achieve is: I have a table with 3 columns

我遇到了从数据库中选择的问题。基本上我想要实现的是：我有一个包含 3 列的表格

type | number | date

I need to do where based on column (type)

我需要根据列（类型）做 where

If(type = 1) then where number > 1 else where date today()

So if type is equal to 1 then apply where to number else apply where to date. It is possible to do such think? And it is possible to do it with Laravel Eloquent? Thank you.

因此，如果 type 等于 1，则应用到 number 的位置，否则应用到 date 的位置。有可能这样做吗？用 Laravel Eloquent 可以做到吗？谢谢你。

The original query and datatype are: type = string number = integer date = timestamp (Y-m-d H:i:s)

原始查询和数据类型是： type = string number = integer date = timestamp (Y-m-d H:i:s)

Query

询问

SELECT * FROM table_name WHERE CASE WHEN type=days THEN date>now() ELSE number>0 END

SELECT * FROM table_name WHERE CASE WHEN type=days THEN date>now() ELSE number>0 END

Table schema CREATE TABLE banners ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, type varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', number int(10) unsigned NOT NULL DEFAULT '0', finish_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

表架构 CREATE TABLE banners ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, type varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', number int(10) unsigned NOT NULL DEFAULT '0', finish_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Solution that worked:

有效的解决方案：

select * from `banners` 
where `banners`.`block_id` = 1 and `banners`.`block_id` is not null 
and IF (`type` = 'days', `finish_at` > now(), `number` > 0)

Laravel Way:

Laravel 方式：

ParentModel::banners()->whereRaw("IF (`type` = 'days', `finish_at` > now(), `number` > 0)")->get();

采纳答案by Narendrasingh Sisodia

You can use it like as

你可以像这样使用它

SELECT * FROM tablename WHERE IF (`type` = 1, `number` > 1,`date` = today())

Using Laravel Eloquent you can use it like as

使用 Laravel Eloquent 你可以像这样使用它

ModelName::whereRaw('IF (`type` = 1, `number` > 1,`date` = today())')->get();

回答by Niranjan N Raju

You should use casefor this,

你应该case为此使用，

SELECT * FROM tablename
WHERE  CASE WHEN type = 1 THEN number > 1 ELSE date = today() END 

回答by Георги Илиев

$date = '';//Date you want
$number = ''//Number you want
$field = ''//date or number
SomeModel::where($field, '=', $value)->where('type', '=', 'sometype');

You can use Eloquent of Laravel this way, its not necessary to do method chaining in single line.

您可以通过这种方式使用 Laravel 的 Eloquent，无需在单行中进行方法链接。

EDIT: On the first i didnt got the question right.Now its fixed assume you will have dynamic "type" put it into use statement too.

编辑：首先我没有把问题弄好。现在它的固定假设你将有动态“类型”也将其投入使用语句。

EDIT 2: You can represent the field as literal according to if condition and then pass it to the where clause.

编辑 2：您可以根据 if 条件将该字段表示为文字，然后将其传递给 where 子句。

回答by Chung

Try this:

尝试这个：

$query = SomeModel::where(function($query){
                            $query->where(function($query){
                                 $query->where('type',1)->where('number',1)
                             })
                            ->orWhere(function($query){
                                 $query->where('type',1)->where('date', today())
                             })
                         })

We use orWhere to solve this problem. And need a where wraps outside of orWhere to make it clear with another condition if need.

我们使用 orWhere 来解决这个问题。并且需要一个 where 包裹在 orWhere 之外，以便在需要时使用另一个条件进行说明。

If you are using MySQL, try DB::raw and use CASE in WHERE:

如果您使用 MySQL，请尝试 DB::raw 并在 WHERE 中使用 CASE：

Laravel Eloquent Select CASE?

Laravel Eloquent 选择案例？