Laravel Eloquent 嵌套查询 [英] Laravel Eloquent nested query

问题描述

我在使用 Laravel 时陷入了困境.我有以下型号:

I was working with Laravel and got stuck in a situation. I have following models:

• 类别
• 产品
• 类别产品

CategoryProduct 保存了哪个产品属于哪个类别的信息(一个产品可能属于多个类别).

CategoryProduct holds the information about which product belongs to which category (a product may belong to multiple categories).

现在，当我想加载属于特定类别的所有产品时，我需要对 Product 和 CategoryProduct 运行查询，这就是我卡住的地方.

Now, when I want to load all products belonging to a particular category, I need to run query on Product and CategoryProduct which is where I'm stuck.

我进行了以下尝试，但没有成功:

I gave it the following try but was unsuccessful:

$products = Product::where('status', '=', 'active')
->where('category_id', '=', $category_id)
->take($count)
->skip($skip)
->get();

显然，它会说 category_id 不是列.

Obviously, it will say that category_id is not a column.

这是我的数据库 &模型结构:

Here is my DB & Model structure:

身份证，名称，等

身份证，名称，单品，等

id, name, sku, etc.

身份证，product_id, (Product.id 的外键)category_id, ( Category.id 的外键)等

id, product_id, ( Foreign key to Product.id ) category_id, ( Foreign key to Category.id ) etc.

class Product extends Eloquent {

protected $table = 'products';

protected $hidden = array();

    public static $rules = array('name' => 'required|min:3');

}

类别模型

class Category extends Eloquent {

protected $table = 'categories';

public static $rules = array('name' => 'required|min:3');

}

类别产品型号

<?php

class CategoryProduct extends Eloquent {

protected $table = 'category_products';

public function product()
{
    return $this->belongsTo('Product');
}

public function category()
{
    return $this->belongsTo('Category');
}
}

更新

关于这个的新问题

我正在尝试展示产品.如果没有通过类别(值为-1)，那么我将显示所有产品，否则我将显示通过类别中的产品.

I'm trying to display products. If category is not passed (value is -1), then I will show all products, otherwise I will show products from the passed category.

现在，当我展示所有产品时，这些产品可能已经存在于一个类别中.我想为已经在一个类别中的产品显示勾选的复选框.我正在做这样的事情:

Now, when I show all products, those products may already exist in a category. I want to display ticked checkbox for products that are already in a category. I'm doing something like this:

if($category_id==-1)
        $products = Product::where('status', '=', 'active')->take($count)->skip($skip)->get();
    else{
        $products = Product::whereHas('categories', function($q) use ($category_id)
        {
            $q->where('category_id', $category_id);
        })->where('status', 'active')
            ->take($count)
            ->skip($skip)
            ->get();
    }

表category_products有product_id、category_id作为列.

The table category_products have product_id, category_id as columns.

现在，查询:

$products = Product::where('status', '=', 'active')->take($count)->skip($skip)->get();

$products = Product::where('status', '=', 'active')->take($count)->skip($skip)->get();

只会从产品表中挑选产品.如果我在 category_products 中检查每个产品是否存在，那么对于大量产品的数据库查询将过多.

will pick products only from products table. If I check each product for its existence in category_products, then there will be too many database queries for large number of products.

任何想法，如何实现这一点.我希望我能够清除我的情况.谢谢

Any idea, how to achieve this. I hope I was able to clear my situation. Thanks

推荐答案

CategoryProduct 模型应该不是必需的，除非除了 product_id 和 category_id 之外还有其他字段指向其他关系.

The CategoryProduct model should not be necessary unless you have additional fields besides product_id and category_id which point to other relationships.

在Category和Product模型上建立关系的方法是什么.

What is necessary are the methods for setting up the relationship on the Category and Product models.

在Category中，添加关系函数...

In Category, add the relationship function...

public function products()
{
    return $this->belongsToMany('Product', 'category_products');
}

在您的 Product 模型中，对类别执行相同的操作.

In your Product model, do the same for categories.

public function categories()
{
    return $this->belongsToMany('Category', 'category_products');
}

然后您可以使用您的关系方法和 whereHas()

Then you can query for your active products that belong to that category using your relationship method and whereHas()

$products = Product::whereHas('categories', function($q) use ($category_id)
{
    $q->where('id', $category_id);
})->where('status', 'active')
->take($count)
->skip($skip)
->get();

这篇关于Laravel Eloquent 嵌套查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！