1

I am facing issue with a custom report with multiple table join.

I have created a custom collection into _initSelect function.

While I am print this query and run manually on a database server its run perfectly and I get data what I want. But it in the normal case it's not working. Also without joining its working fine and I got data in grid.xml

protected function _initSelect()
{
    parent::_initSelect();

    $collection = $this->getSelect()->reset(\Zend_Db_Select::COLUMNS)
                 ->columns(['product_id','name']);

    $this->getSelect()->join(
        ['secondTable' => $this->getTable('sales_order')], 
        'main_table.order_id = secondTable.entity_id', 
        ['status','increment_id','customer_email','DISTINCT(entity_id)'] 
       );
}

Here the Main table is sales_order_item.

Without Join Output of Report

enter image description here

Adding join in the collection

enter image description here

here is code of grid.xml

<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
<update handle="reports_report_grid"/>
<body>
    <referenceBlock name="adminhtml.report.grid">
        <arguments>
            <argument name="id" xsi:type="string">gridTestReport</argument>
            <argument name="dataSource" xsi:type="object">Test\Testmodule\Model\ResourceModel\Testmodule\Collection\Initial</argument>
        </arguments>
    </referenceBlock>
    <referenceBlock name="adminhtml.report.grid.export">
        <arguments>
            <argument name="exportTypes" xsi:type="array">
                <item name="csv" xsi:type="array">
                    <item name="urlPath" xsi:type="string">*/*/exportCsv</item>
                </item>
                <item name="excel" xsi:type="array">
                    <item name="urlPath" xsi:type="string">*/*/exportExcel</item>
                </item>
            </argument>
        </arguments>
    </referenceBlock>
    <referenceBlock name="adminhtml.report.grid.columnSet">
        <arguments>
            <argument name="count_totals" xsi:type="string">1</argument>
        </arguments>
        <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.report.grid.columnSet.product_id" as="product_id">
            <arguments>
                <argument name="header" xsi:type="string" translate="true">Product Id</argument>
                <argument name="sortable" xsi:type="string">0</argument>
                <argument name="type" xsi:type="string">text</argument>
                <argument name="index" xsi:type="string">product_id</argument>
                <argument name="id" xsi:type="string">product_id</argument>
                <argument name="column_css_class" xsi:type="string">col-name</argument>
                <argument name="header_css_class" xsi:type="string">col-name</argument>
            </arguments>
        </block>
    </referenceBlock>
</body>

1

Instead of joining at _initSelect in the class Test\Testmodule\Model\ResourceModel\Testmodule\Collection\Initial , you can try to join fields in resource collection class.

Assume your second table is "sales_order" and you wish to fetch "status","increment_id" and "customer_email" from sales_order.

Please try to update your files as described below.

File : Test\Testmodule\Model\ResourceModel\Testmodule\Collection.php

class Test\Testmodule\Model\ResourceModel\Testmodule\Collection extends ........

    protected function _joinFields($fromDate = '', $toDate = '')
        {

            $this->getSelect();
            $this->addAttributeToFilter(
                'from_date_field',
                ['from' => $fromDate, 'to' => $toDate, 'datetime' => true]
            )->addAttributeToSelect('*');        

            $this->addOrderInfo();                       
            return $this;
        }   



     /**
     * Add ordered Info
     *     
     * @return $this
     */
    public function addOrderInfo()
    {
        $connection = $this->getConnection();
        $joinConditions = 'order_items.order_id = sales_order.entity_id';
        $this->getSelect()->join(
            ['sales_order' => $this->getTable('sales_order')],
            $joinConditions,
            []
        )->columns([            
            "sales_order.increment_id as ordincrementid",
            "sales_order.status as orderstatus",            
            "sales_order.customer_email as customeremail",
            "sales_order.customer_firstname as firstname",
            "sales_order.customer_lastname as lastname",
            "sales_order.total_paid as totalpaid",
            "sales_order.total_due as totaldue",
            "sales_order.grand_total as grandtotal"]);        
        return $this;
    }  



}

File : Test\Testmodule\Model\ResourceModel\Testmodule\Collection\Initial.php

class Initial extends \Magento\Reports\Model\ResourceModel\Report\Collection
{
    /**
     * Report sub-collection class name
     * @var string
     */
    protected $_reportCollection = \Test\Testmodule\Model\ResourceModel\Testmodule\Collection::class;
}

File : grid.xml

<referenceBlock name="adminhtml.report.grid.columnSet">            
--------------------
    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.report.grid.columnSet.orderstatus" as="orderstatus">
        <arguments>
            <argument name="header" xsi:type="string" translate="true">Order Status</argument>
            <argument name="sortable" xsi:type="string">0</argument>
            <argument name="type" xsi:type="string">text</argument>
            <argument name="index" xsi:type="string">orderstatus</argument>
            <argument name="id" xsi:type="string">orderstatus</argument>
            <argument name="column_css_class" xsi:type="string">col-name</argument>
            <argument name="header_css_class" xsi:type="string">col-name</argument>
         </arguments>
    </block>     
   <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.report.grid.columnSet.ordincrementid" as="ordincrementid">
            <arguments>
                <argument name="header" xsi:type="string" translate="true">Order Increment  Id</argument>
                <argument name="sortable" xsi:type="string">0</argument>
                <argument name="type" xsi:type="string">text</argument>
                <argument name="index" xsi:type="string">ordincrementid</argument>
                <argument name="id" xsi:type="string">ordincrementid</argument>
                <argument name="column_css_class" xsi:type="string">col-name</argument>
                <argument name="header_css_class" xsi:type="string">col-name</argument>
            </arguments>
    </block>
    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.report.grid.columnSet.customeremail" as="customeremail">
            <arguments>
                <argument name="header" xsi:type="string" translate="true">Email</argument>
                <argument name="sortable" xsi:type="string">0</argument>
                <argument name="type" xsi:type="string">text</argument>
                <argument name="index" xsi:type="string">customeremail</argument>
                <argument name="id" xsi:type="string">customeremail</argument>
                <argument name="column_css_class" xsi:type="string">col-name</argument>
                <argument name="header_css_class" xsi:type="string">col-name</argument>
            </arguments>
    </block>

--------------------
</referenceBlock>
| improve this answer | |
  • I have updated code as per your suggestion still not working. – Ketan Panchal Dec 13 '18 at 7:22
  • Could you please confirm the tables names (main table and second joined table). Do you extend Magento's any default report? – Pritam Info 24 Dec 13 '18 at 7:30
  • I have sales_order_item as main table and second table as sales_order. I have created 3-4 custom report but i am facing issue with join collection – Ketan Panchal Dec 13 '18 at 7:35
  • here is a screenshot of both function: prnt.sc/luca0n and grid.xml code: prnt.sc/lucajo. – Ketan Panchal Dec 13 '18 at 7:45
  • please check my updated answer. I successfully append order table information with sales _order_item using the code for Magento Report ->Products -> Ordered. (ref YOURMAGENTOROOT/vendor/magento/module-reports/Model/ResourceModel/Product/Sold/Collection.php ) – Pritam Info 24 Dec 13 '18 at 7:55

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.