Least Popular Event - Get list of events where boo

问题:

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?

回答1:

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!

回答2:

This works too, I used Left Join to get all the Events from the events table where there is no matching id for an event in Bookings Table. Here is the Query:

    $least = Event::leftJoin('bookings', function($join) 
    {
    $join->on('bookings.event_id', '=', 'events.id');
    })
    ->select('events.name')
    ->whereNull('bookings.event_id')
    ->first();