Error when using an include in SQL with a nested query in the properties

[edthetechie]

Linking two tables together using a model call with an include, the model has a property with a nested query doing a count. This was working in 1.3.1 but fails in 1.4.4. We recently upgraded to CF11 and MS SQL 2014.

The model call looks like this:

<cfset criterias = model("criteria").findAll(where="itemtypeid='#this.itemtypeid#' AND id IN ('3','4','70','71','72','73','15')", include="criteriaitemtypelinks", distinct="true", order="priority")>

The property on the criteria model causing the problems is:

property(
    name="numberOfItems",
    sql="select count(id) from criteriaitems where deletedat IS NULL and criteriaid = criterias.id"
);

The error is as follows

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

The error occurred in E:/path_to/wheels/model/adapters/Base.cfc: line 296
Called from E:/path_to/wheels/model/adapters/SQLServer.cfc: line 205
Called from E:/path_to/wheels/model/read.cfm: line 203
Called from E:/path_to/controllers/Edit.cfc: line 396
Called from E:/path_to/wheels/global/cfml.cfm: line 164
Called from E:/path_to/wheels/controller/processing.cfm: line 128
Called from E:/path_to/wheels/controller/processing.cfm: line 95
Called from E:/path_to/wheels/dispatch/request.cfm: line 198
Called from E:/path_to/wheels/index.cfm: line 1
Called from E:/path_to/rewrite.cfm: line 1
Called from E:/path_to/wheels/events/onrequest.cfm: line 1
Called from E:/path_to/wheels/model/adapters/Base.cfc: line 296
Called from E:/path_to/wheels/model/adapters/SQLServer.cfc: line 205
Called from E:/path_to/wheels/model/read.cfm: line 203
Called from E:/path_to/controllers/Edit.cfc: line 396
Called from E:/path_to/wheels/global/cfml.cfm: line 164
Called from E:/path_to/wheels/controller/processing.cfm: line 128
Called from E:/path_to/wheels/controller/processing.cfm: line 95
Called from E:/path_to/wheels/dispatch/request.cfm: line 198
Called from E:/path_to/wheels/index.cfm: line 1
Called from E:/path_to/rewrite.cfm: line 1
Called from E:/path_to/wheels/events/onrequest.cfm: line 1

294 :       StructAppend(loc.args, loc.orgArgs);
295 :     </cfscript>
296 :     <cfquery attributeCollection="#loc.args#"><cfset loc.pos = 0><cfloop array="#arguments.sql#" index="loc.i"><cfset loc.pos = loc.pos + 1><cfif IsStruct(loc.i)><cfset loc.queryParamAttributes = $CFQueryParameters(loc.i)><cfif NOT IsBinary(loc.i.value) AND loc.i.value IS "null" AND loc.pos GT 1 AND (Right(arguments.sql[loc.pos-1], 2) IS "IS" OR Right(arguments.sql[loc.pos-1], 6) IS "IS NOT")>NULL<cfelseif StructKeyExists(loc.queryParamAttributes, "list")><cfif arguments.parameterize>(<cfqueryparam attributeCollection="#loc.queryParamAttributes#">)<cfelse>(#PreserveSingleQuotes(loc.i.value)#)</cfif><cfelse><cfif arguments.parameterize><cfqueryparam attributeCollection="#loc.queryParamAttributes#"><cfelse>#$quoteValue(str=loc.i.value, sqlType=loc.i.type)#</cfif></cfif><cfelse><cfset loc.i = Replace(PreserveSingleQuotes(loc.i), "[[comma]]", ",", "all")>#PreserveSingleQuotes(loc.i)#</cfif>#chr(13)##chr(10)#</cfloop><cfif arguments.limit>LIMIT #arguments.limit#<cfif arguments.offset>#chr(13)##chr(10)#OFFSET #arguments.offset#</cfif></cfif></cfquery>
297 :     <cfscript>
298 :       if (StructKeyExists(query, "name"))

[perdjurner]

Can you show the SQL it used to produce before the bug and the SQL it produces now?

[edthetechie]

Before:

SELECT criterias.id, 
       criterias.oldid, 
       criterias.title, 
       criterias.description, 
       criterias.displayed, 
       criterias.priority, 
       criterias.tooltip, 
       criterias.createdat, 
       criterias.updatedat, 
       criterias.deletedat, 
       criterias.createdby, 
       criterias.updatedby, 
       criterias.deletedby, 
       criterias.multipleinputs, 
       criterias.peruser, 
       (SELECT Count(id) 
        FROM   criteriaitems 
        WHERE  deletedat IS NULL 
               AND criteriaid = criterias.id) AS numberOfItems, 
       criteriaitemtypelinks.criteriaid, 
       criteriaitemtypelinks.itemtypeid 
FROM   criterias 
       LEFT OUTER JOIN criteriaitemtypelinks 
                    ON criterias.id = criteriaitemtypelinks.criteriaid 
WHERE  ( criteriaitemtypelinks.itemtypeid = 1 
         AND criterias.id IN ( 3, 4, 70, 71, 72, 73, 15 ) 
        ) 
       AND ( criterias.deletedat IS NULL ) 
GROUP  BY criterias.id, 
          criterias.oldid, 
          criterias.title, 
          criterias.description, 
          criterias.displayed, 
          criterias.priority, 
          criterias.tooltip, 
          criterias.createdat, 
          criterias.updatedat, 
          criterias.deletedat, 
          criterias.createdby, 
          criterias.updatedby, 
          criterias.deletedby, 
          criterias.multipleinputs, 
          criterias.peruser, 
          criteriaitemtypelinks.criteriaid, 
          criteriaitemtypelinks.itemtypeid 
ORDER  BY criterias.priority ASC 

After

SELECT          criterias.id, 
                criterias.oldid, 
                criterias.title, 
                criterias.description, 
                criterias.displayed, 
                criterias.priority, 
                criterias.tooltip, 
                criterias.createdat, 
                criterias.updatedat, 
                criterias.deletedat, 
                criterias.createdby, 
                criterias.updatedby, 
                criterias.deletedby, 
                criterias.multipleinputs, 
                criterias.peruser, 
                ( 
                       SELECT Count(id) 
                       FROM   criteriaitems 
                       WHERE  deletedat IS NULL 
                       AND    criteriaid = criterias.id) AS numberofitems, 
                criteriaitemtypelinks.criteriaid, 
                criteriaitemtypelinks.itemtypeid 
FROM            criterias 
LEFT OUTER JOIN criteriaitemtypelinks 
ON              criterias.id = criteriaitemtypelinks.criteriaid 
WHERE           ( 
                                criteriaitemtypelinks.itemtypeid = 1 
                AND             criterias.id IN ( 3, 4, 70, 71, 72, 73, 15 )  ) 
AND             ( 
                                criterias.deletedat IS NULL ) 
GROUP BY        criterias.id, 
                criterias.oldid, 
                criterias.title, 
                criterias.description, 
                criterias.displayed, 
                criterias.priority, 
                criterias.tooltip, 
                criterias.createdat, 
                criterias.updatedat, 
                criterias.deletedat, 
                criterias.createdby, 
                criterias.updatedby, 
                criterias.deletedby, 
                criterias.multipleinputs, 
                criterias.peruser, 
                ( 
                       SELECT count(id) 
                       FROM   criteriaitems 
                       WHERE  deletedat IS NULL 
                       AND    criteriaid = criterias.id), 
                criteriaitemtypelinks.criteriaid, 
                criteriaitemtypelinks.itemtypeid 
ORDER BY        criterias.priority ASC

[perdjurner]

Thanks, a work-around might be to avoid using the "distinct" argument if possible.