I have the group and product entities. In the product entity there is a fk referencing the group entity.
I'm trying to query the product table and I'm using CreateAlias to check a field in the group table.
When I run the query, I get the following error: '[FireDAC] [Phys] [PG] [libpq] ERROR: column reference "codigo" is ambiguous'.
From what I understand, it is an error by the database itself, saying that it found two codigo columns, and in fact in both entities it has this codigo field with the same name. How do I get through this problem and make the query without changing the entity?
This is the query code: FManager.Find (EntityClass)
.Select (TProjections.Sql ('MAX (CAST (CODIGO as INT)))))
.CreateAlias ('FFkGroup', 'G') //
.Where (Linq ['G.FkUsuario'] = GUIDToString (FFkEstablishment)) //
.Add (Linq.Sql ('{Codigo} not like' + QuotedStr ('@%'))) //
.Add (Linq ['FCodigo']. IsNotNull) //
.UniqueValue
The problem is that you are using Sql projection which is a low level feature. The SQL snippet you provide will be added to the final SQL as-is. Since the query created by aurelius has a join between Group and Product, the CODIGO field will be present in both tables and you need to prefix the field as with any SQL.
You must either know in advance the alias used by Aurelius (usually that would be A.CODIGO for the original class EntityClass). Or you can use macros to ask Aurelius to replace a property name by the correct field value:
I applied this change Select (TProjections.Sql ('MAX (CAST ({Codigo} as INT))))) and I was able to make the query, I did not need to put A.CODIGO. Thank you for your help.