如何使用多个外键查询数据透视表Laravel 5 Eloquent [英] How to query pivot table with multiple foreign keys Laravel 5 Eloquent

问题描述

我具有以下表格结构:

| products | product_options | product_option_values | product_option_to_product |
+----------+-----------------+-----------------------+---------------------------+
| id       | id              | id                    | id                        |
|          |                 |                       | product_id                |
|          |                 |                       | product_option_id         |
|          |                 |                       | product_option_value_id   |

我的关系如下:

// Product.php
public function options()
{
    return $this->belongsToMany('App\ProductOption', 'product_option_to_product', 'product_id', 'product_option_id')->withPivot('product_option_value_id', 'product_id');
}

// ProductOption.php
public function values()
{
    return $this->belongsToMany('App\ProductOptionValue', 'product_option_to_product', 'product_option_id', 'product_option_value_id')->withPivot('product_id');
}

现在，当我尝试此操作时:

Now when i try this:

$products = Product::with('options')->with('options.values')->first();

或

$products = Product::with('options.values')->first();

我获得所有可能的选项值，因为它不使用产品ID来加载选项值.

I get all the options possible values because it does not use the product id to load the option values.

有人知道我如何确保它仅加载属于该产品的值吗?

Does anyone know how I can make sure it only loads the values that belong to that product?

我可以直接在产品上与选项值建立联系，但是我确实需要将它们附加到它们所属的选项上.

I could make a relation on the product, directly to the option values, but I really need them attached to the option they belong to.

我用这样的联接尝试了它:

I tried it with a join like this:

$products = Product::with(['options.values' => function($q) {
    $q->join('products', 'products.id', '=', 'product_option_to_product.product_id');
}])->first();

但是它给出了相同的结果.

But it gives the same results.

我目前已经实施了一个非常讨厌的修复程序，在我看来，我需要进行一些检查:

I've implemented a really nasty fix for now, in my view i perform a little check:

@if($value->pivot->product_id == $product->id)
    <div class="checkbox">
        <label>
            <input type="checkbox" name="{{ $option->id }}" value="{{ $value->id }}"> {{ $value->language->name }}
        </label>
    </div>
@endif

但是，这对我来说不合适.

However, this doesn't feel right to me.

推荐答案

您的问题在于表product_options中的记录不知道您要检索的产品.

Your issue resides in the fact that records from table product_options don't know the product you're retrieving.

根据您在问题中提到的数据库结构，我写了以下几行:

Based in the database structure you mentioned in the question, I wrote the following lines:

$product = App\Product::with('options')->with('options.values')->first();
foreach ($product->options as $option) {
    foreach ($option->values as $value) {
        printf(
            "Product: %s, Option: %d, Value: %d\n",
            $product->id,
            $option->id,
            $value->id
        );
    }
}

上面的代码仅打印特定产品的选项和值的可能组合.我使用的是您在应用程序中创建的相同关系.

The code above simply prints the possible combinations of options and values for a specific product. I'm using the same relations you created in your application.

当我加载该代码时，Eloquent(Laravel的ORM)在数据库中运行以下查询:

When I load that code, Eloquent (Laravel's ORM) runs the following queries in the database:

-- Retrieves the first product
select * from `products` limit 1;

-- Retrieves all options using the product_id as condition
select `product_options`.*, `product_option_to_product`.`product_id` as `pivot_product_id`, `product_option_to_product`.`product_option_id` as `pivot_product_option_id`, `product_option_to_product`.`product_option_value_id` as `pivot_product_option_value_id` 
from `product_options` inner join `product_option_to_product` on `product_options`.`id` = `product_option_to_product`.`product_option_id` 
where `product_option_to_product`.`product_id` in ('1')

-- Retrieves all values using the option_id as condition
select `product_option_values`.*, `product_option_to_product`.`product_option_id` as `pivot_product_option_id`, `product_option_to_product`.`product_option_value_id` as `pivot_product_option_value_id`, `product_option_to_product`.`product_id` as `pivot_product_id` 
from `product_option_values` inner join `product_option_to_product` on `product_option_values`.`id` = `product_option_to_product`.`product_option_value_id` 
where `product_option_to_product`.`product_option_id` in ('1', '2')

如您所见，最后一个查询没有使用product_id作为检索值的条件，因为product_options表没有保存对products表的引用.

As you can see, the last query doesn't use the product_id as a condition to retrieve the values, because product_options table doesn't hold a reference to products table.

最后，我取得了与您相同的结果.尽管我正在搜索产品，但所有值都与一个选项相关.

Finally, I've achieved the same result as you. All values related to an option despite the product I'm searching.

最简单的解决方法是在调用关系时添加查询约束.像这样:

The easiest way to fix that is adding a query constraint when calling your relation. Like this:

$product = App\Product::with('options')->with('options.values')->first();
foreach ($product->options as $option) {
    foreach ($option->values()->where('product_id', $product->id)->get() as $value) {
        printf("PRO: %s, OPT: %d, VAL: %d<br>\n", $product->id, $option->id, $value->id);
    }
}

请注意，我没有使用$option->values()作为方法，而没有将$option->values作为属性.调用关系方法时，可以像使用查询生成器一样使用它.

Notice that instead of calling $option->values as a property, I've made use of $option->values(), as a method. When calling a relation method you can use it like you use a query builder.

如果您不想使用此解决方法，则可能需要更改数据库结构和模型类.

If you don't want to use this workaround, you'll probably need to change your database structure and model classes.

-

另一种解决方案可能是使用查询生成器来获取特定产品的所有值.

An alternative solution could be using a query builder to get all values for a specific product.

您可以在控制器中执行以下操作:

You can do that with the following lines in your controller:

$values = ProductOptionValue::select([
    'product_option_values.*',
    'product_option_to_product.option_id',
])
    ->join('product_option_to_product', 'product_option_to_product.product_option_value_id', '=', 'product_option_values.id')
    ->where('product_id', $product->id)
    ->get();

这样，您可以获取特定产品(由其$product->id给出)及其各自的option_id的所有值.

This way you get all values for a specific product (given by its $product->id) with its respective option_id.

要在模板中使用上面查询的结果，您只需对复选框名称做一点改动:

To use the result from the query above in your template, you should do only a small change in the checkbox name:

<div class="checkbox">
    <label>
        <input type="checkbox" name="{{ $value->option_id }}" value="{{ $value->id }}"> {{ $value->language->name }}
    </label>
</div>

这篇关于如何使用多个外键查询数据透视表Laravel 5 Eloquent的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！