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

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

Edit:

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.

Answer 1

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.

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')

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

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.

--

Edit

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

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>