Reading list Switch to dark mode

    Creating Virtual columns in magento2 by using nested queries

    Updated 29 February 2024

    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:

    Searching for an experienced
    Magento Company ?
    Find out More
    $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()
                        ->columns("CONCAT(customer_firstname, customer_lastname) as customer_fullname");
    $finalQuery = $queryCopy->getSelect()
    //$finalQuery is the final ready to use result set

    You can similarly use any operation on the virtual column created

    . . .

    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