1

I am trying to display a report which

GROUP BY : SKU and Subsidiary.

So instead of displaying each RMA in different rows,

I am trying to display Filtered Report, Group by SKU and 2nd group by Subsidiary (CA / DE).

I have used two tables for it. (i.e mst_rma_rma & mst_rma_item)

enter image description here

enter image description here

Current grid : enter image description here

Desired grid : enter image description here

I have customized Mirasvit Rma module for this.

Here are the modules - https://easyupload.io/8e4jbp.

Is there any way I can display the data in a grid-like the desired grid?

Any thoughts appreciated. Kindly let me know if any further details are required.

5
  • any thoughts on this ?
    – Devhs
    Jun 13 at 12:39
  • for solution of this problem we have to use left join query to combine two tables and specify product sku in group by in query.
    – Deep Shah
    Jun 16 at 6:55
  • Hello @DeepShah, would be great if you can add a query as answer. Jun 17 at 13:24
  • Hello @HamendraSunthwal I am going to share link with you it will help you to create join query : mageplaza.com/devdocs/…
    – Deep Shah
    Jun 17 at 14:00
  • this is the actual query in which they want to get data from sales order and payment table: $this->sales_order_table = "main_table"; $this->sales_order_payment_table = $this->getTable("sales_order_payment"); $this->getSelect() ->join(array('payment' =>$this->sales_order_payment_table), $this->sales_order_table . '.entity_id= payment.parent_id', array('payment_method' => 'payment.method', 'order_id' => $this->sales_order_table.'.entity_id' ) ); $this->getSelect()->where("payment_method=".$payment_method);
    – Deep Shah
    Jun 20 at 9:08

2 Answers 2

0
public function getJoinData(){
   $collection = $this->_helloModelFactory->create()->getCollection();
   $second_table_name = $this->_resource->getTableName('second_table_name'); 
   $collection->getSelect()->joinLeft(array('second' => $second_table_name),
                                               'main_table.id = second.customer_id');
   echo $collection->getSelect()->__toString(); 
   exit();
}
0

First of all you should not to use subselect to eav_attribute and hardcode attribute table. For any manipulations with EAV attribute use \Magento\Eav\Model\Config instance.

Second, you should be careful with link field between entity and their attributes. You should use \Magento\Framework\EntityManager\MetadataPool and \Magento\Framework\EntityManager\EntityMetadataInterface for link them.

Example for correct way of join product attribute

app/code/Acme/StackExchange/Model/ResourceModel/Example/Collection.php

<?php
declare(strict_types=1);

namespace Acme\StackExchange\Model\ResourceModel\Example;

use Magento\Catalog\Api\Data\ProductInterface;
use Magento\Catalog\Model\Product;
use Magento\Eav\Model\Config as EavConfig;
use Magento\Framework\Data\Collection\Db\FetchStrategyInterface;
use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\EntityManager\EntityMetadataInterface;
use Magento\Framework\EntityManager\MetadataPool;
use Magento\Framework\Event\ManagerInterface;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use Psr\Log\LoggerInterface;

class Collection extends AbstractCollection
{
    /**
     * @var EavConfig
     */
    protected $eavConfig;

    /**
     * @var EntityMetadataInterface
     */
    protected $metadata;

    public function __construct(
        EntityFactoryInterface $entityFactory,
        LoggerInterface $logger,
        FetchStrategyInterface $fetchStrategy,
        ManagerInterface $eventManager,
        EavConfig $eavConfig,
        MetadataPool $metadataPool,
        AdapterInterface $connection = null,
        AbstractDb $resource = null
    ) {
        $this->eavConfig = $eavConfig;
        $this->metadata  = $metadataPool->getMetadata(ProductInterface::class);

        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $connection, $resource);
    }

    /**
     * @inheritDoc
     */
    protected function _initSelect()
    {
        parent::_initSelect();

        $attribute  = $this->eavConfig->getAttribute(Product::ENTITY, 'manufacturer');
        $tableAlias = sprintf('t_%s', $attribute->getAttributeCode());
        $linkField  = $this->metadata->getLinkField();
        $fieldAlias = $attribute->getAttributeCode();

        // e is alias for catalog_product_entity
        $this->getSelect()->joinInner(
            [$tableAlias => $attribute->getBackendTable()],
            sprintf('e.%1$s = %2$s.%1$s AND %2$s.attribute_id = %3$d AND %2$s.store_id = 0',
                $linkField, $tableAlias, $attribute->getId()),
            [$fieldAlias => 'value']
        );

        return $this;
    }
}

P.S. Use catalog_product_entity as main table and join result (attributes) to. Bad idea to use attribute table and use left join for parent table

[UPDATE]

I keep original answer to original question, maybe it will be helpful for someone

I see 2 ways to implement desired grid in magento. I don't have a time to implement solution based on 3rd party extension, but i fast review how it built and how it works.

You cannot reuse exists grid with exists data provider because it's based on another one logic to load data.

1. Using Listing Component

Create Data Provider and register it

First of all you need to have data provide which implement \Magento\Framework\Api\SearchResultsInterface.

You can create DB collection based on RMA Item Collection and use RMA Item Resource. In following example used some custom resource model Acme\StackExchange\Model\ResourceModel\Report which describe item table and connection.

<?php
declare(strict_types=1);

namespace Acme\StackExchange\Model\ResourceModel\Report;

use Acme\StackExchange\Model\ResourceModel\Report as ReportResource;
use Magento\Framework\Api\Search\AggregationInterface;
use Magento\Framework\Api\SearchCriteriaInterface;
use Magento\Framework\Api\SearchResultsInterface;
use Magento\Framework\DataObject;
use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use Magento\Framework\View\Element\UiComponent\DataProvider\Document;

