Eloquent query not builded as expected

I have three models:

Rental.php

class Rental extends Model
{
use SoftDeletes;

public function rentalItem()
{
    return $this->hasMany('App\Models\RentalItem');
}

}

RentalItem.php

class RentalItem extends Model
{
    public function rentalAsset()
    {
        return $this->belongsTo('App\Models\RentalAsset');
    }

    public function rental()
    {
        return $this->belongsTo('App\Models\Rental');
    }
}

and RentalAsset.php

class RentalAsset extends Model
{    
    public function rentalItem()
    {
        return $this->hasMany('App\Models\RentalItem');
    }
}

One Rental can have many RentalItems which belongs to RentalAsset. RentalAsset is product card for each RentalItem so one RentalItem can have one RentalAsset.

When RentalItem is created, first it needs to check if it is available in date interval. This done by checking whether between date_from and date_two is some Rental which has RentalItem related to RentalAsset.

I want query, which will return all RentalAssets which are not related to any RentalItem being related to Rental during those dates.

Unfortunately, this is not working right:

$freeAssets = RentalAsset::where('rental_asset_category_id', '=', $request->rental_asset_category_id)
    ->whereDoesntHave('rentalItem.rental', function($query) use($date_from, $date_to)
        {
            $query->where('date_from', '<=', $date_to);
            $query->where('date_to', '>=', $date_from);
        })
    ->get();

Your help is highly apreciated! Thx a lot.

UPDATE: using Laravel 5.6

UPDATE2: I sput out generated select by the provided eloquent query:

select * from `rental_assets` where `rental_asset_category_id` = ? and exists 
(select * from `rental_items` where `rental_assets`.`id` = `rental_items`.`rental_asset_id` and not exists 
    (select * from `rentals` where `rental_items`.`rental_id` = `rentals`.`id` 
        and `date_from` <= ? and `date_to` >= ? and `rentals`.`deleted_at` is null)) 
and `rental_assets`.`deleted_at` is null

And this select returns what I need:

select * from `rental_assets` where `rental_asset_category_id` = 2 
and not exists (
    select * from `rental_items` where `rental_assets`.`id` = `rental_items`.`rental_asset_id` and exists 
        (select * from `rentals` where `rental_items`.`rental_id` = `rentals`.`id` 
            and `date_from` <= '2018-12-12' and `date_to` >= '2018-01-01' and `rentals`.`deleted_at` is null)) 
and `rental_assets`.`deleted_at` is null;

What is the correct eloquent query? I would rather that then raw query. thx.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2xDwpny
via IFTTT