I have the below tables
posts - id - name categories - id - name category_post - post_id - category_id
Post.php
public function Category() { return $this->belongsToMany(Category::class); }
Category.php
public function posts() { return $this->belongsToMany(Post::class); }
A post may contain many categories.
I want to query all posts related to the categories of any given post in the least number of database queries.
For example, If a post belongs to three categories, I want to get all the posts related to that three categories. I could achieve this in 4 DB queries as below.
$post = Post::find(1); $categoryIds = $post->category()->pluck('id'); $postIds = DB::table('category_post') ->whereIn('category_id', $categoryIds) ->pluck('post_id') ->unique(); $relatedPosts = DB::table('posts') ->whereIn('id', $postIds) ->get();
Any help would be highly appreciated to reduce DB queries or refactor the code in laravel way. Thanks
Advertisement
Answer
Your given example can be written like:
$postWithRelatedPosts = Post::whereHas('category.post', function ($q) { $q->where('id', 1); })->get();
This is a single query but has 2 sub-queries within it. It generates something like:
select * from `posts` where exists ( select * from `categories` inner join `category_post` on `categories`.`id` = `category_post`.`category_id` where `posts`.`id` = `category_post`.`post_id` and exists ( select * from `posts` inner join `category_post` on `posts`.`id` = `category_post`.`post_id` where `categories`.`id` = `category_post`.`category_id` and `id` = ? ) )