Laravel:多个where子句中许多一对多的关系
问题描述:
我不能对laravel创建此SQL查询:Laravel:多个where子句中许多一对多的关系
select * from `events_theatre` where
event_id IN (
select id from events where (`events`.`id` = `events_theatre`.`event_id` and `user_id` = 1 and `event_type` ='theatre') and `events`.`deleted_at` is null
)
or event_id IN (
select id from events inner join events_managers on events.id = events_managers.event_id and events_managers.user_id = 1
)
and `events_theatre`.`deleted_at` is null
的Events_Theatre模型有这种关系:
public function event()
{
return $this->hasOne('App\MyEvent', 'id');
}
虽然MyEvent模型具有以下关系:
public function managers()
{
return $this->belongsToMany('App\User', 'events_managers', 'event_id', 'user_id');
}
实际上Events_Theatre模型是Event模型,Ev模型ent模型可能有许多管理者,但只有1个管理员。
我需要获取Events_Theatre(及其MyEvent),其用户是管理员或管理员之一。
我试着用这个雄辩查询:
$list = App\Events_Theatre::whereIn('event_id', function ($query) {
$query
->where(['user_id' => Auth::id(), 'event_type' => 'theatre'])
->orWhereHas('managers', function ($query) {
$query->where('user_id', Auth::id());
});
});
但我得到这个SQL查询:
select * from `events_theatre` where
exists (
select * from `events` where (
`events`.`id` = `events_theatre`.`event_id` and `user_id` = 1 and `event_type` = 'theatre'
) or exists (
select * from `users` inner join `events_managers` on `users`.`id` = `events_managers`.`user_id` where `events_managers`.`event_id` = `events`.`id` and `user_id` = 1
)
and `events`.`deleted_at` is null
) and `events_theatre`.`deleted_at` is null
但它错了。我该如何解决这个问题?
感谢
修订
我得到了答案。这是雄辩的代码,我用:
$list = Events_Theatre::whereHas('event', function ($query) {
$query
->where(['user_id' => Auth::id(), 'event_type' => 'theatre']);
})->orWhereHas('event', function ($query) {
$query
->whereHas('managers', function ($query) {
$query->where('user_id', Auth::id());
});
})->get();
在表是你的域user_id说明和EVENT_TYPE,第一选择到存在吗? –
你能说清楚你试图寻找什么样的条件。从你粘贴的所有不同的sql推断出来有点令人困惑。 –
我需要获取其用户是管理员或管理员之一的Events_Theatre(及其MyEvent)。 –