Magento Forum

Select attributes from configurable and simple products at same time
 
HSREuf
Jr. Member
 
Total Posts:  4
Joined:  2012-10-26
 

Hello,

I am trying to query the magento database but I need some information that is on the Configurable and some other that is on the simple product.
E.g: I need the sku prices name from the configurable and from the Smple i need the stock (that will be the sum of all sizes)

With this I get the stock:

$simplecollect Mage::getModel(\'catalog/product\')
            ->
getCollection()
            ->
addAttributeToSelect(\'name\')
            ->
addAttributeToSelect(\'color\')
            ->
addAttributeToSelect(\'attribute_set_id\')
            ->
addAttributeToFilter(\'type_id\'\'Simple\')
            ->
groupByAttribute(\'name\')
            ->
groupByAttribute(\'color\')
            ->
joinField(\'qty\',
                  
\'cataloginventory/stock_item\',
                  
\'SUM(qty) AS qty\',
                  
\'product_id=entity_id\',
                  
\'{{table}}.stock_id=1\',
                  
\'left\')

With this I guet the rest ofthe info that i need

$confcollect Mage::getModel(\'catalog/product\')
            ->
getCollection()
            ->
addAttributeToSelect(\'name\')
            ->
addAttributeToSelect(\'color\')
            ->
addAttributeToSelect(\'attribute_set_id\')
            ->
addAttributeToSelect(\'type_id\');
            ->
addAttributeToFilter(\'type_id\'\'Configurable\');

I need to \"join\" the 2 collections +- like in the following SQL but using the catalog/product so I can reuse the existent code.

select from 
(
SELECT `e`.*, `at_price`.`value` AS `priceFROM 
`catalog_product_entity` AS `e
LEFT JOIN 
`catalog_product_entity_decimal` AS `at_priceON (`at_price`.`entity_id` = `e`.`entity_id`) 
AND (`
at_price`.`attribute_id` = \'75\') AND (`at_price`.`store_id` = 0WHERE (`e`.`type_id` = \'Configurable\')
x
join
(
SELECT `e`.*, `at_name`.`value` AS `name`, `at_color`.`value` AS `color`, SUM(qty) AS `qtyFROM `catalog_product_entity` AS `e
INNER JOIN `catalog_product_entity_varchar` AS `at_nameON (`at_name`.`entity_id` = `e`.`entity_id`) 
AND (`
at_name`.`attribute_id` = \'71\') AND (`at_name`.`store_id` = 0
INNER JOIN `catalog_product_entity_int` AS `at_colorON (`at_color`.`entity_id` = `e`.`entity_id`) AND (`at_color`.`attribute_id` = \'92\') AND (`at_color`.`store_id` = 0
LEFT JOIN `cataloginventory_stock_item` AS `at_qtyON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1
WHERE (`e`.`type_id` = \'Simple\'GROUP BY `at_name`.`value`, `at_color`.`value`
z
on x
.sku=substring(z.sku,  1LENGTH(z.sku)-3)
In this SQL the x returns the Configurables and z the simple products
 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top