{"action":"created","issue":{"url":"https://api.github.com/repos/Casecommons/pg_search/issues/229","labels_url":"https://api.github.com/repos/Casecommons/pg_search/issues/229/labels{/name}","comments_url":"https://api.github.com/repos/Casecommons/pg_search/issues/229/comments","events_url":"https://api.github.com/repos/Casecommons/pg_search/issues/229/events","html_url":"https://github.com/Casecommons/pg_search/issues/229","id":59458349,"number":229,"title":"SQL error when using pg_search_scope associated_against for a calcuation query with includes scope","user":{"login":"kreintjes","id":3602336,"avatar_url":"https://avatars.githubusercontent.com/u/3602336?v=3","gravatar_id":"","url":"https://api.github.com/users/kreintjes","html_url":"https://github.com/kreintjes","followers_url":"https://api.github.com/users/kreintjes/followers","following_url":"https://api.github.com/users/kreintjes/following{/other_user}","gists_url":"https://api.github.com/users/kreintjes/gists{/gist_id}","starred_url":"https://api.github.com/users/kreintjes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kreintjes/subscriptions","organizations_url":"https://api.github.com/users/kreintjes/orgs","repos_url":"https://api.github.com/users/kreintjes/repos","events_url":"https://api.github.com/users/kreintjes/events{/privacy}","received_events_url":"https://api.github.com/users/kreintjes/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":8,"created_at":"2015-03-02T09:42:58Z","updated_at":"2015-05-28T10:20:04Z","closed_at":null,"body":"Hi,\r\n\r\nI 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:\r\n\r\nWe 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:\r\n```\r\npg_search_scope :search, against: { description: 'A' }, associated_against: { project: { name: 'B' }, task: { name: 'B' } }\r\n```\r\n\r\nI have the following ActiveRecord relation:\r\n```\r\n@results = Log.includes(task: :project).search('test')\r\n```\r\n\r\nWhen 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:\r\n```\r\nLog.includes(task: :project).search('test').sum(:id)\r\n   (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' || ' '''))))\r\nPG::UndefinedTable: ERROR:  invalid reference to FROM-clause entry for table \"tasks\"\r\nLINE 1: ...ects\" \"projects_tasks\" ON \"projects_tasks\".\"id\" = \"tasks\".\"p...\r\n                                                             ^\r\nHINT:  Perhaps you meant to reference the table alias \"tasks_logs\".\r\n: 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' || ' '''))))\r\nActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  invalid reference to FROM-clause entry for table \"tasks\"\r\nLINE 1: ...ects\" \"projects_tasks\" ON \"projects_tasks\".\"id\" = \"tasks\".\"p...\r\n                                                             ^\r\nHINT:  Perhaps you meant to reference the table alias \"tasks_logs\".\r\n: 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' || ' '''))))\r\n```\r\n\r\nYou 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.\r\n\r\nI hope this could be easily fixed in pg_search.\r\n\r\nPs. 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)."},"comment":{"url":"https://api.github.com/repos/Casecommons/pg_search/issues/comments/106262713","html_url":"https://github.com/Casecommons/pg_search/issues/229#issuecomment-106262713","issue_url":"https://api.github.com/repos/Casecommons/pg_search/issues/229","id":106262713,"user":{"login":"DmitryKK","id":7626966,"avatar_url":"https://avatars.githubusercontent.com/u/7626966?v=3","gravatar_id":"","url":"https://api.github.com/users/DmitryKK","html_url":"https://github.com/DmitryKK","followers_url":"https://api.github.com/users/DmitryKK/followers","following_url":"https://api.github.com/users/DmitryKK/following{/other_user}","gists_url":"https://api.github.com/users/DmitryKK/gists{/gist_id}","starred_url":"https://api.github.com/users/DmitryKK/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DmitryKK/subscriptions","organizations_url":"https://api.github.com/users/DmitryKK/orgs","repos_url":"https://api.github.com/users/DmitryKK/repos","events_url":"https://api.github.com/users/DmitryKK/events{/privacy}","received_events_url":"https://api.github.com/users/DmitryKK/received_events","type":"User","site_admin":false},"created_at":"2015-05-28T10:20:04Z","updated_at":"2015-05-28T10:20:04Z","body":"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?"}}