Select with an alias does not show alias column in "having".

[ruby user]

The Model Image has many Tags

When i run this query to get Images with tags that are named both bamboo in and Green i get an error that says the alias table is not present. What am I doing wrong?

Image.joins(:tags).select("images.*, count(tags.id) as 'tags_count'").where(tags:{name:["bamboo","Green"]}).group("images.id").having("tags_count = 2")

Error 

Image Load (0.4ms)  SELECT images.*, count(tags.id) as tags_count FROM "images" INNER JOIN "image_tags" ON "image_tags"."image_id" = "images"."id" INNER JOIN "tags" ON "tags"."id" = "image_tags"."tag_id" WHERE "tags"."name" IN ('bamboo', 'Green') GROUP BY images.id HAVING tags_count = 2

PG::UndefinedColumn: ERROR:  column "tags_count" does not exist

LINE 1: ... IN ('bamboo', 'Green') GROUP BY images.id HAVING tags_count...

                                                             ^

: SELECT images.*, count(tags.id) as tags_count FROM "images" INNER JOIN "image_tags" ON "image_tags"."image_id" = "images"."id" INNER JOIN "tags" ON "tags"."id" = "image_tags"."tag_id" WHERE "tags"."name" IN ('bamboo', 'Green') GROUP BY images.id HAVING tags_count = 2

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "tags_count" does not exist

LINE 1: ... IN ('bamboo', 'Green') GROUP BY images.id HAVING tags_count...

[Christian Kruse]

Hi,

At Wed, 3 Sep 2014 10:38:13 -0700 (PDT), ruby user wrote:
> When i run this query to get Images with tags that are named both
> bamboo in and Green i get an error that says the alias table is not
> present. What am I doing wrong?
>
> Image.joins(:tags).select("images.*, count(tags.id) as 'tags_count'").where(tags:{name:["bamboo","Green"]}).group("images.id").having
> ("tags_count = 2")
>
> Error
>
> Image Load (0.4ms) SELECT images.*, count(tags.id) as tags_count FROM "images" INNER JOIN "image_tags" ON "image_tags"."image_id" =
> "images"."id" INNER JOIN "tags" ON "tags"."id" = "image_tags"."tag_id" WHERE "tags"."name" IN ('bamboo', 'Green') GROUP BY images.id HAVING
> tags_count = 2
>
> PG::UndefinedColumn: ERROR: column "tags_count" does not exist
>
> LINE 1: ... IN ('bamboo', 'Green') GROUP BY images.id HAVING tags_count...
>
> ^

Well, "doing it wrong" is not the phrase I would choose. In
PostgreSQL, aliases are in a different scope than HAVING. HAVING may
not access aliases, you have to repeat the aggregate (will be
optimized to not be executed again). This is part of the SQL standard,
AFAIK.

Best regards,

--
Christian Kruse
http://ck.kennt-wayne.de/