Rails impressionist gem - missing FROM-clause entry for table "impressions"

Question

I am using Rails 4.2 and have set up the impressionist gem which is working fine for logging page impressions.

I am trying to do exactly the same thing as in this Stackoverflow post but the answer isn't working. I tried:

start_time = 30.days.ago
@mostReadAlbums30Days = Album.joins(:impressions).where("impressions.created_at<='#{Time.now}' and impressions.created_at >= '#{start_time}'").group("impressions.impressionable_id").order("count(impression‌s.id) DESC")

This produces the following SQL and error message

PG::UndefinedTable: ERROR: missing FROM-clause entry for table "impression‌s" LINE 1: ... BY impressions.impressionable_id ORDER BY count(impression...

^ : SELECT "albums".* FROM "albums" INNER JOIN "impressions" ON "impressions"."impressionable_id" = "albums"."id" AND "impressions"."impressionable_type" = $1 WHERE (impressions.created_at<='2015-01-02 00:50:18 -0200' and impressions.created_at >= '2014-12-03 02:50:18 UTC') GROUP BY impressions.impressionable_id ORDER BY count(impression‌s.id) DESC

I'm using Postgresql v9.3.5.2. How can I get this query to work?

There's a subtle typo in your query. It contains a Unicode Character 'ZERO WIDTH SPACE' (U+200B) between the n and s of ORDER BY count(impression‌s.id). Look at how impressions gets line-broken in the rendering of the error message, that wouldn't happen otherwise. — Daniel Vérité
Great, that worked perfectly. Please add it as an answer! It gave me the (infamous) error "column "albums.id" must appear in the GROUP BY clause or be used in an aggregate function" - if I add albums.id to the GROUP BY clause it seems to work, is this the right approach? — tacua

Daniel Vérité Daniel Vérité · Accepted Answer · 2015-01-02T19:47:22

The main problem (per comment):

There's a subtle typo in your query. It contains a Unicode Character ZERO WIDTH SPACE (U+200B) between the n and s of ORDER BY count(impression‌s.id). Look at how impressions gets line-broken in the rendering of the error message, that wouldn't happen otherwise.

The GROUP BY problem: since the query selects albums.*, it has to GROUP BY albums.id rather than GROUP BY impressions.impressionable_id (assuming that albums.id is a primary key). This is numerically equivalent since the tables are equi-joined through these two fields anyway, but as it seems, the SQL engine in this context can't figure out this equivalency by itself.

Rails impressionist gem - missing FROM-clause entry for table "impressions"

1 Answers