Frank Frank - 2 months ago 8
SQL Question

Open Cart - Get the product id of a product option - sql

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:

enter image description here

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:

select pov.*
from product_option_value pov
where pov.option_id in (select option_id from product_option
where required = 0 and price > 0);


If product_option_value actually had the product_id of the option in it. Forgive me if I'm being stupid.

Answer

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.