How to Join Only category if subcategory is null?-CodePudding

How do I join Category if Subcategory is Null. The below query builder is working for Category that have Subcategory too, but not working for those category where Subcategory is Null.

public function Index(){
    $post = DB::table('posts')
    ->join('categories', 'posts.category_id', 'categories.id')
    ->join('subcategories', 'posts.subcategory_id', 'subcategories.id')
    ->select('posts.*', 'categories.category_en', 'subcategories.subcategory_eng')
    ->orderBy('id', 'desc')->paginate(5);
    return view('backend.post.index', compact('post'));
}

CodePudding user response：

Use leftJooin to subcategories. See more about Left Join in Laravel. As you didn't shared table schema of above tables to understand the relationship between them. If subcategories table have column category_id which relate subcategory to it parent than follow the below code:

->leftJoin('subcategories', 'categories.id', 'subcategories.category_id')

Replace above written line of code in your function like I did below:

public function Index(){
    $post = DB::table('posts')
    ->join('categories', 'posts.category_id', 'categories.id')
    ->leftJoin('subcategories', 'categories.id', 'subcategories.category_id')
    ->select('posts.*', 'categories.category_en', 'subcategories.subcategory_eng')
    ->orderBy('posts.id', 'desc')->paginate(5);
    return view('backend.post.index', compact('post'));
}

CodePudding user response：

add whereNull condition for parent id is null.

CodePudding user response：

Please use leftjoin in your query:

public function Index(){
    $post = DB::table('posts')
    ->join('categories', 'posts.category_id', 'categories.id')
    ->leftJoin('subcategories', 'posts.subcategory_id', 'subcategories.id')
    ->select('posts.*', 'categories.category_en', 'subcategories.subcategory_eng')
    ->orderBy('id', 'desc')->paginate(5);
    return view('backend.post.index', compact('post'));
}