mysql - 将 SQL 选择查询转换为 Magento 集合

Question

我有一个复杂的 SQL 选择查询，它实际上返回针对指定类别的销售报告。我想加载数据作为集合来显示，但我无法将查询转换为 Magento 的等效 getCollection 语句。我的查询如下。提前致谢

        select
        sfo.increment_id as 'orderid',
        sfoi.sku,
        sfoi.qty_ordered as 'qty',
        IFNULL(sfoi.price_incl_tax-sfoi.discount_amount, 0) as 'productprice',
        sfo.shipping_amount,
        cost.value as 'price',
        sfo.status,
        sfo.created_at,
        IF(ccp1.product_id IS NULL, 'no', 'yes') as sale,
        IF(ccp2.product_id IS NULL, 'no', 'yes') as home

        from sales_flat_order as sfo
        join sales_flat_order_item as sfoi on sfo.entity_id = sfoi.order_id
        join catalog_product_entity_decimal as cost on cost.entity_id = sfoi.product_id and cost.attribute_id = 79
        left join catalog_category_product as ccp1 on sfoi.product_id = ccp1.product_id and ccp1.category_id = 8
        left join catalog_category_product as ccp2 on sfoi.product_id = ccp2.product_id and ccp2.category_id = 7
        where sfo.created_at between '2017-01-01' and '2017-01-02'
        and sfo.status in ('processing','complete')

Answer 1

Magento 收集声明

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

$collection->getSelect()->join(
        array('sfoi' => $collection->getTable('sales/order_item')), 'main_table.entity_id=sfoi.order_id',
        array('qty' => 'sfoi.qty_ordered', 'sku')
      )->join(
        array('cost' => 'catalog_product_entity_decimal'), 'cost.entity_id=sfoi.product_id and cost.attribute_id=79',
        array('price' => 'cost.value')
      )->joinLeft(
            array('ccp1' => 'catalog_category_product'),
            'sfoi.product_id = ccp1.product_id and ccp1.category_id = 8', null
      )->joinLeft(
            array('ccp2' => 'catalog_category_product'),
            'sfoi.product_id = ccp2.product_id and ccp2.category_id = 7', null
);


$collection->addFieldToSelect('increment_id', 'orderid')
       ->addFieldToSelect('shipping_amount')
       ->addFieldToSelect('status')
       ->addFieldToSelect('created_at')
       ->addExpressionFieldToSelect(
        'productprice',
        'IFNULL((sfoi.price_incl_tax - {{discount_amount}}), "0")',
        array(
            'discount_amount' => 'sfoi.discount_amount'))
       ->addExpressionFieldToSelect(
        'sale',
        'IF({{product_id}} IS NULL, "no", "yes")',
        array(
            'product_id' => 'cpp1.product_id'))
       ->addExpressionFieldToSelect(
        'home',
        'IF({{product_id}} IS NULL, "no", "yes")',
        array(
            'product_id' => 'ccp2.product_id'))
       ->addAttributeToFilter('sfo.created_at', array(
        'from' => '2017-01-01',
        'to' => '2017-01-02',
        'date' => true,
        ))
       ->addFieldToFilter('status', array('in' => array('processing','complete')));

...生成的sql查询

SELECT `main_table`.`increment_id`                                 AS `orderid`,
       `main_table`.`shipping_amount`,
       `main_table`.`status`,
       `main_table`.`created_at`,
       `sfoi`.`qty_ordered`                                        AS `qty`,
       `sfoi`.`sku`,
       `cost`.`value`                                              AS `price`,
       Ifnull(( sfoi.price_incl_tax - sfoi.discount_amount ), "0") AS
       `productprice`,
       IF(cpp1.product_id IS NULL, "no", "yes")                    AS `sale`,
       IF(ccp2.product_id IS NULL, "no", "yes")                    AS `home`
FROM   `sales_flat_order` AS `main_table`
       INNER JOIN `sales_flat_order_item` AS `sfoi`
               ON main_table.entity_id = sfoi.order_id
       INNER JOIN `catalog_product_entity_decimal` AS `cost`
               ON cost.entity_id = sfoi.product_id
                  AND cost.attribute_id = 79
       LEFT JOIN `catalog_category_product` AS `ccp1`
              ON sfoi.product_id = ccp1.product_id
                 AND ccp1.category_id = 8
       LEFT JOIN `catalog_category_product` AS `ccp2`
              ON sfoi.product_id = ccp2.product_id
                 AND ccp2.category_id = 7
WHERE  ( `sfo`.`created_at` >= '2017-01-01 00:00:00'
         AND `sfo`.`created_at` <= '2017-01-02 00:00:00' )
       AND ( `status` IN( 'processing', 'complete' ) )