class Collection extends AbstractCollection implements SearchResultsInterface
{
    /**
     * @var AggregationInterface
     */
    protected AggregationInterface $aggregations;

    /**
     * @inheritDoc
     */
    protected function _construct()
    {
        parent::_construct();

        $this->_init(Document::class, ReportResource::class);
    }

    /**
     * @inheritDoc
     */
    protected function _initSelect()
    {
        parent::_initSelect();

        // ideally to reset extra data and keep only needed like
        $this->getSelect()->reset(\Zend_Db_Select::COLUMNS);
        $this->getSelect()->columns([
            'product_sku',
        ], 'main_table');

        // join your rma table to rma_item as main_table and load related data
        $this->getSelect()->join(
            ['rma' => $this->getTable('rma_entity')],
            'main_table.rma_id = rma.rma_id',
            'subsidiary'
        )
        ->group('main_table.product_sku, rma.subsidiary');
        // don't select your extra table fields here

        return $this;
    }

    /**
     * @inheritDoc
     */
    protected function _getItemId(DataObject $item)
    {
        return implode('-', [
            $item->getData('product_sku'),
            $item->getData('subsidiary'),
        ]);
    }

    /**
     * @return AggregationInterface
     */
    public function getAggregations(): AggregationInterface
    {
        return $this->aggregations;
    }

    /**
     * @param AggregationInterface $aggregations
     * @return $this
     */
    public function setAggregations(AggregationInterface $aggregations)
    {
        $this->aggregations = $aggregations;

        return $this;
    }

    /**
     * @inheritDoc
     */
    public function setItems(array $items)
    {
        return $this;
    }

    /**
     * @inheritDoc
     */
    public function getSearchCriteria()
    {
        return null;
    }

    /**
     * @inheritDoc
     */
    public function setSearchCriteria(SearchCriteriaInterface $searchCriteria)
    {
        return $this;
    }

    /**
     * @inheritDoc
     */
    public function getTotalCount(): int
    {
        return (int)$this->getSize();
    }

    /**
     * @inheritDoc
     */
    public function setTotalCount($totalCount)
    {
        return $this;
    }
}

Alternative is create collection without interface implementation and create virtual collection for data provider

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <virtualType name="Acme\StackExchange\Model\ResourceModel\Report\Grid\Collection"
                 type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
        <arguments>
            <argument name="mainTable" xsi:type="string">rma_item_table</argument>
            <argument name="resourceModel" xsi:type="string">Acme\StackExchange\Model\ResourceModel\Report\Collection</argument>
        </arguments>
    </virtualType>
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="acme_rma_report_listing_data_source" xsi:type="string">Acme\StackExchange\Model\ResourceModel\Report\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
</config>

Listing and UI Column

Next step is create UI listing for your grid and specify your extra data as custom column

<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="listing_columns">
        <column name="extra"  class="Acme\StackExchange\Ui\Component\Listing\Column\Extra">
            <settings>
                <label translate="true">Extra</label>
                <bodyTmpl>ui/grid/cells/html</bodyTmpl>
                <sortable>false</sortable>
            </settings>
        </column>
    </columns>
</listing>

Example of Listing Column Component for load data

<?php
declare(strict_types=1);

namespace Acme\StackExchange\Ui\Component\Listing\Column;

use Magento\Ui\Component\Listing\Columns;

class Extra extends Columns
{
    /**
     * @inheritDoc
     */
    public function prepareDataSource(array $dataSource): array
    {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as &$item) {
                // you need to get sku and subsidiary from item
                // receive list of related items
                // you can use repository or collection for get data by sku and subsidiary
                // you need to generate HTML fragment of table to show
                // set result to related field

                $item[$this->getName()] = 'your result HTML here';
            }
        }

        return $dataSource;
    }
}

2. Using old style Magento Grid (used in report)

General idea is prepare special collection which populate data in specific format

You need to create collection which loads the same data like in first example, but you don't need to implements Magento\Framework\Api\SearchResultsInterface and use Document model as item. You need to use \Magento\Reports\Model\Item or implement some of methods. You need to implement after load logic in your collection for load extra data

<?php
declare(strict_types=1);

namespace Acme\StackExchange\Model\ResourceModel\Report;

// ...
class Collection extends AbstractCollection
{
    // ...
    /**
     * @inheritDoc
     */
    protected function _afterLoad()
    {
        // you can collect all available sku in result and load data for those sku
        // or load data for each item individually
        // you need to set children (iterable) with any DataObject instances 
        foreach ($this->_items as $item) {
            $children = [];
            // your logic to load or add children to array
            $item->setChildren($children);
        }

        return parent::_afterLoad();
    }
}

Create layout with grid container, grid using default templates or customize.

NOTE: Your grid block should be extends from \Magento\Backend\Block\Widget\Grid\Extended and you need to set your collection and all fields from both tables.

Also you need to set _groupedColumn property as list of general fields or add them individually with $this->isColumnGrouped('sku', true);

As result you will get grid with rowspans for grouped fields

11
  • Hello, I am already using two tables in same function (i.e _initSelect) how to manage the attribute join within it ?
    – Devhs
    Jun 7 at 5:15
  • Can you please advise ?
    – Devhs
    Jun 7 at 5:18
  • It's a very abstract question. Please update your question with details. For example, you can add your field to map with $this->addFilterToMap('fieldAlias', 'tableAlias.realField'); and use manipulation like $collection->addFieldToFilter('fieldAlias', ['eq' => 'value']); outside of collection, etc. Jun 7 at 10:08
  • Hello Victor, I have updated my question. Please refer it & advise me. Thank you.
    – Devhs
    Jun 12 at 12:47
  • I've updated comment and spent some time for explain and hope concept can help you Jun 13 at 21:36

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.