Brand spanking new to Open Cart. I'm trying to do some data exporting.
All i need is the product id of each product option. It doesn't sound complicated but i honestly can't figure out how open cart knows what option is what product.
Example product option:
I've attached an image of what I'm talking about. You can see that after the product option in square brackets is the ID of the product. But I just can't figure out how Open Cart is associating the option to the product.
The end goal is to get a list of all product_ids that are being used as options and what products they are assigned to (non-required only)
So something like this:
from product_option_value pov
where pov.option_id in (select option_id from product_option
where required = 0 and price > 0);
Incase anyone else needs to do this - I figured it out eventually.
SELECT pov.product_id, substring_index(substring_index(ovd.name, '[', -1),']', 1) as option_product_id, p.model FROM product_option_value pov LEFT JOIN option_value ov ON pov.option_value_id = ov.option_value_id LEFT JOIN option_value_description ovd ON ov.option_value_id = ovd.option_value_id LEFT JOIN product p ON p.product_id = pov.product_id WHERE ovd.name LIKE '%[%' AND ovd.name NOT LIKE '%FREE%' AND pov.option_id IN (SELECT option_id FROM product_option WHERE required = 0);
Most of this is pretty straight forward. I'm using some likes to only get product options with product ids. I use a substring within a substring in the select to get everything between the square brackets.
This gives us a list of options and the product ids they are assigned to
( i don't have enough rep for an image so here ascii instead)
+------------+-------------------+-------+ | product_id | option_product_id | model | +------------+-------------------+-------+ | 1234 | 223 | mmss1 | | 1234 | 224 | mmss1 | | 1225 | 221 | mmss3 | | 1226 | 229 | mmss6 | +------------+-------------------+-------+
So for example product 1234 has two options, their product ids are 223 and 224
If anyone finds a better way of doing this rather than ripping product ids out of a name column please let me know as this feels like a hack.