Reading list Switch to dark mode

    Use Nested Queries in Magento2 Admin UiGrid

    Updated 29 February 2024

    In this blog we, will be learning how to use nested queries in Magento2 Ui Admin Grid for creating the desired result set.

    You can check the basics of creating the Ui admin grid at: https://webkul.com/blog/how-to-create-a-grid-using-ui-component/
    and the basics of creating virtual columns using nested queries in Magento 2 at: https://webkul.com/blog/creating-virtual-columns-in-magento2-by-using-nested-queries/

    The advantages of using virtual columns in your data set is that you can actually use the default filters on those columns that are created virtually using nested queries.

    To implement the same, we need to go to the file: Webkul\Hello\Model\ResourceModel\Employee\Grid\Collection and implement the method: _renderFiltersBefore()

    This method is called before rendering the filters and the same is called before applying the filters on the UiGrid

    Searching for an experienced
    Magento Company ?
    Find out More

    The nested query(as described in the previous mentioned) blog will be implemented in the Collection file as:

    <?php
    /**
     * Webkul Software.
     *
     * @category  Webkul
     * @package   Webkul_Hello
     * @author    Webkul
     * @copyright Copyright (c) Webkul Software Private Limited (https://webkul.com)
     * @license   https://store.webkul.com/license.html
     */
    
    namespace Webkul\Hello\Model\ResourceModel\Employee\Grid;
    
    use Magento\Framework\Api\Search\SearchResultInterface;
    use Webkul\Hello\Model\ResourceModel\Employee\Collection as QuoteCollection;
    
    class Collection extends EmployeeCollection implements SearchResultInterface
    {
        public function __construct(
            \Magento\Quote\Model\Quote $modelQuote,
            \Magento\Framework\Data\Collection\EntityFactoryInterface $entityFactory,
            \Psr\Log\LoggerInterface $logger,
            \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy,
            \Magento\Framework\Event\ManagerInterface $eventManager,
            \Magento\Store\Model\StoreManagerInterface $storeManager,
            $mainTable,
            $eventPrefix,
            $eventObject,
            $resourceModel,
            $model = 'Magento\Framework\View\Element\UiComponent\DataProvider\Document',
            $connection = null,
            \Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource = null
        ) {
            parent::__construct(
                $entityFactory,
                $logger,
                $fetchStrategy,
                $eventManager,
                $storeManager,
                $connection,
                $resource
            );
            $this->_modelQuote = $modelQuote;
            $this->_eventPrefix = $eventPrefix;
            $this->_eventObject = $eventObject;
            $this->_init($model, $resourceModel);
            $this->setMainTable($mainTable);
        }
    
        /**
         * use the nested query here to change the result data set
         **/
        protected function _renderFiltersBefore()
        {
            $modelQuote = $this->_modelQuote;
            $queryCopy = clone($modelQuote->getCollection());
            //cloning the object to use later
            $query = $modelQuote->getCollection()
                                ->getSelect()
                                ->reset('columns')
                                ->columns("main_table.entity_id")
                                ->columns("CONCAT(customer_firstname, customer_lastname) as customer_fullname");
            $query->__toString();
            $finalQuery = $queryCopy->getSelect()
                                    ->from($query)
                                    ->where("t.entity_id=main_table.entity_id");
            parent::_renderFiltersBefore();
        }
    }

    Now, the column customer_fullname can be used in the UiGrid and the filters will be working on the same also, automatically as the virtual column now actually exists in the result set

    If you have any doubts or queries, please put in the comments section.

    . . .

    Leave a Comment

    Your email address will not be published. Required fields are marked*


    Be the first to comment.

    Back to Top

    Message Sent!

    If you have more details or questions, you can reply to the received confirmation email.

    Back to Home