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 the type is equal to 1 then apply where to number else apply where to date. It is possible to do such a thing? 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();

Narendrasingh Sisodia · Accepted Answer · 2015-11-02 11:09:19Z

8

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

edited Nov 2, 2015 at 11:09

answered Nov 2, 2015 at 8:50

Narendrasingh Sisodia

21.4k6 gold badges51 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

SergkeiM Over a year ago

You have an error in your SQL syntax; WHERE IF (type = days, finish_at > now() ,left > 0)' at line 3

Narendrasingh Sisodia Over a year ago

What is days over here ? You didn't specified it within your question

SergkeiM Over a year ago

This is my data, type is a string, if accepts only integers?

Narendrasingh Sisodia Over a year ago

Please specify your conditions clearly. What you want to achieve and what variables you're using over here also post the original error that you were getting over here

SergkeiM Over a year ago

Updated my question. Thank you.

|

Community · Accepted Answer · 2017-05-23 12:16:52Z

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?

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Nov 2, 2015 at 8:39

Chung

9751 gold badge12 silver badges22 bronze badges

4 Comments

SergkeiM Over a year ago

Good solution But I cant use OR I need exact where

Chung Over a year ago

What DBMS are you using?

Chung Over a year ago

@PoWeR if you are using MySQL, just take a look at DB raw queries . Using Case in where may help. stackoverflow.com/questions/17092112/…

SergkeiM Over a year ago

MySQL I will take a look

Niranjan N Raju · Accepted Answer · 2015-11-02 08:54:42Z

3

You should use case for this,

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

edited Nov 2, 2015 at 8:54

answered Nov 2, 2015 at 8:20

Niranjan N Raju

12k4 gold badges24 silver badges41 bronze badges

2 Comments

SergkeiM Over a year ago

This is what I need but MySQL gives error You have an error in your SQL syntax; WHERE CASE WHEN type=days THEN finish_at > 1 ELSE left > 0 END

Niranjan N Raju Over a year ago

@PoWeR will have a check and will update the answer.

Георги Илиев · Accepted Answer · 2015-11-02 09:31:32Z

2

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

edited Nov 2, 2015 at 9:31

answered Nov 2, 2015 at 8:21

Георги Илиев

315 bronze badges

1 Comment

SergkeiM Over a year ago

From where did you got variable $type? I think you didn't got the question

Collectives™ on Stack Overflow

Laravel where if statement

4 Answers 4

13 Comments

4 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

13 Comments

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related