Solved: adminhtml grid search for a name whos userid is st...

subhan_javid · ‎07-24-2019

Hi All,

I have an admin grid which displays "customer name" in one of its column.

my customtable has "customer_id". So, the grid is joining my customtable with customer_entity and getting the customer name displayed in grid column.

Now when i try to search using "customer name", it shows "no results" . But when search with "customer_id", then it gives results.

I am using UI component in this case. Any suggestions please. My magento2 is EE 2.2.7

Thanks,

Javid

subhan_javid · ‎07-25-2019

Hi @Ravikant soni

Solution you provided is related to getting new column in existing grid. Well thanks for the information you shared.

Solution

In my case, it is to join table results and get the filter search working.

I modified my code in <vendor_module>/Model/ResourceModel/Grid/Collection.php and in method protected function _initSelect() query to retrieve "name" column from secondary table using userid from main table. It worked for me.

Previous Code

$this->getSelect()->join(
                ['customerTable' => $this->getTable('customer_entity')],
                'main_table.customer_id = customerTable.entity_id'
)

Updated Code

$this->getSelect()->join(
                ['customerTable' => $this->getTable('customer_entity')],
                'main_table.customer_id = customerTable.entity_id'
)->columns(new \Zend_Db_Expr("CONCAT(`customerTable`.`firstname`, ' ',`customerTable`.`lastname`) AS fullname"))
            ->join(
                ['cpe' => $this->getTable('catalog_product_entity')],
                'main_table.product_id = cpe.entity_id'
            )->group('main_table.entity_id');

And since i am using ui_component to get the column in grid, so the column tag would be in <vendor_module>/view/adminhtml/ui_component/<gridfilename>.xml

<column name="fullname">
           <argument name="data" xsi:type="array">
               <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                   <item name="label" xsi:type="string" translate="true">Customer Name</item>
                   <item name="sortOrder" xsi:type="number">40</item>
               </item>
           </argument>
       </column>

For those who are new to ui_component, the above ui_component file name <gridfilename.xml> should be defined in <vendor_module>/view/adminhtml/layout/somefilenamewhichmatchestherouteadmingrid.xml

<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
    <body>
        <referenceContainer name="content">
            <!-- here we call our ui component of grid-->
            <uiComponent name="gridfilename"/>
        </referenceContainer>
    </body>
</page>

Thanks,

Javid

View solution in original post

Ravikant soni · ‎07-24-2019

Hi @subhan_javid

This happens when you don't use filtering for the results in join. Please check this link and check out filtering process, that will help you out.

https://belvg.com/blog/how-to-add-column-in-sales-order-grid-in-magento-2-1.html

If my answer is helpful, please Accept as Solution & give Kudos

subhan_javid · ‎07-25-2019

Hi @Ravikant soni

Solution you provided is related to getting new column in existing grid. Well thanks for the information you shared.

Solution

In my case, it is to join table results and get the filter search working.

I modified my code in <vendor_module>/Model/ResourceModel/Grid/Collection.php and in method protected function _initSelect() query to retrieve "name" column from secondary table using userid from main table. It worked for me.

Previous Code

$this->getSelect()->join(
                ['customerTable' => $this->getTable('customer_entity')],
                'main_table.customer_id = customerTable.entity_id'
)

Updated Code

$this->getSelect()->join(
                ['customerTable' => $this->getTable('customer_entity')],
                'main_table.customer_id = customerTable.entity_id'
)->columns(new \Zend_Db_Expr("CONCAT(`customerTable`.`firstname`, ' ',`customerTable`.`lastname`) AS fullname"))
            ->join(
                ['cpe' => $this->getTable('catalog_product_entity')],
                'main_table.product_id = cpe.entity_id'
            )->group('main_table.entity_id');

And since i am using ui_component to get the column in grid, so the column tag would be in <vendor_module>/view/adminhtml/ui_component/<gridfilename>.xml

<column name="fullname">
           <argument name="data" xsi:type="array">
               <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                   <item name="label" xsi:type="string" translate="true">Customer Name</item>
                   <item name="sortOrder" xsi:type="number">40</item>
               </item>
           </argument>
       </column>

For those who are new to ui_component, the above ui_component file name <gridfilename.xml> should be defined in <vendor_module>/view/adminhtml/layout/somefilenamewhichmatchestherouteadmingrid.xml

<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
    <body>
        <referenceContainer name="content">
            <!-- here we call our ui component of grid-->
            <uiComponent name="gridfilename"/>
        </referenceContainer>
    </body>
</page>

Thanks,

Javid

adminhtml grid search for a name whos userid is stored in main table and name is stored in secondary

adminhtml grid search for a name whos userid is stored in main table and name is stored in secondary

Re: adminhtml grid search for a name whos userid is stored in main table and name is stored in secon

Re: adminhtml grid search for a name whos userid is stored in main table and name is stored in secon

Re: adminhtml grid search for a name whos userid is stored in main table and name is stored in secon