Feature(s) impacted
Search when using replaceSearch.
Observed behavior
Take users with a one-to-many relationship to companies.
This code fails:
users.replaceSearch((searchString, extendedMode, context) => {
return context.generateSearchFilter(searchString, {
extended: extendedMode,
onlyFields: [
'Id',
'FirstName',
'LastName',
'Email',
],
})
})
The error is Ambiguous column name 'Id'.
Indeed, here is the generated SQL:
SELECT [User].[Id],
[User].[FirstName],
[User].[LastName],
[User].[Email],
-- snip other User fields
[Company].[Name] AS [Company.Name],
[Company].[Id] AS [Company.Id]
FROM [Users] AS [User]
LEFT OUTER JOIN [Companies] AS [Company] ON [User].[CompanyId] = [Company].[Id]
WHERE ((LOWER([FirstName]) LIKE N'%search_string%' OR
LOWER([LastName]) LIKE N'%search_string%' OR
LOWER([Id]) LIKE N'%search_string%' OR -- Uh-oh.
LOWER([Email]) LIKE N'%search_string%'))
ORDER BY [User].[Id] ASC
OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY;
The offending line is in the WHERE clause, in which you can see we’re hitting an ambiguous Id. MSSQL has no way to know whether we’re looking up a company Id or a user Id.
Expected behavior
The generated SQL should properly qualify fields looked up in the WHERE clause.
Failure Logs
===== An exception was raised =====
GET /forest/Users/count?{
-- snip
}
Ambiguous column name 'Id'.
Error
at Query.run -- snip...
===================================
Context
- Agent technology: nodejs
- Agent (forest package) name & version: 1.40.1 with sequelize 6.37.3 and sequelize-typescript 2.1.6
- Database type: MSSQL
- Recent changes made on your end if any: Migrated from old agent.