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?

no I am ok with using foreach, the most important is to get the right result as I provided. thank you — Osama Shaki
– Osama Shaki, Commented Aug 23, 2021 at 8:26
@OsamaShaki it's not really clear what the problem is. Nipun has a point, i don't think the foreach is a good way of handeling this. Instead of making a new collection you can put your if statements in your view, where you display the table, or you can edit your select to only get what you really need. Changing the query is the best way. — Gert B.
– Gert B., Commented Aug 23, 2021 at 8:29
@NipunTharuksha get the expired listings based on the availability periods, as user might not provide the 3 periods, period 1 is a must. Thank you — Osama Shaki
– Osama Shaki, Commented Aug 23, 2021 at 8:35
@OsamaShaki For me it's not really clear what the correct data is. So it's very hard to help you with the query. I agree on not overcomplicating the view. — Gert B.
– Gert B., Commented Aug 23, 2021 at 8:37

Nipun Tharuksha · Accepted Answer · 2021-08-24 04:44:38Z

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

edited Aug 24, 2021 at 4:44

answered Aug 23, 2021 at 8:57

Nipun Tharuksha

2,5654 gold badges19 silver badges44 bronze badges

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

5 Comments

Osama Shaki Over a year ago

Nipun, I should get only the 3rd row from the sample data. not record 1&2... you can refer to the result sample. thank you

Osama Shaki Over a year ago

yes I only amend the result. a row should only be returned if the latest not null to_date_* for that row is before now. hope it is clear now. thanks alot

Osama Shaki Over a year ago

Hi @Nipun, could you update the query to get the sample result please? Thanks

Nipun Tharuksha Over a year ago

@OsamaShaki please check

Osama Shaki Over a year ago

Thanks a lot Nipun, Appreciate your help

Collectives™ on Stack Overflow

SQL Query in Laravel is not returning the correct result

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related