I need to query all products in my database that have the same custom attribute value. The end results is I need the product IDs
What tables do I need to JOIN? What would the script look like?
All Attributes are stored in eav_attribute table you willnever need to join table you can write query filter by attribute
// Your Attribute code $attribute_code='recommend' $products = Mage::getResourceModel('catalog/product_collection'); // Select which fields to load into the product // * will load all fields but it is possible to pass an array of // select fields to load $products->addAttributeToSelect('*'); // Ensure the product is visible $products->addAttributeToFilter('visibility', array('neq' => 1)); // Ensure the product is enabled $products->addAttributeToFilter('status', 1); // Add attribute name $products->addAttributeToFilter('$attribute_code,true); // Limit the collection to 1 result $products->setCurPage(1)->setPageSize(1); // Load the collection $products->load();
Thank you for this, the reason I need the product IDs is this.
I have 6000 products in my accounting database (attribute my_product_id )
These turn into 31,000 products which I need for my layered navigation (car parts: make(dropdown), model(dropdown), year(multi-select) - different cars use the exact same part, but are sold as separate parts). Too complex to use as configurable product (I think)
The inventory is stored in the cataloginventory_stock_item table and holds the magento product IDs
So I could potentially have 1 actual product which displays as 10 different products on the website
So whenever a purchase is made, I want to write a script to search the whole product database for all products that share that attribute ID (my_product_id) and adjust their inventory accordingly
I will also write a script for updating inventory levels and price (a different thread on here) So if I have 5 qty of my_product_id, then the 10 products it will turn into will all have 5 qty when I import
To query all products that have the same custom attribute value, you'll need to interact with your database's tables that store product information and attributes. The specific tables and structure will depend on the e-commerce platform or database schema you're using. However, I can provide you with a general outline of how the query might look using SQL:
Let's assume you have the following tables:
Here's a basic SQL query to achieve your goal:
SELECT p.product_id FROM products p JOIN product_attributes pa ON p.product_id = pa.product_id WHERE pa.custom_attribute = 'your_desired_value';
Explanation of the query:
SELECT p.product_id: This selects the product_id from the products table.
FROM products p: This specifies the products table and assigns it the alias p.
JOIN product_attributes pa ON p.product_id = pa.product_id: This performs an inner join between the products and product_attributes tables based on the product_id column, linking the two tables together.
WHERE pa.custom_attribute = 'your_desired_value': This filters the results to only include rows where the custom_attribute in the product_attributes table matches the value you're interested in.
Replace 'your_desired_value' with the actual value you want to search for in the custom_attribute field.
Please note that the actual table and column names in your database might differ from the examples provided Meguiar’s here. You'll need to adjust the query to match your database schema. Also, if you're using a specific e-commerce platform, it might provide its own query builder tools or APIs to fetch this information more easily. Always refer to the documentation of your platform for the most accurate and relevant instructions.