Jump to content

Creating A Sql Query Retrieving Products Without A Specific Product Feature Specified


Diffusio

Recommended Posts

We are a web SEO company in business since fall 2011. We are located in Canada. Our staff is composed of a web strategist and two SEO specialists, one in French and the other in English. Our team is able to work with html, CSS, php, Java Script, etc. But we are not programmers! In the short term, we have some things to be done by a programmer for a Prestashop e-commerce website.

 

Creating a SQL query retrieving products without a specific product feature specified in Prestashop

 

Our first project consist of creating a SQL query retrieving all products which do not have a specific product feature specified (for example, list all products for which the "microwave safe" feature is not specified)). We already have built a query named "List products without manufacturers" in the SQL Manager of Prestashop. We would like to have similar queries for each feature. As you should know, "features" are not stored in the Prestashop database in the same table as the "features". That is why we need your help.

 

We say this is the first project because we are looking for someone to do programming jobs on a regular basis for our website

Link to comment
Share on other sites

This query will select all product IDs that don't have a specific feature set:

SELECT `p`.`id_product` FROM `ps_product` `p` WHERE `p`.`id_product` NOT IN (SELECT `id_product` FROM `ps_feature_product` WHERE `id_feature` = 5)

Where you need to change "5" to ID of the feature that you are interested in.

 

This query will select all product IDs that don't have a specific attribute set:

SELECT `id_product` FROM `ps_product` WHERE `id_product` NOT IN (SELECT DISTINCT `ppa`.`id_product` FROM `ps_product_attribute` `ppa` WHERE `ppa`.`id_product_attribute` IN (SELECT `ppac`.`id_product_attribute` FROM `ps_product_attribute_combination` `ppac` WHERE `ppac`.`id_attribute` IN (SELECT `id_attribute` FROM `ps_attribute` WHERE `id_attribute_group` = 1)))

The "1" at the end is attribute group ID and needs to be changed accordingly.

 

Queries assume that your database prefix is 'ps_' and I will be happy to cooperate with you :)

Edited by Daresh (see edit history)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...