Ticket #26 (closed defect: fixed)
Field aliases in OO joins don't work
| Reported by: | cbradford | Owned by: | dhughes |
|---|---|---|---|
| Type: | defect | Priority: | high |
| Milestone: | Component: | Reactor - Object Oriented Queries | |
| Version: | 1.0 | Severity: | major |
| Keywords: | join alias | Cc: |
Description
Using the following:
<object name="GraphCategory" alias="imageCategory">
<field name="CategoryID" alias="ID" />
<hasMany name="graphic">
<relate from="ID" to="parentCategoryID" />
</hasMany>
</object>
<object name="Graphics" alias="graphic">
<field name="GraphicsID" alias="ID" />
<field name="GraphCategoryID" alias="parentCategoryID" />
<hasOne name="imageCategory" alias="parentCategory">
<relate from="parentCategoryID" to="ID" />
</hasOne>
</object>
Now I get a graphicGateway, create a query on it, and start to build a custom query. After adding some where and order conditions, I add a join:
<cffset local.query.join("graphic", "imageCategory", "parentCategory") />
When I call getByQuery(local.query), I get an error from MS SQL Server: invalid column name "parentCategoryID"
The query being attempted has the following in it:
SELECT [graphic].[GraphicsID] AS [ID], [graphic].[Title] AS [Title], [graphic].[Image] AS [Image], [imageCategory].[GraphCategoryID] AS [ID], [imageCategory].[Title] AS [Title], [imageCategory].[bgCategory] AS [bgCategory] FROM [Graphics] AS [graphic] INNER JOIN [GraphCategory] AS [imageCategory] ON [graphic].[parentCategoryID] = [imageCategory].[ID] WHERE [graphic].[Keywords] LIKE (param 1) OR [graphic].[Keywords] LIKE (param 2) ORDER BY [graphic].[Title] ASC
The query is attempting to use the alias in the join, which obviously won't work. Removing the aliases from the reactor.xml hasOne and hasMany tags allows the OO query to work.
