2

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

enter image description here

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":"[email protected]","giftcard_recipient_name":"test22","giftcard_recipient_email":"[email protected]","giftcard_message":"test test gift","qty":"1"},"giftcard_sender_name":"test","giftcard_sender_email":"[email protected]","giftcard_recipient_name":"test22","giftcard_recipient_email":"[email protected]","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.

5
  • how you make your grid using UI or block?
    – ASQ
    Jul 15, 2019 at 6:53
  • Using block & render
    – Arjun
    Jul 15, 2019 at 6:53
  • can you please share the code of your block in which you call the collection for grid
    – ASQ
    Jul 15, 2019 at 6:55
  • @abdus added code in my question
    – Arjun
    Jul 15, 2019 at 7:19
  • You can view my friend's answer and take reference from there and apply as per your requirement.. magento.stackexchange.com/questions/149445/… Feel free to comment if you face any issue.
    – mighty_hk
    Jul 24, 2019 at 8:42

3 Answers 3

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!

3
  • Can you let me know what should I do for sorting? Also, as I am having too much data so it just keeps on loading.
    – Arjun
    Aug 7, 2019 at 13:57
  • Just FYI, it is filtering entire 'sales_order_item.product_options' & not only 'giftcard_recipient_email'.
    – Arjun
    Aug 8, 2019 at 11:46
  • @Arjun Yes it will filter all the product_options, the thing is in the collection filter must be done by sql, and since mysql does not unserialize, this is the best solution so far, otherwise it will be a really pain in the ass to load everything, on the other hand, sortable must be done with some other tricks. My suggestion, do not sort it, search instead
    – Rus0
    Aug 29, 2019 at 19:15
0

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;
}
6
  • Will this work on 'product_options' column in sales_order_item'
    – Arjun
    Jul 15, 2019 at 7:23
  • yes, you can join any table
    – Hiren
    Jul 15, 2019 at 7:25
  • I have added product_options data in my question. I need to get 'giftcard_recipient_email' data
    – Arjun
    Jul 15, 2019 at 7:32
  • I want to join product_options table value with other table value
    – Arjun
    Jul 15, 2019 at 8:42
  • please update your question with all related code and clear requirement
    – Hiren
    Jul 15, 2019 at 8:56
0

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]');
        }

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.