Laravel - query to join tables in Many to many relation

user_17231425 0
Viewed 74

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

laravel mysql sql php
1 Answers

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` = ?
    )

)
user_4878130
Related