Creating Virtual columns in magento2 by using nested queries

In this blog we will be checking how to implement nested queries in Magento2 which then can be used in admin Ui Grid for applying filters.

For creating the virtual columns, you can also use render or class attributes in the Ui component but you will get errors while implementing filters as that coulmn will not be actually present in the data source!

The other approach is implementing the nested query to actually create a virtual column in the result set so that filters can work on it.

For example, let us consider a situation in which we will be creating a dataset from table quote of Magento2 and creating virtual column customer_full_name which will be a combination of the columns customer_firstname and customer_lastname.

First we will be creating a virtual column to the data set as:

$query = $modelQuote->getCollection()->getSelect()
->columns("CONCAT(customer_firstname, customer_lastname) as customer_fullname");

on dumping the above mentioned collection query, it will give us the following query:

SELECT `main_table`.*,
CONCAT(customer_firstname, customer_lastname) AS `customer_fullname`
FROM `quote` AS `main_table`

Till now a virtual column customer_fullname has been added to the collection but as this is just a virtual column, you can not use a WHERE clause at this column.

For example, if you try the following query with a WHERE clause:

SELECT `main_table`.*,
CONCAT(customer_firstname, customer_lastname) AS `customer_fullname`
FROM `quote` AS `main_table`
WHERE `customer_fullname` LIKE "%tset%"

it will throw the error as: Error in query (1054): Unknown column ‘customer_fullname‘ in ‘where clause‘.

Now, to make this column permanent, we need to create a join of the query(with virtual column) with the original query. In mysql, the query will be:

SELECT `main_table`.*, `t`.* FROM `quote` AS `main_table`
INNER JOIN ( SELECT `main_table`.`entity_id`,
CONCAT(customer_firstname, customer_lastname) AS `customer_fullname`
FROM `quote` AS `main_table` ) AS `t`
WHERE (t.entity_id=main_table.entity_id)

Now for implementing the same query in Magento2, check the following:

/**
 * \Magento\Quote\Model\Quote $this->_modelQuote
*/
$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");
$finalQuery;
//$finalQuery is the final ready to use result set

You can similarly use any operation on the virtual column created