How to query a pivot table with multiple Laravel 5 Eloquent foreign keys

Question

I have the following table structure:

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

My relationship is like this:

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

or

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

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

Does anyone know how I can make sure that it only downloads values ​​belonging to this product?

I could establish a relation to the product directly to the parameter values, but I really need them to be bound to the parameter to which they belong.

Edit:

I tried this with the union as follows:

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

But he gives the same results.

At the moment, I have implemented a really unpleasant solution, in my opinion, I am doing 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 does not seem right to me.

Answer 1

, product_options , .

, , :

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

. , .

, Eloquent (Laravel ORM) :

-- 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.

, , . , , , .

, . :

$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() . , .

, , , .

-

.

:

$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.

, :

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