Laravel Eloquent 嵌套查询

Laravel Eloquent nested query

提问by Ashutosh

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

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

• Category
• Product
• CategoryProduct

• 类别
• 产品
• 类别产品

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

CategoryProduct保存有关哪个产品属于哪个类别的信息（一个产品可能属于多个类别）。

Now, when I want to load all products belonging to a particular category, I need to run query on Productand CategoryProductwhich is where I'm stuck.

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

I gave it the following try but was unsuccessful:

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

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

Obviously, it will say that category_idis not a column.

显然，它会说那category_id不是一个列。

Here is my DB & Model structure:

这是我的数据库和模型结构：

categories table

类别表

id, name, etc.

id、姓名等

products table

产品表

id, name, sku, etc.

id、名称、sku 等

category_products table

category_products 表

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

id、product_id、（Product.id 的外键）category_id、（Category.id 的外键）等。

Product model

产品型号

class Product extends Eloquent {

protected $table = 'products';

protected $hidden = array();

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

}

Category model

品类模型

class Category extends Eloquent {

protected $table = 'categories';

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

}

CategoryProduct model

类别产品型号

<?php

class CategoryProduct extends Eloquent {

protected $table = 'category_products';

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

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

Update

更新

A new question on this

关于这个的新问题

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.

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

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();
    }

The table category_productshave product_id, category_id as columns.

表category_products有 product_id, category_id 作为列。

Now, the query:

现在，查询：

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

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

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

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

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

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

采纳答案by user1669496

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

的CategoryProduct，除非你有另外的product_id和category_id的附加字段，其指向其他关系模型不应该是必要的。

What is necessary are the methods for setting up the relationship on the Categoryand Productmodels.

需要的是在Category和Product模型上建立关系的方法。

In Category, add the relationship function...

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

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

In your Productmodel, do the same for categories.

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

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

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

然后您可以使用您的关系方法查询属于该类别的活跃产品 whereHas()

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

回答by Padarom

You don't need a model for a pivot table in Many-to-Many relationships. Look at this sectionof the Eloquent documentation for further explanation.

您不需要多对多关系中的数据透视表模型。查看Eloquent 文档的这一部分以获得进一步的解释。

You still need to create a migration to set up the pivot table (or do it manually if you don't use migrations), but not a model. Instead, create a function for Categoryto designate the relationship:

您仍然需要创建迁移来设置数据透视表（或者如果您不使用迁移，则手动进行），而不是模型。相反，创建一个用于Category指定关系的函数：

public function products()
{
    return $this->belongsToMany('App\Product', 'category_products');
    // - You might need to adjust the namespace of App\Product
    // - category_products refers to the pivot table name
}

Likewise, Productneeds a similar public function.

同样，Product需要一个类似的公共功能。

Then you're able to do it the other way around, by finding the category and then listing all its related products:

然后你可以反过来做，通过查找类别然后列出所有相关产品：

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

This questioncould also be relevant to yours.

这个问题也可能与你有关。