laravel get data when give id not match with join table id

here is my category table data

id
1
2
6
7

when in my post bale I join with this category table

here is my post table sample data

  1. ID = 1,
  2. name = ‘hellow’
  3. category id = 4 (i join with category table but selected category is deleted)

here is my index SQL query (when categy_id match with the category.id) then only its fetch

$post = DB::table('posts)->join('category','posts.category_id','categories.id')-.paginate(10);

for some reason, the selected category can be deleted so I try to get category deleted post data

here is my query

$cpost = DB::table('posts')->join('categories','posts.category_id', '!=' ,'categories.id')->select('post.*')->paginate(5);

but above query duplicate post data based on available category data

i want all post data which are category id is not matched with in category table id how can i get that ?

Answer

Try this. Key is the leftJoin instead of default innerJoin (join).

        // posts without assigned or existing category
        $posts = DB::table('posts')
            ->leftJoin('category','posts.category_id','categories.id')
            ->whereNull('categories.id')
            ->paginate(10);

Leave a Reply

Your email address will not be published. Required fields are marked *