Coldfusion ORM Count many-to-many

Question

I have posts which can be assigned many categories. so its a many-to-many relationship.

I want to get a count of how many posts (that fit a certain criteria) are in each category and then order the results.

I have:

Select ( Select count(post.id)
         From post
         Join category as postcat
         where postcat.id = category.id
         and (post.deleted is null or post.deleted = false)
         and ...
       ), category
From category
order by ????? DESC, category.name

I want to order it by the count column. But I can't declare an alias on it. It just ignores any alias I add. then throws a sql error saying:

java.sql.SQLSyntaxErrorException: [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'numPosts'.

This is what I tried:

         ...
         and (post.deleted is null or post.deleted = false)
         and ...
       ) as numPosts, category
From category
order by numPosts DESC, category.name

I checked the HQL runtime log and the 'as numPosts' isn't being set on the count. I don't know how to get around this.

Answer 1

I haven't come up with a solution, but I do have a work around.

SELECT (SELECT count(post.id)
    FROM post
    JOIN post.category postCategory
    WHERE postCategory.id = category.id
    AND (post.deleted IS NULL OR post.deleted = <cfqueryparam value="#false#">)
    ), category
FROM category
order by 1 desc, category.name

Instead of ordering by an alias, I am just ordering by the column number. Which seems to work. I'm not happy about it. But it works.