最少热门活动 - 获取预订为NULL或最少的活动列表 [英] Least Popular Event - Get list of events where bookings IS NULL OR LEAST

问题描述

我有两个表：活动和预订
一个活动有很多预订

 
活动预订
 1 1 
 1 2 
 1 3 
 2 //认为这在预订表中没有行
 3 4 
 3 5 
 3 6 
 4 
 

我需要获得没有预订的事件列表，即在预订表中没有events_id的外键引用。
所以这里的事件2和4.
如果所有的事件有一些预订，那么最少预订的一个预订显示。

  $ query = select E.id，E.name，
 count（'B.event_id'）
 as totalbookings 
来自事件E，预订B其中E.id = B.event_id 
 group by B.event_id; 
  

获得一些预订活动的所有活动和总预订的列表。

如何获取没有预订的活动数据？

解决方案

好的。所以，你需要得到没有预订的事件？让我们来看看这里的一些解决方案。

我们可以使用Eloquent这样的东西。

包含方法的事件模型：

  public function bookings（）
 {
 return $ this- > hasMany（Bookings :: class，'FK'，'PK'）; 
} 
  

和包含方法的Bookings模型：

  public function event（）
 {
 return $ this-> belongsTo（Event :: class，'FK'，'PK '）; 
} 
  

您可以在控制器中执行这样的操作：

$ events = Events :: whereHas（'bookings'） - > get（） - > count（）;

这将抓取所有的事件，有一个预订绑定到他们并计数的集合。有效地解决了一个问题，现在对于没有预订的事件。

Laravels Eloquent ORM没有可以使用的hasNot方法，但是我们可以使用Eagarloading约束来实现您需要的结果。

我们可以通过执行以下操作来获取没有预订的所有活动：

  $ events = Events :: whereHas（bookings，function（$ q）{
 $ q-> whereNotNull（'deleted_at' ）; 
}，'<'，1） - > get（）; 
  

我相信应该给你一个没有预订的活动的集合。 >

让我知道你是怎么做的！

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?

解决方案

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!

这篇关于最少热门活动 - 获取预订为NULL或最少的活动列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！