Skip to content

Integrated search not working with query with JOIN

← Search

Problem


Integrated search does not work properly if I use JOIN in the query when there are columns with the same name in both tables:
 
"SELECT a.id as id, a.NameOfFirm, a.Email as Email, a.ContactNumber as ContactCumber, c.LicenseNo FROM Applicants a
INNER JOIN Contractors c on a.id = c.idCompany"

Column NameOfFirm is present in both tables (Applicants and Contractors). When I turn on integrated search and try to search, I get "PHPGRID_ERROR: Column 'NameOfFirm' in where clause is ambiguous"

Solution


The issue is that filter function does not know anything about the alias in Select statement, and it always expects the actual table. 

In fact, if you are using MySQL, it is not possible to have alias in Where statement. 
https://dev.mysql.com/doc/refman/8.4/en/problems-with-alias.html

The solution is to use the full table name instead of alias. 

SELECT Applicants.id as id, Applicants.NameOfFirm, Applicants.Email as Email, Applicants.ContactNumber as ContactCumber, Contractors.LicenseNo
FROM Applicants
INNER JOIN Contractors on Applicants.id = Contractors.idCompany

Feedback and Knowledge Base

(thinking…)