query all products that have the same custom attribute value

[justinblayney]

Hello,

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?

[Magikvishal]

Hi Justin,

All Attributes are stored in eav_attribute table you willnever need to join table you can write query filter by attribute

Ex:-

// 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();

[justinblayney]

Hello,

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