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":"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.

  • how you make your grid using UI or block? – ASQ Jul 15 at 6:53
  • Using block & render – Arjun Jul 15 at 6:53
  • can you please share the code of your block in which you call the collection for grid – ASQ Jul 15 at 6:55
  • @abdus added code in my question – Arjun Jul 15 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 at 8:42
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!

  • 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 at 13:57
  • Just FYI, it is filtering entire 'sales_order_item.product_options' & not only 'giftcard_recipient_email'. – Arjun Aug 8 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 – Rusit0 Aug 29 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;
}
  • Will this work on 'product_options' column in sales_order_item' – Arjun Jul 15 at 7:23
  • yes, you can join any table – user4536 Jul 15 at 7:25
  • I have added product_options data in my question. I need to get 'giftcard_recipient_email' data – Arjun Jul 15 at 7:32
  • I want to join product_options table value with other table value – Arjun Jul 15 at 8:42
  • please update your question with all related code and clear requirement – user4536 Jul 15 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, privacy policy and cookie policy

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