Magento2 Need to filter grid when value is entered in search box, the column of the data is from another table

Question

I have added 4 columns in the grid as displayed in below screenshot.

For displaying for eg. Recipient Email column I added following code in XML & it's renderer PHP file. (Code is added below)

app/code/Company/Module/view/adminhtml/layout/adminhtml_giftcardaccount_block.xml

<block class="Magento\Backend\Block\Widget\Grid\Column">
                    <arguments>
                        <argument name="header" xsi:type="string" translate="true">Recipient Email</argument>
                        <argument name="index" xsi:type="string">recipient_email</argument>
                        <argument name="renderer" xsi:type="string">Company\Module\Block\Adminhtml\Widget\Grid\Column\Renderer\Recipientemail</argument>
                        <argument name="sortable" xsi:type="boolean">false</argument>
                    </arguments>
                </block>

app/code/Company/Module//Block/Adminhtml/Widget/Grid/Column/Renderer/Recipientemail.php

<?php
namespace Company\Module\Block\Adminhtml\Widget\Grid\Column\Renderer;
class Recipientemail extends \Magento\Backend\Block\Widget\Grid\Column\Renderer\AbstractRenderer
{
    protected $_storeManager;

    public function __construct(
        \Magento\Backend\Block\Context $context,
        \Magento\Store\Model\StoreManagerInterface $storeManager,      
        \Magento\Sales\Model\Order\Item $orderItem,
        array $data = []
    ) {
        parent::__construct($context, $data);
        $this->_storeManager = $storeManager;      
        $this->orderitem =  $orderItem;  
    }


    public function render(\Magento\Framework\DataObject $row)
    {
        $code = $row->getCode();
        $result = array();
        $salesorderitem =  $this->orderitem->getCollection()
                          ->addFieldToFilter('product_options', array('like' => '%'.$code.'%'));

        if(!empty($salesorderitem)) 
        {
            foreach($salesorderitem as $orderdata){
                $productOptions = $orderdata->getProductOptions();   
                $recipientemail = $productOptions['giftcard_recipient_email'];
                if(!empty($recipientemail)){
                return $recipientemail;
                }else{
                    return '-';
                }
            }
        }else{
               return '-';
        }


    }

Main table of grid is - magento_giftcardaccount

Recipientemail table is - product_options

{"info_buyRequest":{"uenc":"aHR0cDovLzE5Mi4xNjguMS4xODQvZGcvZHJ1bW1vbmQtZ29sZi1lLWdpZnQtY2FyZC5odG1s","product":"76450","selected_configurable_option":"","related_product":"","giftcard_amount":"5","giftcard_sender_name":"test","giftcard_sender_email":"testmscgoriteeps@gmail.com","giftcard_recipient_name":"test22","giftcard_recipient_email":"arjun.cmarix@gmail.com","giftcard_message":"test test gift","qty":"1"},"giftcard_sender_name":"test","giftcard_sender_email":"testmscgoriteeps@gmail.com","giftcard_recipient_name":"test22","giftcard_recipient_email":"arjun.cmarix@gmail.com","giftcard_message":"test test gift","giftcard_lifetime":null,"giftcard_is_redeemable":0,"giftcard_email_template":null,"giftcard_type":"0","email_sent":0,"giftcard_created_codes":["HQE9EMUNTA6N"]}

My issue is the filter is not working. When I enter email in the search text field of grid it keeps on spinning for some time & grid is loaded as it is without filter.

It would be really appreciable if someone can guide me on how to filter this search text.

Answer 1

This is a tricky one, I got a similar case and found a solution, using the _initSelect in the collection and changing the index in the xml. Just follow the steps and you will be fine!

First you need to add a preference for the collection and overwrite _initSelect():

<preference for="Magento\GiftCardAccount\Model\ResourceModel\Giftcardaccount\Collection" type="Company\Module\Model\ResourceModel\Giftcardaccount\Collection"/>

Inside the collection Company\Module\Model\ResourceModel\Giftcardaccount\Collection extend the select to add the required table with a wildcard in the select:

class Collection extends \Magento\GiftCardAccount\Model\ResourceModel\Giftcardaccount\Collection
{
    protected function _initSelect()
    {
        parent::_initSelect();
        $this->getSelect()->joinLeft(
            ['sales_order_item' => $this->getTable('sales_order_item')],
            'sales_order_item.product_options like CONCAT("%",main_table.code,"%")',
            ['sales_order_item.product_options']
        );

        return $this;
    }
}

For performance I suggest you change your file: app/code/Company/Module//Block/Adminhtml/Widget/Grid/Column/Renderer/Recipientemail.php

class Recipientemail extends \Magento\Backend\Block\Widget\Grid\Column\Renderer\AbstractRenderer
{
    /**
     * @var \Magento\Framework\Serialize\Serializer\Json
     */
    private $serializer;

    /**
     * Recipientemail constructor.
     * @param \Magento\Backend\Block\Context $context
     * @param array $data
     * @param \Magento\Framework\Serialize\Serializer\Json|null $serializer
     */
    public function __construct(
        \Magento\Framework\Serialize\Serializer\Json $serializer,
        \Magento\Backend\Block\Context $context,
        array $data = [])
    {
        parent::__construct($context, $data);
        $this->serializer = $serializer;
    }

    /**
     * @param \Magento\Framework\DataObject $row
     * @return string
     */
    public function render(\Magento\Framework\DataObject $row) {
        if (empty($row->getProductOptions())) {
            return "-";
        }
        $productOptions = $this->serializer->unserialize($row->getProductOptions());
        $recipientemail = $productOptions['giftcard_recipient_email'];
        if(!empty($recipientemail)){
            return $recipientemail;
        }else{
            return '-';
        }
    }
}

Finally you need to change the adminhtml_giftcardaccount_block.xml file, this in order to take the filter:

<block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.logging.archive.grid.columnSet.recipient_email" as="recipient_email">
    <arguments>
        <argument name="header" xsi:type="string" translate="true">Recipient Email</argument>
        <argument name="index" xsi:type="string">sales_order_item.product_options</argument>
        <argument name="renderer" xsi:type="string">WolfSellers\CatalogRule\Block\Adminhtml\Widget\Grid\Column\Renderer\Recipientemail</argument>
        <argument name="sortable" xsi:type="boolean">false</argument>
    </arguments>
</block>

I tested in my local environment, it works!

Answer 2

You can join 2 or more table in _initSelect() function of collection class Test\TestModule\Model\ResourceModel\Gift\Collection. After Joining the table you can use join tables column in your grid.

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

    $this->getSelect()->joinLeft(['secondTable' => $this->getTable('your_table_name')], "secondTable.entity_id = main_table.entity_id ", ['secondTable.value as alies_name']);

    return $this;
}

Answer 3

You can Get Data From Multiple Tables using _initSelect Function in Vendor\Module\Model\ResourceModel\Gift\Collection

 protected function _initSelect()
        {
            parent::_initSelect();
            $secondTable=$this->getTable('product_options');
            $this->getSelect()->joinLeft(' ".$secondTable.",' ".$secondTable.".entity_id = main_table.entity_id ', [' ".$secondTable.".giftcard_recipient_email as Recipent Email]');
        }