CODEWITHSUNDEEP
mysqllaravel
Dwix
Dwix

Reputation: 1257

MySQL add where clause if field is not null

I have a news table, and I want to get all the news that are started but not ended yet by filtering by the columns start_at & end_at :

"select * from `news` where `start_at` <= NOW() and `end_at` >= NOW()"

or as I'm doing in Laravel :

$news = News::where("start_at", '<=', date("Y-m-d"))
            ->where("end_at", '>=', date("Y-m-d"))->get();

But I also want to ignore filtering by the column end_at if it is NULL, so if the column is NULL I want the result to be displayed as if I only used this query :

"select * from `news` where `start_at` <= NOW()"

Thanks in advance.

Upvotes: 2

Views: 981

Answers (4)

Raymond Nijland
Raymond Nijland

Reputation: 11602

If end_at is indexed i rather would rather rewrite Madhur Bhaiya's answer

Query 

SELECT * FROM news 
WHERE start_at <= NOW() AND 
      (end_at >= NOW() OR end_at IS NULL)

as

Query rewite 

SELECT * FROM news 
WHERE
    start_at <= NOW()
  AND 
    end_at >= NOW()
UNION ALL 
SELECT * FROM news 
WHERE
    start_at <= NOW()
  AND
     end_at IS NULL

Because OR tends to optimize much worse then using a UNION ALL approach.
Oracle database does optimization trick native in the optimizer, still waiting until MySQL also get this optimization trick native in the optimizer as MySQL is owned by Oracle corp.

Upvotes: 2

Djellal Mohamed Aniss
Djellal Mohamed Aniss

Reputation: 1733

Thanks to the union Operator, we can do something like this:

$news = News::whereNotNull("end_at")
            ->where("start_at", '<=', date("Y-m-d"))
            ->where("end_at", '>=', date("Y-m-d"))
            ->union
            (

               News::whereNull("end_at")
                   ->where("start_at", '<=', date("Y-m-d"))

            )->get();

Upvotes: 0

mare96
mare96

Reputation: 3859

You can try with Carbon:

$news = News::where("start_at", '<=', Carbon::now())
             ->when('end_at' !== null, function($query){
                  $query->where("end_at", '>=', Carbon::now())
             })
             ->get();

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Simply use AND/OR logical operators, alongwith IS NULL comparison operator. Query in MySQL would be as follows:

SELECT * FROM news 
WHERE start_at <= NOW() AND 
      (end_at >= NOW() OR end_at IS NULL)

Upvotes: 5

Related Questions