SQL error when using pg_search_scope associated_against for a calcuation query with includes scope

[kreintjes]

Hi,

I have a strange problem. I have a pg_search_scope that searches in associations as well, using associated_against. When I use this scope for a calculation query (e.g. sum) with the includes scope set, then I get an SQL query error. I think I can best explain this using an example:

We have three models: Project, Task and Log. A Task belongs to a project and a Log belongs to a Task (and Project). On the Log model I have the following pg_search_scope defined:

pg_search_scope :search, against: { description: 'A' }, associated_against: { project: { name: 'B' }, task: { name: 'B' } }

I have the following ActiveRecord relation:

@results = Log.includes(task: :project).search('test')

When using this to display all the items. It works fine. However, when I use the same relation to sum something (@results.sum(:something)), then I get the following SQL error:

Log.includes(task: :project).search('test').sum(:id)
   (0.6ms)  SELECT SUM("logs"."id") FROM "logs" LEFT OUTER JOIN "tasks" "tasks_logs" ON "tasks_logs"."id" = "logs"."task_id" LEFT OUTER JOIN "projects" "projects_tasks" ON "projects_tasks"."id" = "tasks"."project_id" LEFT OUTER JOIN (SELECT "logs"."id" AS id, string_agg("projects"."name"::text, ' ') AS pg_search_86a5b22b6348a78c634fcf FROM "logs" INNER JOIN "projects" ON "projects"."id" = "logs"."project_id" GROUP BY "logs"."id") pg_search_65e3f8265dfa723d721ade ON pg_search_65e3f8265dfa723d721ade.id = "logs"."id" LEFT OUTER JOIN (SELECT "logs"."id" AS id, string_agg("tasks"."name"::text, ' ') AS pg_search_27264e2376734ab2733873 FROM "logs" INNER JOIN "tasks" ON "tasks"."id" = "logs"."task_id" GROUP BY "logs"."id") pg_search_c8df7499cbfe4dcae83666 ON pg_search_c8df7499cbfe4dcae83666.id = "logs"."id" WHERE (((setweight(to_tsvector('simple', coalesce("logs"."description"::text, '')), 'A') || setweight(to_tsvector('simple', coalesce(pg_search_65e3f8265dfa723d721ade.pg_search_86a5b22b6348a78c634fcf::text, '')), 'B') || setweight(to_tsvector('simple', coalesce(pg_search_c8df7499cbfe4dcae83666.pg_search_27264e2376734ab2733873::text, '')), 'B')) @@ (to_tsquery('simple', ''' ' || 'test' || ' '''))))
