Murlidhar Fichadia Murlidhar Fichadia - 7 months ago 13
SQL Question

Least Popular Event - Get list of events where bookings IS NULL OR LEAST

I have two Tables : Events and Bookings
One Event has Many Bookings


Event Bookings
1 1
1 2
1 3
2 //consider this as no row in bookings table
3 4
3 5
3 6
4


I need to get list of events that have no bookings, that is there is no foreign key reference of events_id in bookings table.
so here Event 2 and 4.
If all the events have some bookings then the one with least bookings shows up.

$query = select E.id, E.name,
count('B.event_id')
as totalbookings
from events E, bookings B where E.id = B.event_id
group by B.event_id;


Gets me list of all events and total bookings for events with some bookings.

How do I get the events data which don't have bookings at all?

Answer

Okay. So, you need to get events which have no bookings? Let's look at some solutions here.

We can use Eloquent for something like this.

Lets say you have an Events model which contains the method:

public function bookings()
{
return $this->hasMany(Bookings::class, 'FK', 'PK');
}

and a Bookings model which contains the method:

public function event()
{
return $this->belongsTo(Event::class, 'FK', 'PK');
}

You would be able to do something like this in your controller:

$events = Events::whereHas('bookings')->get()->count();

This will grab a collection of all the events which have a booking tied to them and count it. That solves one issue eloquently, now for the events which don't have a booking.

Laravels Eloquent ORM does not have a hasNot method that we can use, we can however use Eagarloading constraints to achieve the result you require.

We are able to grab all Events which don't have a booking by doing something like:

$events = Events::whereHas("bookings", function($q) {
  $q->whereNotNull('deleted_at');
}, '<', 1)->get();

I believe that should give you a collection of all events which do not have a booking.

Let me know how you get on!