How to pull out models with a criterion for connection, as well as without a connection?

Question

Laravel

Good afternoon,
There are 2 models:
Place (a place for possible rent)
--id
and
Lease (rent)
--id
--place_id
--started_at
--finished_at
The bottom line is that any Place can be rented as many times as you like, the main thing is that it was not during the period of an existing lease.
I want to pull out by request all Place , which:
1. During the specified period are not in the lease stage.
2. Generally never rented.
Sketch:

public function search(Request $request)
    {

        $started_at = $request->query('started_at');
        $finished_at = $request->query('finished_at');

$places = Places::latest();

if(isset($started_at) && isset($finished_at)) {

            $places->whereHas('leases', function($q) use ($started_at, $finished_at) {
                $q
                    ->where('status', '<>', StatusesLeaseHelper::STATUS_WAIT)
                    ->where(function ($qq) use ($started_at, $finished_at) {

                        $qq->where(function ($qqq) use ($started_at, $finished_at) {
                            $qqq
                                ->where('started_at', '>', $started_at)
                                ->where('started_at', '>', $finished_at);

                        })->orWhere(function ($qqq) use ($started_at, $finished_at) {
                            $qqq
                                ->where('finished_at', '<', $started_at)
                                ->where('finished_at', '<', $finished_at);
                        });
                    });
            });
}

return $places->get();
 
}

As far as I understand, this fragment meets condition 1. since the period to the link leases is affected (model Lease ), but places (item 2.), for which there was no lease, are not taken into account. Please tell me - how can I solve this moment?

Answer 1

Will classic SQL queries help you?
Places that never gave up.

select distinct p.* from place p
left join lease l on p.id = l.place_id
where l.id is null -- нет записей в примыкаемой таблице lease, значит место не арендовалось

Places that have never been rented, or for the period in question are free.

select distinct p.* from place p
left join lease l on p.id = l.place_id
where l.id is null
OR p.id not in (select distinct p.id -- находим арендованные места на период поиска
                  from place p
                  join lease l on p.id = l.place_id
                 where l.started_at >= :begin AND (l.finished_at is null OR l.finished_at <= :end) -- период аренды лежит внутри периода поиска
                    OR l.started_at <= :begin AND (l.finished_at is null OR l.finished_at >= :begin) -- точка начала периода поиска лежит в периоде аренды
                    OR l.started_at <= :end AND (l.finished_at is null OR l.finished_at >= :end) -- точка конца периода поиска лежит в периоде аренды
               )