SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'lastname' in where clause is ambiguous

[jfilipesantos]

Hello

I have installed a prestashop 1.7.7.1

In backoffice when i go to  index.php/sell/customers/ this error appears.

An exception occurred while executing 'SELECT c.id_customer, c.firstname, c.lastname, c.email, c.active, c.newsletter, c.optin, c.date_add, gl.name as social_title, s.name as shop_name, c.company, (SELECT SUM(total_paid_real / conversion_rate) FROM ps_orders o WHERE (o.id_customer = c.id_customer) AND (o.id_shop IN (?, ?, ?, ?, ?)) AND (o.valid = 1)) as total_spent, (SELECT con.date_add FROM ps_guest g LEFT JOIN ps_connections con ON con.id_guest = g.id_guest WHERE g.id_customer = c.id_customer ORDER BY con.date_add DESC LIMIT 1) as connect, IF(wcm.`phone` IS NULL,0,wcm.`phone`) AS `phone` FROM ps_customer c LEFT JOIN ps_gender_lang gl ON c.id_gender = gl.id_gender AND gl.id_lang = ? LEFT JOIN ps_shop s ON c.id_shop = s.id_shop LEFT JOIN `ps_customer` wcm ON wcm.`id_customer` = c.`id_customer` WHERE (c.deleted = 0) AND (c.id_shop IN (?, ?, ?, ?, ?)) AND (`lastname` LIKE ?) AND (wcm.`phone` = ?) ORDER BY c.date_add DESC LIMIT 50' with params [9, 1, 7, 10, 8, 1, 9, 1, 7, 10, 8, "%abcd%", "999999999"]:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'lastname' in where clause is ambiguous

 

I tried to discover the location of this sql statement but i cannot find it.

The error is that no table was added to sql and more than one table used in the statement has a column called lastname.

 

My problem is where to find this sql statement to correct it.

 

Thanks

Edited February 16, 2021 by jfilipesantos (see edit history)

[Prestachamps]

Hi,

do you have some override or module which has something to do with the Backoffice customers, or customer details? Most probably that one has this bug, as the issueis in this section: "AND (`lastname` LIKE ?) AND" where a table name/alias should be specified like "AND (wcm.`lastname` LIKE ?) AND"

You should try to disable the overrides from performance page, and if still have the error, then try to disable some customer information related modules, as most probably there is the issue in one of them.

Kind regards, Leo

[musicmaster]

To find the query you should enable _PS_DEBUG_PROFILING_ in /config/defines.in.php

It will generate below the page that you are looking at a long list of queries that have been run. With a text search in the browser it should be easy to find your query. At the end of each line (you likely will need to scroll to the right) you find the filenames and line numbers.

Edited February 17, 2021 by musicmaster (see edit history)

[jfilipesantos]

Hello

Thanks in advance for your suggestions

I've activated PS_DEBUG_PROFILING and it does what should do in every other page but not in the Clients page.

When i click it  Clients - Clients, it shows immediately symfony Exception.

I've disabled all non native modules and all substitutions but the problem remains. I've disabled a module in the Clients category but problem remains.

 

Thanks

[musicmaster]

I find this an increasingly strange story:

 - index.php/sell/customers/ is not a valid path in the backoffice

 - all those question marks in your query are weird and shouldn't be there

 - you talk now about a Symfony exception but don't show it.

I noticed that the inner half of the query comes from the function appendTotalSpentQuery() in /src/Core/Grid/Query/CustomerQueryBuilder.php