Magento – How to use UNION for 2 collections ? Or merge collections ? (for use in adminhtml grid)

collection;gridsql

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 
    `e`.`entity_id`, `e`.`group_id`, `e`.`email`, `salesOrder`.`entity_id` AS `order_id`, `salesRecurringProfile`.`profile_id`, `newsletterSubscriber`.`subscriber_status`, `newsletterSubscriber`.`subscriber_id`, `newsletterSubscriber`.`subscriber_email`, `newsletterSubscriber`.`customer_id` 
FROM 
    `customer_entity` AS `e`
LEFT JOIN 
    `sales_flat_order` AS `salesOrder` ON e.entity_id = salesOrder.customer_id AND salesOrder.status = 'processing'
LEFT JOIN 
    `sales_recurring_profile` AS `salesRecurringProfile` ON e.entity_id = salesRecurringProfile.customer_id AND salesRecurringProfile.state = 'active'
LEFT JOIN 
    `newsletter_subscriber` AS `newsletterSubscriber` ON e.entity_id = newsletterSubscriber.customer_id WHERE (salesOrder.entity_id IS NULL AND salesRecurringProfile.profile_id IS NULL)

UNION   

SELECT 
    `e`.`entity_id`, `e`.`group_id`, `e`.`email`, `salesOrder`.`entity_id` AS `order_id`, `salesRecurringProfile`.`profile_id`, `newsletterSubscriber`.`subscriber_status`, `newsletterSubscriber`.`subscriber_id`, `newsletterSubscriber`.`subscriber_email`, `newsletterSubscriber`.`customer_id` 
FROM 
    `customer_entity` AS `e`
LEFT JOIN 
    `sales_flat_order` AS `salesOrder` ON e.entity_id = salesOrder.customer_id AND salesOrder.status = 'processing'
LEFT JOIN 
    `sales_recurring_profile` AS `salesRecurringProfile` ON e.entity_id = salesRecurringProfile.customer_id AND salesRecurringProfile.state = 'active'
