How to access data from multiple many to many relationships in Laravel

1
votes

I have a database with the following tables:

products

  • id

  • name

materials

  • id

  • name

units

  • id

  • name

products and materials table has many to many relationships, also materials and units table have many to many relationships as well so these are 2 pivot tables

material_product

  • material_id

  • product_id

material_unit

  • material_id

  • unit_id

Now from these relations, I know about the material that is used in the product but how will I find out the associated unit of each material as there are many to many relations between materials and units table.

For example, if there is a Product-A which has Aluminum as one of the material and Aluminum is linked to both Kg and Ton on units table. Now how will I get the correct unit of Aluminum when I access the Product-A.

Can I do this in Laravel, instead of making materials and products relation I relate products table to material_unit pivot table as such

material_unit_product

  • product_id

  • material_unit_id

and yes then there will be an increment field in material_unit table as well which will act as id so material_unit table will be

material_unit

  • id

  • material_id

  • unit_id

  • unique([material_id, unit_id)]

Or can I use the id from the material_unit pivot table in the material_product pivot table as an extra field?

Or is this a better way to do this type of relationship in Laravel? I don't know how to do this properly, any help will be much appreciated.

Thanks

1
laraveleloquentmany-to-many
Depending on the use-case I would think that it is indeed an option to use the material_unit_id and product_id in material_product. Depending on what you are storing that is. I would consider the material_unit a list of possible units to use for this material, and material_unit_product a list of concrete material units that belong to a product. You might even consider a table with all 3 id's in them: material_id, unit_id, product_id. This way you simply say: This product has this material in this unit. – Rob Biermann
@RobBiermann thanks for the comment, Does Laravel model support connecting 3 tables in one pivot table, is there any way we can use it in eloquent relation? I am also thinking of adding material_unit_id in material_product pivot table? But I don't think it is the best approach. – MAY
why don't material and unit used one to one relationship ? is Aluminum sometimes contain liter unit ? non sense right ? – Elbo Shindi Pangestu
Is aluminum has more than one unit at the same time ? If no, you can attach unit_id at your product column, one to one relationship. If yes, you can use this (stackoverflow.com/questions/61660436/…) solution. I have same problem too. Hope it helps ^^ – Elbo Shindi Pangestu
@ElboS.P. Yes, Aluminum has more than one unit at the same time. Thanks for pointing me to those links. Instead of adding id to material_unit pivot table we can use id of units table and link it to material_product pivot table, in that way we can avoid the creation of material_unit pivot table. – MAY

1 Answers

1
votes

After a lot of searches and posting the same question on another forum, I came to know that there is no such Laravel way to handle this problem. What helped me is I used the id of pivot table material_unit as a foreign key in the material_product pivot table and it worked fine. I just had to specify it in the model while defining a relationship using withPivot. After that, I can easily get the id of the material_unit pivot table and using that id I was able to find the related unit of the material for that specific product.

Comment of RobBiermann on this question and comment of bugsyha from Laracast forum also helped me use this approach.