Laravel where if statement

Question

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

type | number | date

I need to do where based on column (type)

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.

The original query and datatype are: 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

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

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:

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

Answer 1

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

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

Answer 2

You should use case for this,

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

Answer 3

$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.

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.

Answer 4

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.

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

Laravel Eloquent Select CASE?