RIGHT JOIN 
    `newsletter_subscriber` AS `newsletterSubscriber` ON e.entity_id = newsletterSubscriber.customer_id WHERE (salesOrder.entity_id IS NULL AND salesRecurringProfile.profile_id IS NULL)

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

    $customersSubscribersLeftJoin = Mage::getResourceModel('customer/customer_collection');
    $customersSubscribersLeftJoin->getSelect()
        ->reset(Zend_Db_Select::COLUMNS)
        ->columns(array(
            'entity_id', 
            'group_id',
            'email'
        ))
        ->joinLeft(array('salesOrder' => $resource->getTableName('sales/order')), 'e.entity_id = salesOrder.customer_id AND salesOrder.status = \'' . Mage_Sales_Model_Order::STATE_PROCESSING .  '\'', array(
            'entity_id' => 'salesOrder.entity_id AS order_id'
        ))
        ->joinLeft(array('salesRecurringProfile' => $resource->getTableName('sales/recurring_profile')), 'e.entity_id = salesRecurringProfile.customer_id AND salesRecurringProfile.state = \'' . Mage_Sales_Model_Recurring_Profile::STATE_ACTIVE .  '\'', array(
            'profile_id'    => 'profile_id'
        ))
        ->joinLeft(array('newsletterSubscriber' => $resource->getTableName('newsletter/subscriber')), 'e.entity_id = newsletterSubscriber.customer_id', array(
            'subscriber_status' => 'subscriber_status',
            'subscriber_id'     => 'subscriber_id',
            'subscriber_email'  => 'subscriber_email',
            'customer_id'       => 'customer_id'
        ))
        ->reset(Zend_Db_Select::WHERE)
        ->where('salesOrder.entity_id IS NULL AND salesRecurringProfile.profile_id IS NULL');

    $customersSubscribersRightJoin = Mage::getResourceModel('customer/customer_collection');
    $customersSubscribersRightJoin->getSelect()
        ->reset(Zend_Db_Select::COLUMNS)
        ->columns(array(
            'entity_id', 
            'group_id',
            'email'
        ))
        ->joinLeft(array('salesOrder' => $resource->getTableName('sales/order')), 'e.entity_id = salesOrder.customer_id AND salesOrder.status = \'' . Mage_Sales_Model_Order::STATE_PROCESSING .  '\'', array(
            'entity_id' => 'salesOrder.entity_id AS order_id'
        ))
        ->joinLeft(array('salesRecurringProfile' => $resource->getTableName('sales/recurring_profile')), 'e.entity_id = salesRecurringProfile.customer_id AND salesRecurringProfile.state = \'' . Mage_Sales_Model_Recurring_Profile::STATE_ACTIVE .  '\'', array(
            'profile_id'    => 'profile_id'
        ))
        ->joinRight(array('newsletterSubscriber' => $resource->getTableName('newsletter/subscriber')), 'e.entity_id = newsletterSubscriber.customer_id', array(
            'subscriber_status' => 'subscriber_status',
            'subscriber_id'     => 'subscriber_id',
            'subscriber_email'  => 'subscriber_email',
            'customer_id'       => 'customer_id'
        ))
        ->reset(Zend_Db_Select::WHERE)
        ->where('salesOrder.entity_id IS NULL AND salesRecurringProfile.profile_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

EDIT :

By using : 

        $customersSubscribersLeftJoin->getSelect()
        ->union(
            array(
                $customersSubscribersRightJoin->getSelect()
            )
        );

The given query is : 

   SELECT 
    `e`.`entity_id`, `e`.`group_id`, `e`.`email`, `salesOrder`.`entity_id` AS `order_id`, `salesRecurringProfile`.`profile_id`, `newsletterSubscriber`.`subscriber_status`, `newsletterSubscriber`.`subscriber_id`, `newsletterSubscriber`.`subscriber_email`, `newsletterSubscriber`.`customer_id`SELECT `e`.`entity_id`, `e`.`group_id`, `e`.`email`, `salesOrder`.`entity_id` AS `order_id`, `salesRecurringProfile`.`profile_id`, `newsletterSubscriber`.`subscriber_status`, `newsletterSubscriber`.`subscriber_id`, `newsletterSubscriber`.`subscriber_email`, `newsletterSubscriber`.`customer_id` 
FROM 
    `customer_entity` AS `e`
LEFT JOIN 
    `sales_flat_order` AS `salesOrder` ON e.entity_id = salesOrder.customer_id AND salesOrder.status = 'processing'
LEFT JOIN 
    `sales_recurring_profile` AS `salesRecurringProfile` ON e.entity_id = salesRecurringProfile.customer_id AND salesRecurringProfile.state = 'active'
RIGHT JOIN 
    `newsletter_subscriber` AS `newsletterSubscriber` ON e.entity_id = newsletterSubscriber.customer_id 
WHERE 
    (salesOrder.entity_id IS NULL AND salesRecurringProfile.profile_id IS NULL) 
FROM 
    `customer_entity` AS `e`
LEFT JOIN 
    `sales_flat_order` AS `salesOrder` ON e.entity_id = salesOrder.customer_id AND salesOrder.status = 'processing'
LEFT JOIN 
    `sales_recurring_profile` AS `salesRecurringProfile` ON e.entity_id = salesRecurringProfile.customer_id AND salesRecurringProfile.state = 'active'
LEFT JOIN 
    `newsletter_subscriber` AS `newsletterSubscriber` ON e.entity_id = newsletterSubscriber.customer_id 
WHERE 
    (salesOrder.entity_id IS NULL AND salesRecurringProfile.profile_id IS NULL)

Which is clearly not what we want by making the union of the two queries, any ideas ?

Best Answer

Assuming the two collections are correct and all you needed to do was join the two sets, from a purely hypothetical view point you could try:

$customersSubscribersLeftJoin
    ->getSelect()
    ->union(
        array(
            $customersSubscribersLeftJoin->getSelect()
        )
    );

I suspect however that you would have difficulty with the resulting collection. You would probably end up with something like "item with this id already exists", and that's assuming it even works, which I haven't actually tested.

Related Topic