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

How can I use UNION for 2 collections ? Or merge collections ? (for use in adminhtml grid)
 
nodeny
Jr. Member
 
Total Posts:  3
Joined:  2013-02-28
 

First of all, what I’m trying to do :
> Get a collection with customers who does not have orders yet and newletters subscribers who dot not have customers account.

In mysql FULL JOIN and FULL OUTER JOIN does not exist so my basic sql query is :

SELECT 
        customerEntity
.entity_idcustomerEntity.emailsalesOrder.entity_id AS order_idnewsletterSubscriber.subscriber_idnewsletterSubscriber.subscriber_emailnewsletterSubscriber.customer_id 
    FROM 
        customer_entity 
AS customerEntity
    LEFT JOIN 
        sales_flat_order 
AS salesOrder ON customerEntity.entity_id salesOrder.customer_id
    LEFT JOIN 
        newsletter_subscriber 
AS newsletterSubscriber ON customerEntity.entity_id newsletterSubscriber.customer_id WHERE (salesOrder.entity_id is NULL)
    
UNION
    SELECT 
        customerEntity
.entity_idcustomerEntity.emailsalesOrder.entity_id AS order_idnewsletterSubscriber.subscriber_idnewsletterSubscriber.subscriber_emailnewsletterSubscriber.customer_id FROM customer_entity AS customerEntity
    LEFT JOIN 
        sales_flat_order 
AS salesOrder ON customerEntity.entity_id salesOrder.customer_id
    RIGHT JOIN 
        newsletter_subscriber 
AS newsletterSubscriber ON customerEntity.entity_id newsletterSubscriber.customer_id WHERE (salesOrder.entity_id is NULL)

I’m trying to transpose this by using collections to use it with a grid :

$customersSubscribersLeftJoin Mage::getModel('customer/customer')->getCollection();
        
$customersSubscribersLeftJoin->getSelect()
            ->
reset(Zend_Db_Select::COLUMNS)
            ->
columns(array('entity_id''email'))
            ->
joinLeft(array('salesOrder' => $resource->getTableName('sales/order')), 'e.entity_id = salesOrder.customer_id', array(
                
'entity_id'    => 'salesOrder.entity_id AS order_id'
            
))
            ->
joinLeft(array('newsletterSubscriber' => $resource->getTableName('newsletter/subscriber')), 'e.entity_id = newsletterSubscriber.customer_id', array(
                
'subscriber_id'        => 'subscriber_id',
                
'subscriber_email'    => 'subscriber_email',
                
'customer_id'        => 'customer_id'
            
))
            ->
reset(Zend_Db_Select::WHERE)
            ->
where('salesOrder.entity_id is NULL');

        
$customersSubscribersRightJoin Mage::getModel('customer/customer')->getCollection();
        
$customersSubscribersRightJoin->getSelect()
            ->
reset(Zend_Db_Select::COLUMNS)
            ->
columns(array('entity_id''email'))
            ->
joinLeft(array('salesOrder' => $resource->getTableName('sales/order')), 'e.entity_id = salesOrder.customer_id', array(
                
'entity_id'    => 'salesOrder.entity_id AS order_id'
            
))    
            ->
joinRight(array('newsletterSubscriber' => $resource->getTableName('newsletter/subscriber')), 'e.entity_id = newsletterSubscriber.customer_id', array(
                
'subscriber_id'        => 'subscriber_id',
                
'subscriber_email'    => 'subscriber_email',
                
'customer_id'        => 'customer_id'
            
))
            ->
reset(Zend_Db_Select::WHERE)
            ->
where('salesOrder.entity_id is NULL');

So how can I make an UNION of those 2 collections, or made it in a single call with only the first collection ?

Thank you in advance

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