英文:
how to SELECT with left join where count of one table is more than 1?
问题
以下是翻译好的部分：
我已经实现了类似这样的查询：
$categories = DB::select("SELECT categories.* FROM categories left JOIN products on products.category_root = categories.id")
->withCount('products')
->where('products_count', '>', 0)
->get();
这意味着获取至少有一个具有与category->id相等的category_root的产品的类别。
我的产品表有3个列的名称：category_root，category_parent，category_id，我想在我的集合中检查它们的每一个。
如何获取非空的类别？
英文:
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 ?
答案1
得分: 1
$categories = Category::whereExists(function ($query) {
$query->select(DB::raw(1))->from('products')->whereRaw('products.category_root = categories.id');
})->get();
英文:
try
$categories = Category::whereExists(function ($query){
$query->select(DB::raw(1))->from('products')->whereRaw('products.category_root = categories.id');
})->get();
答案2
得分: 0
使用 leftJoin() 方法，您可以在闭包中添加您的条件：
$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();
英文:
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();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论