Customer review report search Bug in 2.1.x, 2.2

[mageprince]

Preconditions

1. Magento 2.1.5,2.1.6,2.1.7
2. PHP 7.0.17

Steps to reproduce

1. Go to Reports -> By Customers
2. Enter customer name in search box
3. Click on serach

Expected result

1. Retrive customer result

Actual result

1. Fatal Error: Column not found: 1054 Unknown column 'customer_name' in 'where clause', query was: SELECT COUNT(DISTINCT detail.customer_id) FROM review AS main_table

[vishveskrish]

Hey @Prince108 ,

We are facing same issue ! let us know if you found any patch.

Thanks,
Vishves Shah

[dev-Dhaval]

Its solved

need to update two files

1. Update _joinCustomers function in
   vendor/magento/module-reports/Model/ResourceModel/Review/Customer/Collection.php

protected function _joinCustomers()
    {
	
        /** @var $connection \Magento\Framework\DB\Adapter\AdapterInterface */
        $connection = $this->getConnection();
        //Prepare fullname field result
        $customerFullname = $connection->getConcatSql(['customer.firstname'], ' ');
        $this->getSelect()->reset(
            \Magento\Framework\DB\Select::COLUMNS
        )->joinInner(
            ['customer' => $connection->getTableName('customer_entity')],
            'customer.entity_id = detail.customer_id',
            []
        )->columns(
            [
                'customer_id' => 'detail.customer_id',
                'firstname' => $customerFullname,
                'review_cnt' => 'COUNT(main_table.review_id)',
            ]
        )->group(
            'detail.customer_id'
        );
	//echo $this->getSelect()->__toString();;exit;
        return $this;
    }

2. Update line no 43 to 53 in below file
   vendor/magento/module-reports/view/adminhtml/layout/reports_report_review_customer_grid.xml

<block class="Magento\Backend\Block\Widget\Grid\Column" as="firstname">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Customer</argument>
                            <argument name="index" xsi:type="string">firstname</argument>
                            <argument name="type" xsi:type="string">text</argument>
                            <argument name="id" xsi:type="string">firstname</argument>
                            <argument name="default" xsi:type="string" translate="true">Guest</argument>
                            <argument name="column_css_class" xsi:type="string">col-name</argument>
                            <argument name="header_css_class" xsi:type="string">col-name</argument>
                        </arguments>
                    </block>

[mageprince]

@dev-Dhaval thanks for your reply. But this will not work. I have debugged code and found the issue.
If you see below query by printing $this->_joinCustomers() values.

SELECT detail.customer_id, CONCAT_WS(' ', customer.firstname, customer.lastname) AS customer_name, COUNT(main_table.review_id) AS review_cntFROMbn0z_reviewASmain_tableINNER JOINbn0z_review_detailASdetailON main_table.review_id = detail.review_id INNER JOINbn0z_customer_entityAScustomerON customer.entity_id = detail.customer_id GROUP BYdetail.customer_id``

Case 1 : each column assigns by the alias like 'customer_name`, 'review_cnt', ... that's fine. But when we search on grid It is taking wrong data.
You can debug and share your thoughts on it.
@vishveskrish: We are waiting for the response from Magento team.

[dev-Dhaval]

@Prince108 , your query look like coming from cache. because in our updated code have not use customer.lastname and its showing in your query. can you please apply setup :upgrade command . because it's working from my side or compare _joinCustomers with our updated code
Thanks

[mageprince]

@dev-Dhaval, I print this query in default Magento collection.php without changes.
In your code, you just change alias name and assign this name in grid xml. It's doesn't matter filed has value or not.

what I am trying to say that $this->_joinCustomers() returns right query.
I also try to run this query directly in phpmyadmin it's working fine. but at the time of search customer grid get columns (customer_name and review_cnt) from directly in tables without including this join query.

You can debug and share your thoughts on it.
We are waiting for the response from Magento team.

[RomanKrut]

@Prince108 Hi. Thanks for your feedback. The internal ticket MAGETWO-70955 was created to address this issue.

[mageprince]

@veloraven

This issue still continues in Magento 2.1.8 and Magento 2.2 also in Magento 2.2 EE. I have developed simple module to overcome this issue. If you need I can share with you.

Thank You
Prince Patel