{"id":148810,"date":"2018-10-23T14:40:47","date_gmt":"2018-10-23T14:40:47","guid":{"rendered":"https:\/\/webkul.com\/blog\/?p=148810"},"modified":"2024-02-29T09:31:23","modified_gmt":"2024-02-29T09:31:23","slug":"creating-virtual-columns-in-magento2-by-using-nested-queries","status":"publish","type":"post","link":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/","title":{"rendered":"Creating Virtual columns in magento2 by using nested queries"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

For creating the virtual columns, you can also use render<\/strong> or class<\/strong> 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!<\/p>\n\n\n\n

The other approach is implementing the nested query<\/strong> to actually create a virtual column in the result set so that filters can work on it.<\/p>\n\n\n\n

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<\/strong> which will be a combination of the columns customer_firstname<\/strong> and customer_lastname<\/strong>.<\/p>\n\n\n\n

First we will be creating a virtual column to the data set as:<\/p>\n\n\n\n

$query = $modelQuote->getCollection()->getSelect()\n->columns("CONCAT(customer_firstname, customer_lastname) as customer_fullname");<\/pre>\n\n\n\n
on dumping the above mentioned collection query, it will give us the following query:<\/span><\/span><\/p>\n\n\n\n
SELECT `main_table`.*,\nCONCAT(customer_firstname, customer_lastname) AS `customer_fullname`\nFROM `quote` AS `main_table`<\/pre>\n\n\n\n
Till now a virtual column customer_fullname<\/strong> has been added to the collection but as this is just a virtual column, you can not use a WHERE clause at this column.<\/p>\n\n\n\n
For example, if you try the following query with a WHERE clause:<\/p>\n\n\n\n
SELECT `main_table`.*,\nCONCAT(customer_firstname, customer_lastname) AS `customer_fullname`\nFROM `quote` AS `main_table`\nWHERE `customer_fullname` LIKE "%tset%"<\/pre>\n\n\n\n
it will throw the error as: Error in query (1054): Unknown column ‘customer_fullname<\/strong>‘ in ‘where clause<\/strong>‘.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
SELECT `main_table`.*, `t`.* FROM `quote` AS `main_table`\nINNER JOIN ( SELECT `main_table`.`entity_id`,\nCONCAT(customer_firstname, customer_lastname) AS `customer_fullname`\nFROM `quote` AS `main_table` ) AS `t`\nWHERE (t.entity_id=main_table.entity_id)<\/pre>\n\n\n\n
Now for implementing the same query in Magento2<\/strong>, check the following:<\/p>\n\n\n\n
\/**\n * \\Magento\\Quote\\Model\\Quote $this->_modelQuote\n*\/\n$modelQuote = $this->_modelQuote;\n$queryCopy = clone($modelQuote->getCollection());\n\/\/cloning the object to use later\n$query = $modelQuote->getCollection()\n                    ->getSelect()\n                    ->reset('columns')\n                    ->columns("main_table.entity_id")\n                    ->columns("CONCAT(customer_firstname, customer_lastname) as customer_fullname");\n$query->__toString();\n$finalQuery = $queryCopy->getSelect()\n                        ->from($query)\n                        ->where("t.entity_id=main_table.entity_id");\n$finalQuery;\n\/\/$finalQuery is the final ready to use result set<\/pre>\n\n\n\n
You can similarly use any operation on the virtual column created<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/a><\/p>\n","protected":false},"author":212,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,302,13],"tags":[2854,2070,90],"class_list":["post-148810","post","type-post","status-publish","format-standard","hentry","category-magento","category-magento2","category-php","tag-custom-query","tag-magento2","tag-mysql"],"yoast_head":"\nCreating Virtual columns in magento2 by using nested queries - Webkul Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating Virtual columns in magento2 by using nested queries - Webkul Blog\" \/>\n<meta property=\"og:description\" content=\"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 [...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/\" \/>\n<meta property=\"og:site_name\" content=\"Webkul Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/webkul\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-10-23T14:40:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-29T09:31:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/08\/webkul-og.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"630\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishal Verma\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@webkul\" \/>\n<meta name=\"twitter:site\" content=\"@webkul\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishal Verma\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/\"},\"author\":{\"name\":\"Vishal Verma\",\"@id\":\"https:\/\/webkul.com\/blog\/#\/schema\/person\/580b43e8509d5fa4f51575bfb8bed418\"},\"headline\":\"Creating Virtual columns in magento2 by using nested queries\",\"datePublished\":\"2018-10-23T14:40:47+00:00\",\"dateModified\":\"2024-02-29T09:31:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/\"},\"wordCount\":273,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/webkul.com\/blog\/#organization\"},\"keywords\":[\"Custom Query\",\"Magento2\",\"mysql\"],\"articleSection\":[\"magento\",\"Magento2\",\"php\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/\",\"url\":\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/\",\"name\":\"Creating Virtual columns in magento2 by using nested queries - Webkul Blog\",\"isPartOf\":{\"@id\":\"https:\/\/webkul.com\/blog\/#website\"},\"datePublished\":\"2018-10-23T14:40:47+00:00\",\"dateModified\":\"2024-02-29T09:31:23+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/webkul.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating Virtual columns in magento2 by using nested queries\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/webkul.com\/blog\/#website\",\"url\":\"https:\/\/webkul.com\/blog\/\",\"name\":\"Webkul Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/webkul.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/webkul.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/webkul.com\/blog\/#organization\",\"name\":\"WebKul Software Private Limited\",\"url\":\"https:\/\/webkul.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/webkul.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/08\/webkul-logo-accent-sq.png\",\"contentUrl\":\"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/08\/webkul-logo-accent-sq.png\",\"width\":380,\"height\":380,\"caption\":\"WebKul Software Private Limited\"},\"image\":{\"@id\":\"https:\/\/webkul.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/webkul\/\",\"https:\/\/x.com\/webkul\",\"https:\/\/www.instagram.com\/webkul\/\",\"https:\/\/www.linkedin.com\/company\/webkul\",\"https:\/\/www.youtube.com\/user\/webkul\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/webkul.com\/blog\/#\/schema\/person\/580b43e8509d5fa4f51575bfb8bed418\",\"name\":\"Vishal Verma\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/webkul.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31e9b57460b4925d92baea86384d39a23da9408a0d216caea290a9ae75e4b310?s=96&d=https%3A%2F%2Fcdnblog.webkul.com%2Fblog%2Fwp-content%2Fuploads%2F2019%2F10%2Fmike.png&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31e9b57460b4925d92baea86384d39a23da9408a0d216caea290a9ae75e4b310?s=96&d=https%3A%2F%2Fcdnblog.webkul.com%2Fblog%2Fwp-content%2Fuploads%2F2019%2F10%2Fmike.png&r=g\",\"caption\":\"Vishal Verma\"},\"url\":\"https:\/\/webkul.com\/blog\/author\/vishalverma-magento279\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Creating Virtual columns in magento2 by using nested queries - Webkul Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/","og_locale":"en_US","og_type":"article","og_title":"Creating Virtual columns in magento2 by using nested queries - Webkul Blog","og_description":"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 [...]","og_url":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/","og_site_name":"Webkul Blog","article_publisher":"https:\/\/www.facebook.com\/webkul\/","article_published_time":"2018-10-23T14:40:47+00:00","article_modified_time":"2024-02-29T09:31:23+00:00","og_image":[{"width":1200,"height":630,"url":"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/08\/webkul-og.png","type":"image\/png"}],"author":"Vishal Verma","twitter_card":"summary_large_image","twitter_creator":"@webkul","twitter_site":"@webkul","twitter_misc":{"Written by":"Vishal Verma","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/#article","isPartOf":{"@id":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/"},"author":{"name":"Vishal Verma","@id":"https:\/\/webkul.com\/blog\/#\/schema\/person\/580b43e8509d5fa4f51575bfb8bed418"},"headline":"Creating Virtual columns in magento2 by using nested queries","datePublished":"2018-10-23T14:40:47+00:00","dateModified":"2024-02-29T09:31:23+00:00","mainEntityOfPage":{"@id":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/"},"wordCount":273,"commentCount":0,"publisher":{"@id":"https:\/\/webkul.com\/blog\/#organization"},"keywords":["Custom Query","Magento2","mysql"],"articleSection":["magento","Magento2","php"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/","url":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/","name":"Creating Virtual columns in magento2 by using nested queries - Webkul Blog","isPartOf":{"@id":"https:\/\/webkul.com\/blog\/#website"},"datePublished":"2018-10-23T14:40:47+00:00","dateModified":"2024-02-29T09:31:23+00:00","breadcrumb":{"@id":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/webkul.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Creating Virtual columns in magento2 by using nested queries"}]},{"@type":"WebSite","@id":"https:\/\/webkul.com\/blog\/#website","url":"https:\/\/webkul.com\/blog\/","name":"Webkul Blog","description":"","publisher":{"@id":"https:\/\/webkul.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/webkul.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/webkul.com\/blog\/#organization","name":"WebKul Software Private Limited","url":"https:\/\/webkul.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/webkul.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/08\/webkul-logo-accent-sq.png","contentUrl":"https:\/\/cdnblog.webkul.com\/blog\/wp-content\/uploads\/2021\/08\/webkul-logo-accent-sq.png","width":380,"height":380,"caption":"WebKul Software Private Limited"},"image":{"@id":"https:\/\/webkul.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/webkul\/","https:\/\/x.com\/webkul","https:\/\/www.instagram.com\/webkul\/","https:\/\/www.linkedin.com\/company\/webkul","https:\/\/www.youtube.com\/user\/webkul\/"]},{"@type":"Person","@id":"https:\/\/webkul.com\/blog\/#\/schema\/person\/580b43e8509d5fa4f51575bfb8bed418","name":"Vishal Verma","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/webkul.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/31e9b57460b4925d92baea86384d39a23da9408a0d216caea290a9ae75e4b310?s=96&d=https%3A%2F%2Fcdnblog.webkul.com%2Fblog%2Fwp-content%2Fuploads%2F2019%2F10%2Fmike.png&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31e9b57460b4925d92baea86384d39a23da9408a0d216caea290a9ae75e4b310?s=96&d=https%3A%2F%2Fcdnblog.webkul.com%2Fblog%2Fwp-content%2Fuploads%2F2019%2F10%2Fmike.png&r=g","caption":"Vishal Verma"},"url":"https:\/\/webkul.com\/blog\/author\/vishalverma-magento279\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/posts\/148810","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/users\/212"}],"replies":[{"embeddable":true,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/comments?post=148810"}],"version-history":[{"count":6,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/posts\/148810\/revisions"}],"predecessor-version":[{"id":425331,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/posts\/148810\/revisions\/425331"}],"wp:attachment":[{"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/media?parent=148810"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/categories?post=148810"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/tags?post=148810"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}