Constraining eager loaded relationship

Question

I found a very bizarre behavior of with function for overloading relationships. I have Product and Deal relationships, such that Product belongsTo() Deal (through product_id in deals table). Now, when I try to get all products on sale:

Product::with(['deal' => function($query) {
    $query->whereDate('ends_at', '>', Carbon::now()->toDateTimeString());
}])->get()

this returns a collection of all products, even though there are no records in deals table and all products have deal_id set to NULL. At the same time Product::has('deal')->get() returns an empty collection, as you would expect.

I initially discovered this problem while trying to fetch five random products on sale together with Deal and Image relationships:

Product::with(['deal' => function ($query) {
        $query->whereDate('ends_at', '>', // promo still active
                             Carbon::now()->toDateTimeString());
    },
    'images' => function ($query) {
        $query->where('featured', true);    // image featured on homepage
    }])
->where('status', 'IN_STOCK')   // 'In Stock'
->whereNull('deleted_at')       // wasn't soft-deleted
->orderByRaw('RAND()')
->take(5)->get())

This yields a collection with 5 random Products out of all Products. I tried with query->whereNotNull('ends_at')->whereDate('ends_at' ..... ); but got same results.

What am I doing wrong here?

Answer 1

Your understanding of the concept is completely wrong here.

If you are saying that a Product belongsTo() Deal, then lets assume that a Deal hasMany() Products.

This is the deals table

deals
id | name | ends_at | blah | blah

products
id | deal_id | name | blah | blah

So basically, the Product::with('deal') should return you all products with their Deals being Eager loaded. But Deal::with('products') will return you an empty collection, since no products have a valid deal_id in it.

It is important to note that, since Product can only belongTo a single Deal, you will always get the Deal Model rather than a collection when you perform Product::with('deal') query. But when you perform Deal::with('products') you are bound to get a collection.

So basically, when you say

This returns a collection of all products, even though there are no records in deals table and all products have deal_id set to NULL.

It is pretty obvious.... because the query here is being done on Products and not Deal. If you are trying to find the Deal where ends_at > Carbon::now(), you'll have to do this.

Deal::with('product')->where('ends_at', '>', Carbon::now()->toDateTimeString())