7

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 ?

3

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.

  • Hi ! Thank you for taking time to answer ! I test this way to do the union, the problem is that the final query made by this, do not add "UNION". It deletes the select from the second query and concatenate the "FROM" and the joins ! Really weird – Thomas Seres Dec 13 '13 at 10:31
  • For more informations, I just edit my post, and put the query resulting of the union – Thomas Seres Dec 13 '13 at 10:40
  • 1
    It seems that Zend_Db_Select::union doesn't work how you might expect, I think to get the correct query you'd need an instance of Zend_Db_Select and then pass getSelect() from both collections as an array. I'm not sure how this would work in regards to Magento however, since you'd seemingly need to set the returned select object as the select object on the collection which to my knowledge isn't possible. – Peter O'Callaghan Dec 13 '13 at 14:37
3

You need to clone and reset your select.

$customersLeftSelect = clone $customersSubscribersLeftJoin->getSelect();
$customersSubscribersLeftJoin
    ->getSelect()
    ->reset()
    ->union(
        array(
            $customersLeftSelect,
            $customersSubscribersRightJoin
        )
    );

This is how it worked for me.

  • Thanks, it's working for me. I give you some example, please look at this $userCollection = clone $collection->getSelect(); UNION: $myresult = $userCollection->reset()->union(array($adminCollection->getSelect()->__toString(),$collection->getSelect()->__toString() )); UNION ALL: $myresult = $userCollection->reset()->union(array($adminCollection->getSelect()->__toString(),$collection->getSelect()->__toString() ), \Magento\Framework\DB\Select::SQL_UNION_ALL); – Renga May 1 '18 at 13:58
1
$selectFoo = Mage::getResourceModel('model/foo')->getSelect();
$selectBar= Mage::getResourceModel('model/bar')->getSelect();

$mergedFooBar = $selectFoo->union(array($selectBar));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.