Cart rules SQL tables

[DennyG]

Hello Guys!

 

I ran into a trouble. I need to assign a specific cart rule for a certain number of products. I started from the backend Price Rules --> Cart Rules and then created a rule that is assigned to certain products, that are being chosen manually. But since there are many products I want to assign the rule to I find it a little bit wrong doing it manually and spend couple of days on it. I guess there is a certain SQL table in the database that links product_id to the id_cart_rule. I could fill the sql table using a criteria. However I cannot find that link between the products and the rules anywhere among the database tables.

 

So, could you please advice where to search and, if there is some other logic - what to search for?

 

Thank you very much in advance!

[rocky]

It's the ps_cart_rule_product_rule_value table that contains all the products. The product IDs go in the id_item column.

[DennyG]

Thanks a lot! Will try it.

[DennyG]

It makes sense but in the cart_rule_product_rule_value table I have two columns - id_product_rule and id_item. And id_product_rule starts with number 10 and up, though the cart_rule I created has ID number 9. So there still no connection.

[rocky]

There is a connection, though it's through many tables. You'll find the id_cart_rule in the ps_cart_rule_product_rule_group that is associated with the id_product_rule_group. The id_product_rule_group is in the ps_cart_rule_product_rule table and is associated with an id_product_rule. The id_product_rule is in the ps_cart_product_rule_value table and associated with an id_item, which is the product IDs.

 

It's complicated, but it's all there.

[DennyG]

Nice! Now I got the ends together. Thank you very much! You save me several days of work.

 

One last question still: before there was information about discount for each product in the table ps_product or ps_product_shop. But I do not find the price_impact (I guess it was named like this) column there anymore. Has it migrated? The discounts are still there in the front end and can be uploaded via CSV.

[DennyG]

Aha, it is in the ps_specific_price.reduction. Thanks a lot! The issue is solved!

[Nickovitshj]

On 10/15/2016 at 4:04 AM, rocky said:

There is a connection, though it's through many tables. You'll find the id_cart_rule in the ps_cart_rule_product_rule_group that is associated with the id_product_rule_group. The id_product_rule_group is in the ps_cart_rule_product_rule table and is associated with an id_product_rule. The id_product_rule is in the ps_cart_product_rule_value table and associated with an id_item, which is the product IDs.

 

 

 

 

It's complicated, but it's all there.

 

I have a category restriction for my iPad category, id of that cat is 16.
However, the id_item is 40 when i'm looking at this table.

This is one structure of database tables which is 100% unclear to me. I want to create an SQL query where I can select the categories that I've selected as restrictions.