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.

I have seen: hibernate.onjira.com/browse/HHH-892 but I didn't think it was related because the HQL wasn't rendering the alias at all in the main section. so it wouldn't have worked anywhere, not just in the order by. and the issue has been marked as fixed — Rumpleteaser

Rumpleteaser Rumpleteaser · Accepted Answer · 2013-01-23T03:57:40

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.

Coldfusion ORM Count many-to-many

1 Answers