0

I making report for my Magento 2 site. My code as below and the result of orderCollection as image.

$sale_order_item_table = $this->_resource->getTableName('sales_order_item');

    $catalog_category_product_table = $this->_resource->getTableName('catalog_category_product');

    $catalog_category_entity_text_table = $this->_resource->getTableName('catalog_category_entity_text');

    $catalog_category_entity_varchar_table = $this->_resource->getTableName('catalog_category_entity_varchar');

    $orderCollection = $this->_salesOrderCollectionFactory->create()
        ->addAttributeToSelect('entity_id');
        //->addFieldToFilter('status', 'complete');

    $orderCollection->getSelect()
        ->joinLeft(
            ['soi' => $sale_order_item_table],
            "main_table.entity_id = soi.order_id",
            [
                'item_id' => 'soi.item_id',
                'order_id' => 'soi.order_id',
                'product_id' => 'soi.product_id',
                'base_row_total' => 'soi.base_row_total',
                'base_discount_amount' => "soi.base_discount_amount"
            ]
        )
        ->join(
            ['ccp' => $catalog_category_product_table],
            "soi.product_id = ccp.product_id",
            [
                'category_id' => 'ccp.category_id'
            ]
        )
        ->join(
            ['ccet' => $catalog_category_entity_text_table],
            "ccp.category_id = ccet.entity_id",
            [
                'kpt_id' => 'ccet.value'
            ]
        )
        ->join(
            ['ccev' => $catalog_category_entity_varchar_table],
            "ccet.entity_id = ccev.entity_id",
            [
                'category_name' => "ccev.value"
            ]
        )
        ->where("ccev.attribute_id = 45 AND ccev.store_id = 1 AND ccet.value IS NOT NULL")
        ->group("kpt_id");

    $saleOrderData = $orderCollection->getData();

    echo $orderCollection->getSelect();
    die;

The result image: enter image description here

I need count number of item, sum base_row_total, sum base_discount_amount and group by category_id or group by kpt_id (kpt_id is value in catalog_category_entity_text table).

I think to get collection from $orderCollection->getSelect() to do that.

I want expect result: enter image description here

Please help me, How to do that? Thanks & Best regards, BienHV

1 Answer 1

0

I was fixed by this code:

$sale_order_item_table = $this->_resource->getTableName('sales_order_item');

        $catalog_category_product_table = $this->_resource->getTableName('catalog_category_product');

        $catalog_category_entity_text_table = $this->_resource->getTableName('catalog_category_entity_text');

        $catalog_category_entity_varchar_table = $this->_resource->getTableName('catalog_category_entity_varchar');

        $orderCollection = $this->_salesOrderCollectionFactory->create()
            ->addAttributeToSelect('entity_id');
            //->addFieldToFilter('status', 'complete');

        $orderCollection->getSelect()
            ->joinRight(
                ['soi' => $sale_order_item_table],
                "main_table.entity_id = soi.order_id",
                [
                    'total_orders' => 'count(distinct soi.order_id)',
                    'total_items' => 'count(soi.item_id)',
                    'base_row_total' => 'sum(soi.base_row_total)',
                    'base_discount_amount' => "sum(soi.base_discount_amount)",
                    'base_amount_refunded' => "sum(soi.base_amount_refunded)"
                ]
            )
            ->joinLeft(
                ['ccp' => $catalog_category_product_table],
                "soi.product_id = ccp.product_id",
                [
                    'category_id' => 'ccp.category_id'
                ]
            )
            ->join(
                ['ccet' => $catalog_category_entity_text_table],
                "ccp.category_id = ccet.entity_id",
                [
                    'kpt_id' => 'ccet.value'
                ]
            )
            ->join(
                ['ccev' => $catalog_category_entity_varchar_table],
                "ccet.entity_id = ccev.entity_id",
                [
                    'category_name' => "ccev.value"
                ]
            )
            ->where("ccev.attribute_id = 45 AND ccev.store_id = 1 AND ccet.value IS NOT NULL")
            ->group("kpt_id")
            ->order('kpt_id DESC');

        $saleOrderData = $orderCollection->getData();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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