0

I have a table of data "products" and a second table "product_components".

The "products" are made up of any number of "product_components" each of which can also have any number of "product_components" themselves. Products and components are linked using a third table "product_bom".

Costs are stored at the "product_components" level.

I can easily get the price for each "products" line, by adding up its components, and I can repeat the query within itself to look for a second layer of components.

Can anyone suggest how I might infinitely check for another level of components for each component of each component...........etc.?

Thanks,

Euphbasio

Update: This is my basic query for the top level. I could nest a copy of this in the resulting loop and again under that, however this would mean a finite number of possibilities.

SELECT `product_bom`.*, `products_boml`.`product_code` AS `pcode`,`products_boml`.`title` AS `title`, IF(`currency_rates`.`rate`>0,`products_boml`.`cost` * `currency_rates`.`rate`,`products_boml`.`cost`) AS `pcost`,`currency`.`currency` AS `curletters`FROM `product_bom` LEFT JOIN `products` `products_boml` ON `products_boml`.`id`=`product_bom`.`bom_part_id`
LEFT JOIN `products` `products_tlp` ON `products_tlp`.`id`='$product_id'
LEFT JOIN `business_relations` `br_boml_vendor` ON `br_boml_vendor`.`id`=`products_boml`.`vendor_id`
LEFT JOIN `business_relations` `br_tlp_vendor` ON `br_tlp_vendor`.`id`=`products_tlp`.`vendor_id`
LEFT JOIN `currency_rates` ON `currency_rates`.`base_currency_id`=`br_boml_vendor`.`currency` AND `currency_rates`.`exchange_currency_id`=`br_tlp_vendor`.`currency`
LEFT JOIN `currency` ON `currency`.`id`=`br_tlp_vendor`.`currency`
WHERE `product_bom`.`product_id`='$product_id'
ORDER BY `products_boml`.`product_code`

I'm converting for currency also, but that can be ignored.

Thanks.

euphbasio
  • 1
  • 1
  • mysql doesn't do well with infinite recursion however you can achieve similar results using slight different data models http://stackoverflow.com/questions/3704130/recursive-mysql-query or by doing something like a stored procedure http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html (however the SP route you would have to manually code in each depth level) – Dave Jan 15 '15 at 11:30
  • Thanks, I'll go have a read of these. Seems that there aren't too many options... – euphbasio Jan 15 '15 at 12:40
  • So it looks like what I want is closure tables from (http://www.slideshare.net/billkarwin/models-for-hierarchical-data), that'll make the whole thing easier, if a bit more data intensive. Thanks Dave for your help............now how do I thank you officially on here? – euphbasio Jan 15 '15 at 12:48

1 Answers1

0

I had a similar problem myself, what I did was set a "parent_id" for the top level in product_components and it would search for products with the product_components id as their parent_id.

Let me know if this makes sense :)!

So for example:

Product_1 is made from Product_2 and Product_3, Product_3 is made from Product_4 and Product_5.

Product_3 id is 3, Product_4 and Product_5 have the parent_id of 3.

So to make Product_1 it would go through Product_2 and look for "parent_id = 2" find none, go to Product_3 and look for "parent_id = 3" and I think you get the jist.

Another way is to add a field called, "components" and separate the components with a comma (distinguishing the id's) and use "explode" to get a list, i.e. Product_3 components column would read "4,5" and you would use $productarray = explode(',',Product_3). (pseudo-code)

Robert Pounder
  • 1,490
  • 1
  • 14
  • 29
  • Thanks for this. I'm not sure these would allow for infinite recursion though? For example, if components have components, which have components...there are still an unknown number of children. Trying to get my head around it.... – euphbasio Jan 15 '15 at 12:33
  • "Another way is to add a field called, "components" and separate the components with a comma (distinguishing the id's) and use "explode" to get a list, i.e. Product_3 components column would read "4,5" and you would use $productarray = explode(',',Product_3). (pseudo-code)" Did you read full post :P – Robert Pounder Jan 15 '15 at 14:24