mysql - Magento 联合 SQL

Question

我正在使用 Magento，但在对我的集合使用 sql 命令 UNION 时遇到了问题。我有两个收藏:

       $collection = Mage::getModel('sales/order')->getCollection();
    $collection->getSelect()->joinRight(
        array('sfi' => 'sales_flat_invoice'),
        'main_table.entity_id = sfi.order_id AND
        NOW()<= DATE_ADD(main_table.created_at, INTERVAL 3 MONTH)',
        array()
    );
    $collection->getselect()->joinLeft(
        array('ztr'=>'zdg_tv_report'),
        'ztr.type=\'TV\' AND ztr.number=main_table.increment_id',
        array('*')
    );
    $collection->addFieldToSelect(new Zend_Db_Expr('main_table.increment_id'));

和

$collection2 = Mage::getModel('sales/order')->getCollection();
    $collection2->getSelect()->joinRight(
        array('sfc'=>'sales_flat_creditmemo'),
        'main_table.entity_id= sfc.order_id AND
        NOW()<= DATE_ADD(main_table.created_at, INTERVAL 3 MONTH)',
        array()
        );
        $collection2->getSelect()->joinLeft(
            array('ztr'=>'zdg_tv_report'),
            'ztr.type=\'Reverse_TV\' AND ztr.number=main_table.increment_id',
            array('*')
        );
    $collection2->addFieldToSelect(new Zend_Db_Expr('main_table.increment_id'));

我试过

$collection->getSelect()->union(array($collection2->getSelect()));

但是我得到的 SQL select 似乎完全搞砸了:

SELECT main_table.increment_id, `ztr`.*SELECT main_table.increment_id, `ztr`.* FROM `sales_flat_order` AS `main_table` RIGHT JOIN `sales_flat_creditmemo` AS `sfc` ON main_table.entity_id= sfc.order_id AND NOW()<= DATE_ADD(main_table.created_at, INTERVAL 3 MONTH) LEFT JOIN `zdg_tv_report` AS `ztr` ON ztr.type='Reverse_TV' AND ztr.number=main_table.increment_id FROM `sales_flat_order` AS `main_table` RIGHT JOIN `sales_flat_invoice` AS `sfi` ON main_table.entity_id = sfi.order_id AND NOW()<= DATE_ADD(main_table.created_at, INTERVAL 3 MONTH) LEFT JOIN `zdg_tv_report` AS `ztr` ON ztr.type='TV' AND ztr.number=main_table.increment_idSELECT main_table.increment_id, `ztr`.*SELECT main_table.increment_id, `ztr`.* FROM `sales_flat_order` AS `main_table` RIGHT JOIN `sales_flat_creditmemo` AS `sfc` ON main_table.entity_id= sfc.order_id AND NOW()<= DATE_ADD(main_table.created_at, INTERVAL 3 MONTH) LEFT JOIN `zdg_tv_report` AS `ztr` ON ztr.type='Reverse_TV' AND ztr.number=main_table.increment_id FROM `sales_flat_order` AS `main_table` RIGHT JOIN `sales_flat_invoice` AS `sfi` ON main_table.entity_id = sfi.order_id AND NOW()<= DATE_ADD(main_table.created_at, INTERVAL 3 MONTH) LEFT JOIN `zdg_tv_report` AS `ztr` ON ztr.type='TV' AND ztr.number=main_table.increment_id 

我也试过用

    $unionSelect = new Varien_Db_Select();
    $unionSelect->union(array($collection->getSelect(), $collection2->getSelect()));

但是我不知道如何将这样的选择放入集合中。有人可以帮助我吗？

Answer 1

您可以尝试使用以下变体在 Magento 中执行 UNION 语句:

 $collection = Mage::getModel('sales/order')->getCollection();

 $select1 = clone $collection->getSelect();
 $select1->joinRight...

 $select2 = clone $collection->getSelect();
 $select2->joinRight...

 $collection->getSelect()->reset();
 $collection->getSelect()->union(array($select1, $select2));