How to implement Relation in Laravel for 3 table

Question

I have 3 Table in my database :

1. Product Table for keep products
2. Options Table for keep products option values like colors , sizes and othe values
3. OptionGroups Table for keep group of options like size , color , memory and ...

In Options table i have product_id and optiongroup_id and a value like this : product id 1 have a optiongroup with id 2 and value X...

I wanna have dynamic options for each product in my db.

How i can write a relationship between these tables ?

I understand about many to many and many to many polymorphic relations but this relation is different .

Finally i want have a method in Product model to fetch OptionGroups and Options for each product

Any idea or solution ?

UPDATE below picture is my db design :

Answer 1

If the products options will be mixed information the best approach to do it is not any relation. Instead of it you should use a json field options inside the products table.

On migration you have yo do:

$table->json('options');

Then in the model, cast it to array

protected $casts = [ 'options' => 'array', ];

Later you can use it to filter

App\Product::where('options->color', 'red')->get()

You can find a full example using it for User meta in this link:

Using mysql json field in Laravel

Edited

Updating the response after after comment clarification.

If you don't want to use a json field, you can use hasManyThrough relation to do it.

In your case, one product has many optionsgroups through options, so you can do:

    class Product extends Model
    {
        public function optionGroups()
        {
            return $this->hasManyThrough(
                'App\OptionGroups',
                'App\Option',
                'product_id', // Foreign key on options table...
                'option_id', // Foreign key on option_groups table...
                'id', // Local key on products table...
                'id' // Local key on options table...
            );
        }
    }

Second edition

I updated the answer after the question was updated with the model image. I don't delete the previous cause if anyone could find it useful

What is there is a many to many between Products and and OptionsGroups with products as pivot table. So according to the documentation, to retrive the groups from products, you can do:

    class Product extends Model
    {
        public function optionGroups()
        {
            return $this->belongsToMany('App\OptionGroup', 'options');
        }
    }

And the trick is on the second parameter of the relation: 'options' that has to be the name of the options table that acts as pivot in that case. Laravel can not do it automatically due table name does not respect the convention about. Additionally is possible you have to pass extra param in order to specify the foreigns keys names you're using, like in the docu example for user and roles:

return $this->belongsToMany('App\Role', 'role_user', 'user_id', 'role_id');

I think, according with your model screenshot, you'll have to do:

return $this->belongsToMany('App\OptionGroup', 'options', 'product_id', 'optiongroup_id');

But I suggest you read the docs about and do it accordingly to your certain real specifications. You can find more info about it here: many to many relationship.

I hope this definitely solves your problem.