How to rewrite SQL query in Laravel?

Question

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();

How to rewrite this query using short form with:

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

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

Relations between tables:

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

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

2) User can has one or more categories

3) User_category is related with Announcement_category by category_id = category_id

Can you show the relationships you have set up between Announcement, AnnouncementCategory and User? — Rwd
– Rwd, Commented Feb 25, 2017 at 18:01
1) Which table does the delete_at belong to? 2) Shouldn't it be deleted_at if you're using SoftDeletes? 3) Do you have the relationships set up in your model e.g. Announcement belongsToMany Category and Category belongsToMany User? — Rwd
– Rwd, Commented Feb 26, 2017 at 8:24
deleted_at only in Announcement table, and it NULL if not deleted. I dont have relationships in model, because I dont aware what type of relation between AnnouncementCategory and UserCategory. No, Announcement can has many Category and it has no relations with User. User has only relation with UserCategory by field: user.id = user_category.user_id — Darama
– Darama, Commented Feb 26, 2017 at 8:26

LC Yoong · Accepted Answer · 2017-02-25 17:16:39Z

0

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.

answered Feb 25, 2017 at 17:16

LC Yoong

1,8723 gold badges16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Darama Over a year ago

And how yo use this(call)? Can you share full example?

Darama Over a year ago

Like this? Announcement::with("announcement_category")->where("user_id", 1)

Collectives™ on Stack Overflow

How to rewrite SQL query in Laravel?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related