Use Nested Queries in Magento2 Admin UiGrid

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

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.