PG::UndefinedTable: ERROR:  invalid reference to FROM-clause entry for table "tasks"
LINE 1: ...ects" "projects_tasks" ON "projects_tasks"."id" = "tasks"."p...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "tasks_logs".
: SELECT SUM("logs"."id") FROM "logs" LEFT OUTER JOIN "tasks" "tasks_logs" ON "tasks_logs"."id" = "logs"."task_id" LEFT OUTER JOIN "projects" "projects_tasks" ON "projects_tasks"."id" = "tasks"."project_id" LEFT OUTER JOIN (SELECT "logs"."id" AS id, string_agg("projects"."name"::text, ' ') AS pg_search_86a5b22b6348a78c634fcf FROM "logs" INNER JOIN "projects" ON "projects"."id" = "logs"."project_id" GROUP BY "logs"."id") pg_search_65e3f8265dfa723d721ade ON pg_search_65e3f8265dfa723d721ade.id = "logs"."id" LEFT OUTER JOIN (SELECT "logs"."id" AS id, string_agg("tasks"."name"::text, ' ') AS pg_search_27264e2376734ab2733873 FROM "logs" INNER JOIN "tasks" ON "tasks"."id" = "logs"."task_id" GROUP BY "logs"."id") pg_search_c8df7499cbfe4dcae83666 ON pg_search_c8df7499cbfe4dcae83666.id = "logs"."id" WHERE (((setweight(to_tsvector('simple', coalesce("logs"."description"::text, '')), 'A') || setweight(to_tsvector('simple', coalesce(pg_search_65e3f8265dfa723d721ade.pg_search_86a5b22b6348a78c634fcf::text, '')), 'B') || setweight(to_tsvector('simple', coalesce(pg_search_c8df7499cbfe4dcae83666.pg_search_27264e2376734ab2733873::text, '')), 'B')) @@ (to_tsquery('simple', ''' ' || 'test' || ' '''))))
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  invalid reference to FROM-clause entry for table "tasks"
LINE 1: ...ects" "projects_tasks" ON "projects_tasks"."id" = "tasks"."p...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "tasks_logs".
: SELECT SUM("logs"."id") FROM "logs" LEFT OUTER JOIN "tasks" "tasks_logs" ON "tasks_logs"."id" = "logs"."task_id" LEFT OUTER JOIN "projects" "projects_tasks" ON "projects_tasks"."id" = "tasks"."project_id" LEFT OUTER JOIN (SELECT "logs"."id" AS id, string_agg("projects"."name"::text, ' ') AS pg_search_86a5b22b6348a78c634fcf FROM "logs" INNER JOIN "projects" ON "projects"."id" = "logs"."project_id" GROUP BY "logs"."id") pg_search_65e3f8265dfa723d721ade ON pg_search_65e3f8265dfa723d721ade.id = "logs"."id" LEFT OUTER JOIN (SELECT "logs"."id" AS id, string_agg("tasks"."name"::text, ' ') AS pg_search_27264e2376734ab2733873 FROM "logs" INNER JOIN "tasks" ON "tasks"."id" = "logs"."task_id" GROUP BY "logs"."id") pg_search_c8df7499cbfe4dcae83666 ON pg_search_c8df7499cbfe4dcae83666.id = "logs"."id" WHERE (((setweight(to_tsvector('simple', coalesce("logs"."description"::text, '')), 'A') || setweight(to_tsvector('simple', coalesce(pg_search_65e3f8265dfa723d721ade.pg_search_86a5b22b6348a78c634fcf::text, '')), 'B') || setweight(to_tsvector('simple', coalesce(pg_search_c8df7499cbfe4dcae83666.pg_search_27264e2376734ab2733873::text, '')), 'B')) @@ (to_tsquery('simple', ''' ' || 'test' || ' '''))))

You may wonder why I set includes for a sum query. The reason is that I use the same relation for displaying the items, and I want to show a summarize with statistics below the results. Of course I could build the relation again without the includes, but I prefer to not do this.

I hope this could be easily fixed in pg_search.

Ps. I am running Rails 4.2.0, ruby 2.2.0, pg 0.17.1 and pg_search 0.7.8 (I also tried the master branch).

[doits]

I have a simpler test case I think and steps to reproduce locally. I get a similar error when using a pg_search_scope associated_against: in conjunction with a regular scope. Test files are here: https://gist.github.com/doits/df3d61e1eca1817b095a

Simply create a database called test (createdb test), drop Gemfile and test.rb into a folder, run bundle install and ruby test.rb.

When you just run one of each scope separately it works (first two assertions), but combined it throws an error (third assertion) where the table reference went wrong.

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  invalid reference to FROM-clause entry for table "houses"
LINE 1: ...h ON "people"."id" = pg_search.pg_search_id WHERE "houses"."...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "houses_people".

Is the same problem or is it a different one?

[kreintjes]

Your test crashes on the last assertion for me as well. It indeed seems to be the same problem as I have, but I am not absolutely sure.

[doits]

Yeah, that's what I wanted to show and to reproduce (for the developer of this gem, so he has a test case). It looks like the same error you got.

[doits]

I think the error is because of the table aliasing in rails on multiple joins. Maybe worth to take a look at.

[zdraganov]

+1

[doits]

I have a second (little bit simpler) test case where the same error is triggered even without an associated_against pg_scope but a regular. In this case it even depends on the order of chaining the scopes (one order works, second triggers the error).

https://gist.github.com/doits/215b3b54496ea47254b8

[justincampbell]

I believe I'm having a similar issue in #246

[DmitryKK]

I have a similar problem. includes and eager_load does not work with search. joins method works, but I need eager loading! What to do?

[Jaco-Pretorius]

+1 Getting the same issue. I don't understand why the associated table is aliased in this way - where does the code for this live?

[kreintjes]

It seems @DmitryKK is right. The problem already occurs when simply using includes or eager_loads. The pg_search_scope definition does not even have to use the option associated_against for this problem to occur. See the updated issue https://github.com/Casecommons/pg_search/issues/330.

[kreintjes]

@DmitryKK it might be possible to rewrite your query to two queries to work around this problem. See the workaround at https://github.com/Casecommons/pg_search/issues/330.

[acdesouza]

Isn't it related with #88?