I have a many-to-many relationship between a Post model and Category model in a my laravel blog project. And I'm trying to get posts that has a particular category id like so

public function categoryPosts($category_id)
    {
        $posts = Post::whereHas('categories' , function($cat) use($category_id){
            $cat->where('id' , $category_id);
        })->get();
    }

And the route to this controller for the query is this

Route::get('/view/categories/posts/{category_id}' , [CategoryController::class , 'categoryPosts'])->name('category.posts');

And I'm getting an exception

Illuminate\Database\QueryException
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: 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 `id` in (2)))

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use HasFactory;
    
    protected $guarded =  [];

    public function categories(){
        return $this->belongsToMany(Category::class);
    }
}

Here is the category model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    use HasFactory;

    protected $fillable = ['name'];

    public function posts(){
        return $this->belongsToMany(Post::class);
    }
}

here are the migrations

public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->timestamps();
        });
    }

public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->string('title')->unique();
            $table->string('image_path');
            $table->string('slug');
            $table->text('content');
            $table->timestamps();
        });
    }

public function up()
    {
        Schema::create('category_post', function (Blueprint $table) {
            $table->id();
            $table->foreignId('category_id')->constrained();
            $table->foreignId('post_id')->constrained();
            $table->timestamps();
        });
    }

CodePudding user response：

You need to change where condition.

From

$cat->where('id' , $category_id);

to

$cat->where('categories.id' , $category_id);

CodePudding user response：

Make another table with post and categories relationship, like this:

Schema::create('posts_post_categories', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('post_categories_id')->unsigned();
            $table->foreign('post_categories_id')->references('id')->on('post_categories');
            $table->integer('post_id')->unsigned();
            $table->foreign('post_id')->references('id')->on('posts');
            $table->softDeletes();
        });

In model Post

public function category()
    {
        return $this->belongsToMany(PostCategory::class, 'posts_post_categories', 'post_id', 'post_categories_id');
    }

In model category

public function posts()
    {
        return $this->belongsToMany(Post::class,'posts_post_categories', 'post_categories_id', 'post_id');
    }

In controller to get posts, by example

public function showCategory($url){
    $category = PostCategory::where('url',$url)->first();
    $posts = $category->posts()->paginate(7);
    $recentsPosts = Post::orderBy('created_at','Desc')->take(5)->get();
    $categories = PostCategory::all();
    return view('site.blog.category.index', compact('posts','categories','recentsPosts','category'));
  }