SQL Query in Laravel is not returning the correct result

Question

I am trying to get the expired listings based on the availability periods, there are 3 different available periods. a row should only be returned if the latest not null to_date_* for that row is before now.

A sample data:

id	from_date_1	to_date_1	from_date_2	to_date_2	from_date_3	to_date_3
1	2021-06-10	2021-08-15	2021-08-16	2021-08-31	2021-09-01	2021-09-15
2	2021-06-25	2021-08-10	2021-08-11	2021-08-25	NULL	NULL
3	2021-06-25	2021-08-20	NULL	NULL	NULL	NULL

My SQL Query is:

$listings = collect();
            $all_listings = Listing::query()
                        ->where('vendor_id', $vendor->id)
                        ->where('is_deleted', 0)
                        ->where('is_published', 1)
                        ->where('is_approved', 1)
                        ->where('lease_term', '!=', 'long_term')
                        ->orderBy('created_at', 'desc')
                        ->paginate(10);

            foreach($all_listings as $lis)
            {
                if($lis->to_date_3 != null && ($lis->to_date_3 < \Carbon\Carbon::now()->format('Y-m-d')))
                {
                    $listings->add($lis);
                    continue;
                }
                elseif($lis->to_date_2 != null && ($lis->to_date_2 < \Carbon\Carbon::now()->format('Y-m-d')))
                {
                    $listings->add($lis);
                    continue;
                }
                elseif($lis->to_date_1 < \Carbon\Carbon::now()->format('Y-m-d'))
                {
                    $listings->add($lis);
                    
                }
            }

The result should be:

id	from_date_1	to_date_1	from_date_2	to_date_2	from_date_3	to_date_3
3	2021-06-25	2021-08-20	NULL	NULL	NULL	NULL

However, the query is returning all the 3 listings. how can I fix the query to get the correct result?

Answer 1

I have recreated your db and tested below code with above data.No need to use fooreach then Check this

Migration

 $table->dateTime('from_date_1');
            $table->dateTime('to_date_1');
            $table->dateTime('from_date_2')->nullable();
            $table->dateTime('to_date_2')->nullable();
            $table->dateTime('from_date_3')->nullable();
            $table->dateTime('to_date_3')->nullable();

DB

Query

$data = Listing::where(function($query){
            $query->whereDate('to_date_1', '>=', Carbon::now())
                ->orWhereDate('to_date_2', '>=', Carbon::now())
                ->orWhereDate('to_date_3', '>=', Carbon::now());
        })->get();

Result

[
  {
    "id": 1,
    "from_date_1": "2021-06-10 00:00:00",
    "to_date_1": "2021-08-15 00:00:00",
    "from_date_2": "2021-08-16 00:00:00",
    "to_date_2": "2021-08-31 00:00:00",
    "from_date_3": "2021-09-01 00:00:00",
    "to_date_3": "2021-09-15 00:00:00",
    "created_at": null,
    "updated_at": null
  },
  {
    "id": 2,
    "from_date_1": "2021-06-25 00:00:00",
    "to_date_1": "2021-08-10 00:00:00",
    "from_date_2": "2021-08-11 00:00:00",
    "to_date_2": "2021-08-25 00:00:00",
    "from_date_3": null,
    "to_date_3": null,
    "created_at": null,
    "updated_at": null
  }
]

Efficiency

Update

Since required answer has been amended later my answer I have updated the query as follow

$to_date_3 = Listing::whereNotNull('to_date_1')->whereNotNull('to_date_2')->whereDate('to_date_3', '<=', Carbon::now())->get();
$to_date_2 = Listing::whereNull('to_date_3')->whereNotNull('to_date_2')->whereDate('to_date_2', '<=', Carbon::now())->get();
$to_date_1 = Listing::whereNull('to_date_3')->whereNull('to_date_2')->whereDate('to_date_1', '<=', Carbon::now())->get();

$result = $to_date_1->merge($to_date_2)->merge($to_date_3);

Result

[
  {
    "id": 3,
    "from_date_1": "2021-06-25 00:00:00",
    "to_date_1": "2021-08-20 00:00:00",
    "from_date_2": null,
    "to_date_2": null,
    "from_date_3": null,
    "to_date_3": null,
    "created_at": null,
    "updated_at": null
  }
]

Efficiency