Ambiguous Column SQL error when using a concatenated field

I am buidling an app in CakePHP. I have 2 models: - Project - User

The Project model has various belongsTo relations to the user model, one for the creator, one for the last editor and one for the manager. This works fine.

Then I add a virtual field to the User model, called 'name', which is CONCAT(first_name, ' ', last_name). It combines the first name and last name into a general name field, which is used througout the app.

After this, I get SQL errors saying that the first_name column is ambiguous. This is because in the query, the alias for Creator, Manager, etc is not used in the CONCAT field.

A开发者_JAVA技巧ny ideas on how to avoid this?

Showing the exact queries should help resolve this problem. But if you are joining 2 tables, and they both have a column with the same name. you have to reference the column with TableName.ColumnName, like this.

Select Table1.Column1 AS someColumn, Table2.Column1 AS SomeOtherColumn
FROM Table1
INNER JOIN Table2 
ON Table1.ID = Table2.Table1ID
WHERE Table1.ID = 3

You can shorten this up by giving your tables aliases, As follows.

Select T1.Column1 AS someColumn, T2.Column1 AS SomeOtherColumn
FROM Table1 AS T1
INNER JOIN Table2  AS T2
ON T1.ID = T2.Table1ID
WHERE T1.ID = 3

I found the solution: http://book.cakephp.org/view/1632/Virtual-fields-and-model-aliases

Try specifying what table the first_name column is from. Something like this:

CONCAT(table1.first_name,'',table1.last_name)