Question 15236 in Laravel Question posted in
The official Laravel Documentation can be found here.

how to SELECT with left join where count of one table is more than 1? – Laravel

I have implemented a query like this :

$categories = DB::select("SELECT categories.* FROM categories left JOIN products on products.category_root = categories.id")
                ->withCount('products')
                ->where('products_count' , '>' , 0)->get();

that mean get categories where have at least one product with category_root equal the category->id

my products table have 3 column with names : category_root , category_parent , category_id
and I want to check each of them in my collections.

how to get not empty categories ?

Tags:

2

Answers


  1. 0

    Using leftJoin() method, You can add your condition in the closure:

    $categories = DB::table('categories')
                ->leftJoin('products', function ($join) {
                    $join->on('categories.id', '=', 'products.category_root')
                         ->orWhere('categories.id', '=', 'products.category_parent')
                         ->orWhere('categories.id', '=', 'products.category_id');
                })
                ->select('categories.*')
                ->whereNotNull('products.id')
                ->withCount('products')
                ->get();
    Login or Signup to reply.
  2. 0

    try

    $categories = Category::whereExists(function ($query){
            $query->select(DB::raw(1))->from('products')->whereRaw('products.category_root = categories.id');
        })->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search