CODEWITHSUNDEEP
phplaravellaravel-5eloquent
user3718908x100
user3718908x100

Reputation: 8509

Advanced Where Clause In Laravel

I have an issue with my query in Laravel eloquent, I do not get results when I run the query and the generated query does not seem to be what I expect.

This is the code in controller:

$lastUpdate = Input::get('last_update');
$userId = Auth::user()->id;

$eventIds = EventVendor::where('user_id', $userId)
                    ->where('is_active', 1)->get()->lists('event_id');

$events = EventDetails::whereIn('id', $eventIds)
                    ->where(function($query) use ($lastUpdate) {
                        $query->where('created_at', $lastUpdate);
                        $query->orWhere('updated_at', $lastUpdate);
                    })
                    ->where('is_active', 1)
                    ->with("details_sub")
                    ->with("extras")
                    ->with("chargesDiscounts")
                    ->toSql();

This is the query that is generated:

select * from `mtgh_event_details` 
    where `mtgh_event_details`.`deleted_at` is null 
        and 0 = 1 
        and (`created_at` = ? or `updated_at` = ?) 
        and `is_active` = ?

Apart from the 0 = 1 which isn't supposed to be in there I do not see the full query either.

Upvotes: 3

Views: 5070

Answers (2)

patricus
patricus

Reputation: 62248

The 0 = 1 is showing up because the query to populate your $eventIds is not returning any results, so your Collection is empty. If you pass an empty array (or Collection) to whereIn(), it shortcuts the query by adding in 0 = 1, since searching where id in () is invalid SQL, and logically searching in an empty set will always return no results anyway. This shortcut was added in 4.2.17 with this pull request.

As for the rest of the query, everything looks normal to me. The with() statements are setting up eager loading, which uses separate SQL statements; it does not use joins.

So, since you have three with() statements, you will actually be running four separate queries, one to get your EventDetails, and then one each to load your details_sub, extras, and chargesDiscounts for the loaded event details.

Since they are separate queries, they will not show up in the toSql() output.

Other notes:

  • When getting the event ids, you don't need to call ->get()->lists(), you can simply call ->lists() on the query. If you call get() first, it will load the full objects into a Collection, and then you're calling lists() on the Collection. You can avoid loading the full Collection in the first place by just calling lists() on the query itself.

  • Assuming you have the relationships setup, you can avoid the initial query to get the ids in the first place. You can use the whereHas() method instead. Your query would look like:

    $lastUpdate = Input::get('last_update');
$userId = Auth::user()->id;

// assumes a relationship named 'vendor'
$events = EventDetails::whereHas('vendor', function($query) use ($userId) {
            // $query is query for the EventVendors
            $query->where('user_id', $userId)->where('is_active', 1)
        })
        ->where(function($query) use ($lastUpdate) {
            $query->where('created_at', $lastUpdate);
            $query->orWhere('updated_at', $lastUpdate);
        })
        ->where('is_active', 1)
        ->with("details_sub")
        ->with("extras")
        ->with("chargesDiscounts")
        ->toSql();

Upvotes: 5

user3718908x100
user3718908x100

Reputation: 8509

So i found the issue, apparently this part of the query 

$eventIds = EventVendor::where('user_id', $userId)
                    ->where('is_active', 1)->get()->lists('event_id');

Was returning null or an empty list, hence the 0 = 1 in my query. Also with the help of the other answer I was able to simplify my code a bit so thanks. :)

Upvotes: 1

Related Questions