如何在Laravel中重写SQL查询? [英] How to rewrite SQL query in Laravel?

问题描述

我使用以下查询生成器:

I use the following Query Builder:

$res = Announcement::whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('announcement_category')
                    ->join('user_category', 'user_category.category_id', '=', 'announcement_category.category_id')
                    ->where('user_category.user_id', 1)
                    ->where('announcement_category.announcement_id', '=', 'announcements.id')
                    ->whereNull('deleted_at');
            })->get();

如何使用简短格式with重写此查询:

How to rewrite this query using short form with:

Announcement::with("announcement_category")...

因为，在此查询中，构建器最后添加了conditon和where delete_at NOT null，所以它是错误的.

Because, in this query builders adds conditon and where delete_at NOT null in the end, and it works wrong.

表之间的关系:

Announcement           Announcement_category          User_category
_____________          ____________________________   _______________
id | name             announcement_id | category_id   user_id | category_id

1)Announcement可以具有一个或多个类别(Announcement has many Announcement_category)

1) Announcement can has one or more categories (Announcement has many Announcement_category)

2)用户可以具有一个或多个类别

2) User can has one or more categories

3)User_category通过category_id = category_id

推荐答案

您应该先定义Announcement和AnnouncementCategory之间的关系

You should define the relationship between Announcement and AnnouncementCategory first

public function announcement_category() {
 return $this->hasMany(AnnouncementCategory::class, 'announcement_id')
}

然后，在关系中添加条件，甚至在关系函数中对其进行设置.

Then, add condition to the relationship as you go or even set it up in the relationship function.

这篇关于如何在Laravel中重写SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！