{"id":150845,"date":"2018-11-10T11:37:24","date_gmt":"2018-11-10T11:37:24","guid":{"rendered":"https:\/\/webkul.com\/blog\/?p=150845"},"modified":"2024-02-29T09:03:53","modified_gmt":"2024-02-29T09:03:53","slug":"use-nested-queries-in-magento2-admin-uigrid","status":"publish","type":"post","link":"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/","title":{"rendered":"Use Nested Queries in Magento2 Admin UiGrid"},"content":{"rendered":"\n<p>In this blog we, will be learning how to use nested queries in Magento2 Ui Admin Grid for creating the desired result set.<\/p>\n\n\n\n<p>You can check the basics of creating the Ui admin grid at:&nbsp;<a href=\"https:\/\/webkul.com\/blog\/how-to-create-a-grid-using-ui-component\/\">https:\/\/webkul.com\/blog\/how-to-create-a-grid-using-ui-component\/<\/a><br>and the basics of creating virtual columns using nested queries in Magento 2 at:&nbsp;<a href=\"https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/\">https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/<\/a><\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>To implement the same, we need to go to the file: <strong>Webkul\\Hello\\Model\\ResourceModel\\Employee\\Grid\\Collection<\/strong> and implement the method: <strong>_renderFiltersBefore<\/strong>()<\/p>\n\n\n\n<p>This method is called before rendering the filters and the same is called before applying the filters on the UiGrid<\/p>\n\n\n\n<p>The nested query(as described in the previous mentioned) blog will be implemented in the Collection file as:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\">&lt;?php\n\/**\n * Webkul Software.\n *\n * @category  Webkul\n * @package   Webkul_Hello\n * @author    Webkul\n * @copyright Copyright (c) Webkul Software Private Limited (https:\/\/webkul.com)\n * @license   https:\/\/store.webkul.com\/license.html\n *\/\n\nnamespace Webkul\\Hello\\Model\\ResourceModel\\Employee\\Grid;\n\nuse Magento\\Framework\\Api\\Search\\SearchResultInterface;\nuse Webkul\\Hello\\Model\\ResourceModel\\Employee\\Collection as QuoteCollection;\n\nclass Collection extends EmployeeCollection implements SearchResultInterface\n{\n    public function __construct(\n        \\Magento\\Quote\\Model\\Quote $modelQuote,\n        \\Magento\\Framework\\Data\\Collection\\EntityFactoryInterface $entityFactory,\n        \\Psr\\Log\\LoggerInterface $logger,\n        \\Magento\\Framework\\Data\\Collection\\Db\\FetchStrategyInterface $fetchStrategy,\n        \\Magento\\Framework\\Event\\ManagerInterface $eventManager,\n        \\Magento\\Store\\Model\\StoreManagerInterface $storeManager,\n        $mainTable,\n        $eventPrefix,\n        $eventObject,\n        $resourceModel,\n        $model = &#039;Magento\\Framework\\View\\Element\\UiComponent\\DataProvider\\Document&#039;,\n        $connection = null,\n        \\Magento\\Framework\\Model\\ResourceModel\\Db\\AbstractDb $resource = null\n    ) {\n        parent::__construct(\n            $entityFactory,\n            $logger,\n            $fetchStrategy,\n            $eventManager,\n            $storeManager,\n            $connection,\n            $resource\n        );\n        $this-&gt;_modelQuote = $modelQuote;\n        $this-&gt;_eventPrefix = $eventPrefix;\n        $this-&gt;_eventObject = $eventObject;\n        $this-&gt;_init($model, $resourceModel);\n        $this-&gt;setMainTable($mainTable);\n    }\n\n    \/**\n     * use the nested query here to change the result data set\n     **\/\n    protected function _renderFiltersBefore()\n    {\n        $modelQuote = $this-&gt;_modelQuote;\n        $queryCopy = clone($modelQuote-&gt;getCollection());\n        \/\/cloning the object to use later\n        $query = $modelQuote-&gt;getCollection()\n                            -&gt;getSelect()\n                            -&gt;reset(&#039;columns&#039;)\n                            -&gt;columns(&quot;main_table.entity_id&quot;)\n                            -&gt;columns(&quot;CONCAT(customer_firstname, customer_lastname) as customer_fullname&quot;);\n        $query-&gt;__toString();\n        $finalQuery = $queryCopy-&gt;getSelect()\n                                -&gt;from($query)\n                                -&gt;where(&quot;t.entity_id=main_table.entity_id&quot;);\n        parent::_renderFiltersBefore();\n    }\n}<\/pre>\n\n\n\n<p>Now, the column <strong>customer_fullname<\/strong> 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<\/p>\n\n\n\n<p>If you have any doubts or queries, please put in the comments section.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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:&nbsp;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:&nbsp;https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/ The advantages of using virtual columns in <a href=\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/\">[&#8230;]<\/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":[2056,2460,2070],"class_list":["post-150845","post","type-post","status-publish","format-standard","hentry","category-magento","category-magento2","category-php","tag-magento","tag-magento-2","tag-magento2"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Use Nested Queries in Magento2 Admin UiGrid - 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\/use-nested-queries-in-magento2-admin-uigrid\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Use Nested Queries in Magento2 Admin UiGrid - Webkul Blog\" \/>\n<meta property=\"og:description\" content=\"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:&nbsp;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:&nbsp;https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/ The advantages of using virtual columns in [...]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/\" \/>\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-11-10T11:37:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-29T09:03:53+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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/\"},\"author\":{\"name\":\"Vishal Verma\",\"@id\":\"https:\/\/webkul.com\/blog\/#\/schema\/person\/580b43e8509d5fa4f51575bfb8bed418\"},\"headline\":\"Use Nested Queries in Magento2 Admin UiGrid\",\"datePublished\":\"2018-11-10T11:37:24+00:00\",\"dateModified\":\"2024-02-29T09:03:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/\"},\"wordCount\":204,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/webkul.com\/blog\/#organization\"},\"keywords\":[\"magento\",\"Magento 2\",\"Magento2\"],\"articleSection\":[\"magento\",\"Magento2\",\"php\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/\",\"url\":\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/\",\"name\":\"Use Nested Queries in Magento2 Admin UiGrid - Webkul Blog\",\"isPartOf\":{\"@id\":\"https:\/\/webkul.com\/blog\/#website\"},\"datePublished\":\"2018-11-10T11:37:24+00:00\",\"dateModified\":\"2024-02-29T09:03:53+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/webkul.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Use Nested Queries in Magento2 Admin UiGrid\"}]},{\"@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":"Use Nested Queries in Magento2 Admin UiGrid - 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\/use-nested-queries-in-magento2-admin-uigrid\/","og_locale":"en_US","og_type":"article","og_title":"Use Nested Queries in Magento2 Admin UiGrid - Webkul Blog","og_description":"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:&nbsp;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:&nbsp;https:\/\/webkul.com\/blog\/creating-virtual-columns-in-magento2-by-using-nested-queries\/ The advantages of using virtual columns in [...]","og_url":"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/","og_site_name":"Webkul Blog","article_publisher":"https:\/\/www.facebook.com\/webkul\/","article_published_time":"2018-11-10T11:37:24+00:00","article_modified_time":"2024-02-29T09:03:53+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":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/#article","isPartOf":{"@id":"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/"},"author":{"name":"Vishal Verma","@id":"https:\/\/webkul.com\/blog\/#\/schema\/person\/580b43e8509d5fa4f51575bfb8bed418"},"headline":"Use Nested Queries in Magento2 Admin UiGrid","datePublished":"2018-11-10T11:37:24+00:00","dateModified":"2024-02-29T09:03:53+00:00","mainEntityOfPage":{"@id":"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/"},"wordCount":204,"commentCount":0,"publisher":{"@id":"https:\/\/webkul.com\/blog\/#organization"},"keywords":["magento","Magento 2","Magento2"],"articleSection":["magento","Magento2","php"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/","url":"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/","name":"Use Nested Queries in Magento2 Admin UiGrid - Webkul Blog","isPartOf":{"@id":"https:\/\/webkul.com\/blog\/#website"},"datePublished":"2018-11-10T11:37:24+00:00","dateModified":"2024-02-29T09:03:53+00:00","breadcrumb":{"@id":"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/webkul.com\/blog\/use-nested-queries-in-magento2-admin-uigrid\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/webkul.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Use Nested Queries in Magento2 Admin UiGrid"}]},{"@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\/150845","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=150845"}],"version-history":[{"count":5,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/posts\/150845\/revisions"}],"predecessor-version":[{"id":425323,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/posts\/150845\/revisions\/425323"}],"wp:attachment":[{"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/media?parent=150845"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/categories?post=150845"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/webkul.com\/blog\/wp-json\/wp\/v2\/tags?post=150845"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}