Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Get List of Product IDS from orders of a customer
 
rgoodrow
Member
 
Total Posts:  31
Joined:  2013-04-23
 

What I’m looking for is the Magento equivilent of the following SQL query

SELECT p.`id`
FROM `productp
JOIN 
`order_line_itemoli ON oli.`product_id` = p.`id`
JOIN `ordero ON o.`id` = oli.`order_id`
JOIN `customerc ON c.id o.`customer_id
WHERE c.id = <customer id> AND p.media = "download"

I fairly certain it starts with

$ids Mage::getResourceModel('catalog/product_collection')
           ->
AddFieldToFilter(/*something*/)
;
but don’t know how to do the joins within the api to pull out the data I’m looking for.

I am new to Magento, and trying to figure out the basics of the Magento query api, and I’d rather not loop over multiple collections, sub collections to get the data I want.

Any help is appreciated.

 
Magento Community Magento Community
Magento Community
Magento Community
 
rgoodrow
Member
 
Total Posts:  31
Joined:  2013-04-23
 

I’ve seen that design posted elsewhere, but it just seems terribly ineffient.

It’s basically loading the orders from a customer first, then interating over them to load each individual order to get the items, then further iterating over the items to get the product ids

and what I suspect is a separate sql query for each ->Load() statement.

So for a customer with 7 orders and an average of 4 items per order, that’s 29 queries being fired off, when in sql theory, it should be able to be done in just 1.

Does Magento not posses the ability to load a product collection by filtering through joins?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ryan Sun _Kuafu
Guru
 
Total Posts:  317
Joined:  2008-11-14
FL
 

$collection Mage::getModel('catalog/product')->getCollection();
        
$collection->getSelect()->join(array('oi'=>$collection->getTable('sales/order_item')), 'e.entity_id=oi.product_id')
        ->
join(array('o'=>$collection->getTable('sales/order')), 'o.entity_id=oi.order_id')
        ->
join(array('c'=>$collection->getTable('customer/entity')), 'c.entity_id=o.customer_id')
        ->
where('o.customer_id = ?'$customerId)
        ->
where('e.entity_id > ?'1);
        
$ids $collection->getAllIds();
you probably want array_unique($ids)
 
Magento Community Magento Community
Magento Community
Magento Community
 
rgoodrow
Member
 
Total Posts:  31
Joined:  2013-04-23
 

I tip my hat to you good sir.

 
Magento Community Magento Community
Magento Community
Magento Community
 
rgoodrow
Member
 
Total Posts:  31
Joined:  2013-04-23
 
Ryan Sun _Kuafu - 25 April 2013 09:37 AM

$collection Mage::getModel('catalog/product')->getCollection();
        
$collection->getSelect()->join(array('oi'=>$collection->getTable('sales/order_item')), 'e.entity_id=oi.product_id')
        ->
join(array('o'=>$collection->getTable('sales/order')), 'o.entity_id=oi.order_id')
        ->
join(array('c'=>$collection->getTable('customer/entity')), 'c.entity_id=o.customer_id')
        ->
where('o.customer_id = ?'$customerId)
        ->
where('e.entity_id > ?'1);
        
$ids $collection->getAllIds();
you probably want array_unique($ids)

so this works great for getting the Ids, however due to the way our helper functions are setup, I need to get the array of ‘catalog/product’ models that match that query.

I thought just passing $collection would pass the array of product models, but I’m getting the following error when trying that

Item (Mage_Catalog_Model_Productwith the same id "3" already exist

am I missing something to just build that query and return the array of product models?

 
Magento Community Magento Community
Magento Community
Magento Community
 
rgoodrow
Member
 
Total Posts:  31
Joined:  2013-04-23
 

I was about to get around the “already exists” error by following another post regarding Varien data collection,

however if I just return $collection, it only contains an array of 3 items, yet $collection->getAllIds() has 6 items in it.
shouldn’t the parent $collection also have 6 items in it since it’s pulling from the catalog/product model?

 
Magento Community Magento Community
Magento Community
Magento Community
 
rgoodrow
Member
 
Total Posts:  31
Joined:  2013-04-23
 

very confused by Magento....

the following code

$books->addAttributeToSort('name''ASC')
              ->
addFieldToFilter(array(array('attribute' => 'media''eq' => '10'),))
              ->
getSelect()
              ->
join(array('oi' => $books->getTable('sales/order_item')), 'e.entity_id=oi.product_id')
              ->
join(array('o' => $books->getTable('sales/order')), 'o.entity_id=oi.order_id')
              ->
where('o.customer_id = ?'$this->customer->getId())
              ->
group('e.entity_id')
        ;

fetches a collections of products.
if I

echo count($books)
, then I only get 2. However if I
echo $books->getSelect()
and run the resulting query directly against the magento database, I get 5 results.

why is magento method returning a result that is smaller than the query is generating?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ryan Sun _Kuafu
Guru
 
Total Posts:  317
Joined:  2008-11-14
FL
 

not seeing why its different, can you compare the query result with $select->getAllIds()?

 
Magento Community Magento Community
Magento Community
Magento Community
 
rgoodrow
Member
 
Total Posts:  31
Joined:  2013-04-23
 

$books Mage::getModel('catalog/product')->getCollection();
        
$books->addAttributeToSort('name''ASC')
              ->
addFieldToFilter(array(array('attribute' => 'media''eq' => '10'),))
              ->
getSelect()
              ->
distinct()
              ->
join(array('oi' => $books->getTable('sales/order_item')), 'e.entity_id=oi.product_id')
              ->
join(array('o' => $books->getTable('sales/order')), 'o.entity_id=oi.order_id')
              ->
where('o.customer_id = ?'$this->customer->getId())
              ->
group('e.entity_id')
        ;
        echo 
$books->getSelect();
        echo 
'<pre>'.var_export(count($books), true).'</pre>';
        echo 
'<pre>'.var_export($books->getAllIds(), true).'</pre>';

Run as is, Magento throws an exception of

Item (Mage_Catalog_Model_Product) with the same id “9” already exist

Looking up that error, I modified /magento/lib/Varien/Data/Collection.php to comment out the line throwing the exception about duplicates.

Run again, and I get the following output

SELECT DISTINCT `e`.*, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`, `at_media`.`value` AS `media`, `oi`.*, `o`.* FROM `catalog_product_entity` AS `e` LEFT JOIN `catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = ‘71’) AND `at_name_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = ‘71’) AND (`at_name`.`store_id` = 1) INNER JOIN `catalog_product_entity_int` AS `at_media` ON (`at_media`.`entity_id` = `e`.`entity_id`) AND (`at_media`.`attribute_id` = ‘150’) AND (`at_media`.`store_id` = 0) INNER JOIN `sales_flat_order_item` AS `oi` ON e.entity_id=oi.product_id INNER JOIN `sales_flat_order` AS `o` ON o.entity_id=oi.order_id WHERE ((at_media.value = ‘10’)) AND (o.customer_id = ‘3’) ORDER BY `name` ASC

2

array (
0 => ‘29788’,
1 => ‘28819’,
2 => ‘29968’,
3 => ‘29827’,
4 => ‘29857’,
)

Running the generated query against the database directly also generates the same number of results that I expect (5), except when running a count() on the returned collection itself, I only get 2 for some reason.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ryan Sun _Kuafu
Guru
 
Total Posts:  317
Joined:  2008-11-14
FL
 

OK, in each join method, give an empty array [array()] as 3rd parameter, this way you won\’t have the duplicate error either.
Magento does have a nasty collection class

 
Magento Community Magento Community
Magento Community
Magento Community
 
rgoodrow
Member
 
Total Posts:  31
Joined:  2013-04-23
 
Ryan Sun _Kuafu - 26 April 2013 10:13 AM

OK, in each join method, give an empty array [array()] as 3rd parameter, this way you won\’t have the duplicate error either.
Magento does have a nasty collection class

Thank you, that was the magic that I was missing.

Do you have a link anywhere to documentation on the ->join method? I’m thrilled that it’s working, I’d just like to know <i>why</i> it wasn’t working before but is now.

Thanks again!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Ryan Sun _Kuafu
Guru
 
Total Posts:  317
Joined:  2008-11-14
FL
 

code is your best documentation ;P

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top