pg_search_scope error with associations and scopes #88

Open
danieldocki opened this Issue Feb 14, 2013 · 24 comments

Projects

None yet

10 participants

@danieldocki
class Establishment < ActiveRecord::Base
  include PgSearch
  pg_search_scope :search,
      against: [:name, :street, :neighborhood, :screen_name],
      associated_against: { profile: :description, kind_of_foods: [:name, :food] },
      using: { tsearch: {prefix: true} },
      ignoring: :accents
  has_and_belongs_to_many :kind_of_foods
  has_one :profile

  default_scope order: 'unaccent(establishments.name) ASC'

  def self.enabled
    includes(:profile).where("profiles.enable IS true")
  end

  def self.opened
    includes(:hours_of_operations)
    .where("hours_of_operations.week_day = ?", Time.now.wday)
    .where("first_closed = ? OR second_closed = ?", false, false)
  end

  def self.closed
    includes(:hours_of_operations)
    .where("hours_of_operations.week_day = ?", Time.now.wday)
    .where("first_closed = ? AND second_closed = ?", true, true)
  end
end
class EstablishmentsController < ApplicationController

  def index
    @establishments = @city.establishments.includes(:profile, :hours_of_operations).enabled
    @establishments = @establishments.search(params[:search])  if params[:search].present?
  end
PG::Error: ERROR:  invalid reference to FROM-clause entry for table "profiles"
LINE 1: ..., "establishments"."accepted_contract" AS t0_r19, "profiles"...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "profiles_establishments".
: SELECT "establishments"."id" AS t0_r0, "establishments"."name" AS t0_r1, "establishments"."company" AS t0_r2, "establishments"."cnpj" AS t0_r3, "establishments"."phone" AS t0_r4, "establishments"."screen_name" AS t0_r5, "establishments"."neighborhood" AS t0_r6, "establishments"."street" AS t0_r7, "establishments"."number" AS t0_r8, "establishments"."complement" AS t0_r9, "establishments"."postal_code" AS t0_r10, "establishments"."state_id" AS t0_r11, "establishments"."city_id" AS t0_r12, "establishments"."manager_id" AS t0_r13, "establishments"."created_at" AS t0_r14, "establishments"."updated_at" AS t0_r15, "establishments"."pin" AS t0_r16, "establishments"."latlon" AS t0_r17, "establishments"."email" AS t0_r18, "establishments"."accepted_contract" AS t0_r19, "profiles"."id" AS t1_r0, "profiles"."description" AS t1_r1, "profiles"."enable" AS t1_r2, "profiles"."minimum_order" AS t1_r3, "profiles"."created_at" AS t1_r4, "profiles"."updated_at" AS t1_r5, "profiles"."establishment_id" AS t1_r6, "profiles"."logo" AS t1_r7, "profiles"."delivery_time" AS t1_r8, "hours_of_operations"."id" AS t2_r0, "hours_of_operations"."week_day" AS t2_r1, "hours_of_operations"."first_open_time" AS t2_r2, "hours_of_operations"."first_close_time" AS t2_r3, "hours_of_operations"."second_open_time" AS t2_r4, "hours_of_operations"."second_close_time" AS t2_r5, "hours_of_operations"."second_closed" AS t2_r6, "hours_of_operations"."created_at" AS t2_r7, "hours_of_operations"."updated_at" AS t2_r8, "hours_of_operations"."establishment_id" AS t2_r9, "hours_of_operations"."first_closed" AS t2_r10 FROM "establishments" LEFT OUTER JOIN "profiles" "profiles_establishments" ON "profiles_establishments"."establishment_id" = "establishments"."id" LEFT OUTER JOIN "hours_of_operations" ON "hours_of_operations"."establishment_id" = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("profiles"."description"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM "establishments" INNER JOIN "profiles" ON "profiles"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("kind_of_foods"."name"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a, string_agg("kind_of_foods"."food"::text, ' ') AS pg_search_828eb2447a3c62a85b0626 FROM "establishments" INNER JOIN "establishments_kind_of_foods" ON "establishments_kind_of_foods"."establishment_id" = "establishments"."id" INNER JOIN "kind_of_foods" ON "kind_of_foods"."id" = "establishments_kind_of_foods"."kind_of_food_id" GROUP BY "establishments"."id") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = "establishments"."id" WHERE "establishments"."city_id" = 4309 AND (profiles.enable IS true) AND (((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."screen_name"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_828eb2447a3c62a85b0626::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')))) AND (hours_of_operations.week_day = 4) AND (first_closed = 'f' OR second_closed = 'f') ORDER BY unaccent(establishments.name) ASC, pg_search_rank DESC, "establishments"."id" ASC
  Rendered establishments/_content.html.erb (8.5ms)
  Rendered establishments/index.html.erb within layouts/application (18.8ms)
Completed 500 Internal Server Error in 89ms

ActionView::Template::Error (PG::Error: ERROR:  invalid reference to FROM-clause entry for table "profiles"
LINE 1: ..., "establishments"."accepted_contract" AS t0_r19, "profiles"...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "profiles_establishments".
: SELECT "establishments"."id" AS t0_r0, "establishments"."name" AS t0_r1, "establishments"."company" AS t0_r2, "establishments"."cnpj" AS t0_r3, "establishments"."phone" AS t0_r4, "establishments"."screen_name" AS t0_r5, "establishments"."neighborhood" AS t0_r6, "establishments"."street" AS t0_r7, "establishments"."number" AS t0_r8, "establishments"."complement" AS t0_r9, "establishments"."postal_code" AS t0_r10, "establishments"."state_id" AS t0_r11, "establishments"."city_id" AS t0_r12, "establishments"."manager_id" AS t0_r13, "establishments"."created_at" AS t0_r14, "establishments"."updated_at" AS t0_r15, "establishments"."pin" AS t0_r16, "establishments"."latlon" AS t0_r17, "establishments"."email" AS t0_r18, "establishments"."accepted_contract" AS t0_r19, "profiles"."id" AS t1_r0, "profiles"."description" AS t1_r1, "profiles"."enable" AS t1_r2, "profiles"."minimum_order" AS t1_r3, "profiles"."created_at" AS t1_r4, "profiles"."updated_at" AS t1_r5, "profiles"."establishment_id" AS t1_r6, "profiles"."logo" AS t1_r7, "profiles"."delivery_time" AS t1_r8, "hours_of_operations"."id" AS t2_r0, "hours_of_operations"."week_day" AS t2_r1, "hours_of_operations"."first_open_time" AS t2_r2, "hours_of_operations"."first_close_time" AS t2_r3, "hours_of_operations"."second_open_time" AS t2_r4, "hours_of_operations"."second_close_time" AS t2_r5, "hours_of_operations"."second_closed" AS t2_r6, "hours_of_operations"."created_at" AS t2_r7, "hours_of_operations"."updated_at" AS t2_r8, "hours_of_operations"."establishment_id" AS t2_r9, "hours_of_operations"."first_closed" AS t2_r10 FROM "establishments" LEFT OUTER JOIN "profiles" "profiles_establishments" ON "profiles_establishments"."establishment_id" = "establishments"."id" LEFT OUTER JOIN "hours_of_operations" ON "hours_of_operations"."establishment_id" = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("profiles"."description"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM "establishments" INNER JOIN "profiles" ON "profiles"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("kind_of_foods"."name"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a, string_agg("kind_of_foods"."food"::text, ' ') AS pg_search_828eb2447a3c62a85b0626 FROM "establishments" INNER JOIN "establishments_kind_of_foods" ON "establishments_kind_of_foods"."establishment_id" = "establishments"."id" INNER JOIN "kind_of_foods" ON "kind_of_foods"."id" = "establishments_kind_of_foods"."kind_of_food_id" GROUP BY "establishments"."id") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = "establishments"."id" WHERE "establishments"."city_id" = 4309 AND (profiles.enable IS true) AND (((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."screen_name"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_828eb2447a3c62a85b0626::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')))) AND (hours_of_operations.week_day = 4) AND (first_closed = 'f' OR second_closed = 'f') ORDER BY unaccent(establishments.name) ASC, pg_search_rank DESC, "establishments"."id" ASC):
    1: <div class="establishments">
    2:   <!-- Restaurants Opened -->
    3:   <% @establishments.opened.each do |establishment| %>
    4:     <%= content_tag(:div, class: "restaurant", id: "establishment_#{establishment.id}") do %>
    5:     <div class="logo"><%= link_to establishment.profile.logo? ? image_tag(establishment.profile.logo, alt: establishment.name) : image_tag('gruboo-default.png', alt: 'Gruboo Default'), city_restaurant_path(@city, establishment) %></div>
    6:       <div class="establishment_content">
  app/views/establishments/_content.html.erb:3:in `_app_views_establishments__content_html_erb___690055410653268399_70155156458680'
  app/views/establishments/index.html.erb:6:in `_app_views_establishments_index_html_erb___452280125446139122_70155156525760'
  app/controllers/establishments_controller.rb:18:in `index'


  Rendered /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/actionpack-3.2.11/lib/action_dispatch/middleware/templates/rescues/_trace.erb (1.5ms)
  Rendered /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/actionpack-3.2.11/lib/action_dispatch/middleware/templates/rescues/_request_and_response.erb (1.3ms)
  Rendered /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/actionpack-3.2.11/lib/action_dispatch/middleware/templates/rescues/template_error.erb within rescues/layout (9.2ms)

But if I use unscoped, works correctly, but I need the enabled, closed and opened

@establishments = @establishments.unscoped.search(params[:search])
@nertzy
Collaborator
nertzy commented Feb 14, 2013

Thanks for the bug report!

There are a lot of moving parts here. Are you able to reproduce the problem using a smaller example? Try removing some of the scopes until you find the minimum required to reproduce the issue.

@danieldocki

@nertzy ok

I removed opened and closed, was only enabled

controller

class EstablishmentsController < ApplicationController
  def index
    @establishments = Establishment.enabled
    @establishments = @establishments.search(params[:search]) if params[:search].present?
  end
end

error

Started GET "/santamaria-rs/restaurantes?utf8=%E2%9C%93&search=pizza&commit=Buscar" for 127.0.0.1 at 2013-02-14 17:48:03 -0200
Processing by EstablishmentsController#index as HTML
  Parameters: {"utf8"=>"✓", "search"=>"pizza", "commit"=>"Buscar", "locale"=>"pt-BR", "city_id"=>"santamaria-rs"}
  Establishment Load (34.7ms)  SELECT "establishments".*, (ts_rank((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."screen_name"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_828eb2447a3c62a85b0626::text, '')))), (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')), 0)) AS pg_search_rank FROM "establishments" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("profiles"."description"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM "establishments" INNER JOIN "profiles" ON "profiles"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("kind_of_foods"."name"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a, string_agg("kind_of_foods"."food"::text, ' ') AS pg_search_828eb2447a3c62a85b0626 FROM "establishments" INNER JOIN "establishments_kind_of_foods" ON "establishments_kind_of_foods"."establishment_id" = "establishments"."id" INNER JOIN "kind_of_foods" ON "kind_of_foods"."id" = "establishments_kind_of_foods"."kind_of_food_id" GROUP BY "establishments"."id") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = "establishments"."id" WHERE (profiles.enable = 't') AND (((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."screen_name"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_828eb2447a3c62a85b0626::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')))) ORDER BY unaccent(establishments.name) ASC, pg_search_rank DESC, "establishments"."id" ASC
PG::Error: ERROR:  missing FROM-clause entry for table "profiles"
LINE 1: ...7a020be338973bb.id = "establishments"."id" WHERE (profiles.e...
                                                             ^
: SELECT "establishments".*, (ts_rank((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."screen_name"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_828eb2447a3c62a85b0626::text, '')))), (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')), 0)) AS pg_search_rank FROM "establishments" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("profiles"."description"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM "establishments" INNER JOIN "profiles" ON "profiles"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("kind_of_foods"."name"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a, string_agg("kind_of_foods"."food"::text, ' ') AS pg_search_828eb2447a3c62a85b0626 FROM "establishments" INNER JOIN "establishments_kind_of_foods" ON "establishments_kind_of_foods"."establishment_id" = "establishments"."id" INNER JOIN "kind_of_foods" ON "kind_of_foods"."id" = "establishments_kind_of_foods"."kind_of_food_id" GROUP BY "establishments"."id") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = "establishments"."id" WHERE (profiles.enable = 't') AND (((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."screen_name"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_828eb2447a3c62a85b0626::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')))) ORDER BY unaccent(establishments.name) ASC, pg_search_rank DESC, "establishments"."id" ASC
  Rendered establishments/_content.html.erb (38.4ms)
  Rendered establishments/index.html.erb within layouts/application (39.0ms)
Completed 500 Internal Server Error in 44ms

ActionView::Template::Error (PG::Error: ERROR:  missing FROM-clause entry for table "profiles"
LINE 1: ...7a020be338973bb.id = "establishments"."id" WHERE (profiles.e...
                                                             ^
: SELECT "establishments".*, (ts_rank((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."screen_name"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_828eb2447a3c62a85b0626::text, '')))), (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')), 0)) AS pg_search_rank FROM "establishments" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("profiles"."description"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM "establishments" INNER JOIN "profiles" ON "profiles"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("kind_of_foods"."name"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a, string_agg("kind_of_foods"."food"::text, ' ') AS pg_search_828eb2447a3c62a85b0626 FROM "establishments" INNER JOIN "establishments_kind_of_foods" ON "establishments_kind_of_foods"."establishment_id" = "establishments"."id" INNER JOIN "kind_of_foods" ON "kind_of_foods"."id" = "establishments_kind_of_foods"."kind_of_food_id" GROUP BY "establishments"."id") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = "establishments"."id" WHERE (profiles.enable = 't') AND (((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."screen_name"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_828eb2447a3c62a85b0626::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')))) ORDER BY unaccent(establishments.name) ASC, pg_search_rank DESC, "establishments"."id" ASC):
    1: <div class="establishments">
    2:   <% @establishments.each do |establishment| %>
    3:     <%= content_tag(:div, class: "restaurant", id: "establishment_#{establishment.id}") do %>
    4:       <%= establishment.name %>
    5:     <% end %>
  app/views/establishments/_content.html.erb:2:in `_app_views_establishments__content_html_erb___2447623031377564242_70331384801220'
  app/views/establishments/index.html.erb:4:in `_app_views_establishments_index_html_erb___2715535831253758918_70331432808300'


  Rendered /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/actionpack-3.2.11/lib/action_dispatch/middleware/templates/rescues/_trace.erb (1.0ms)
  Rendered /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/actionpack-3.2.11/lib/action_dispatch/middleware/templates/rescues/_request_and_response.erb (0.8ms)
  Rendered /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/actionpack-3.2.11/lib/action_dispatch/middleware/templates/rescues/template_error.erb within rescues/layout (6.9ms)

view

<div class="establishments">
  <% @establishments.each do |establishment| %>
    <%= content_tag(:div, class: "restaurant", id: "establishment_#{establishment.id}") do %>
      <%= establishment.name %>
    <% end %>
  <% end %>
</div>
@counterbeing

I'm running into the same issue within my application. I have a default scope, and another scope I try to apply after the pg_search. I just set up a new rails application to see if I could reproduce the issue... And in my case, I have. The issue seems to have something to do with scopes that have joins... At least for me...

Here's how I got it to fail for me. Take a look at app/models/child.rb. There you'll see a default scope that references the parents association for ordering the children... It makes it blow up:

I'm showing the error there as well... Feel free to clone it and try for yourself. Also, please feel free to tell me I'm just doing it wrong :) Thanks for everything!

https://github.com/counterbeing/pg_search_example

@nertzy
Collaborator
nertzy commented Mar 9, 2013

Thanks for the example, it made it very easy to reproduce the error! I didn't even have to create any records, because the SQL query is just plain invalid. I'm looking into it now.

@nertzy nertzy added a commit that referenced this issue Mar 9, 2013
@nertzy nertzy Add failing spec for Github issue #88 319a3df
@nertzy
Collaborator
nertzy commented Mar 9, 2013

It looks to be a pretty hairy bug to fix. For some reason, the join seems to be getting injected into the pg_search subselect that is used to grab all the text of the other table so that it can be used in the where clause.

I'm partway into a project to convert all the SQL generation code to use Arel and be more symbolic. I'm hoping that once that is done it will be easier to dig into what is going on here.

@nertzy
Collaborator
nertzy commented Mar 9, 2013

Also, as you probably saw above, I created a new branch in the project called issue-88 that has a failing spec that reproduces the issue.

@counterbeing

Thanks Grant! I really appreciate you looking into it. The SQL that is produced is beyond my comprehension. Really glad I can help!

@danieldocki

For now no solution?

Me too, "The SQL that is produced is beyond my comprehension" =/

SQL

SELECT "establishments".*, (ts_rank((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))), (to_tsquery('simple', ''' ' || unaccent('xis') || ' ''' || ':*')), 0)) AS pg_search_rank FROM "establishments" INNER JOIN "profiles" "profiles_establishments" ON "profiles_establishments"."establishment_id" = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("profiles"."description"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM "establishments" INNER JOIN "profiles" ON "profiles"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("kind_of_foods"."name"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a FROM "establishments" INNER JOIN "establishments_kind_of_foods" ON "establishments_kind_of_foods"."establishment_id" = "establishments"."id" INNER JOIN "kind_of_foods" ON "kind_of_foods"."id" = "establishments_kind_of_foods"."kind_of_food_id" GROUP BY "establishments"."id") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("products"."name"::text, ' ') AS pg_search_7f5d6448c03ef7607cc6b4, string_agg("products"."description"::text, ' ') AS pg_search_703389b4628de4cc08c24e FROM "establishments" INNER JOIN "products" ON "products"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_9f5218ccd209cdb7a0dc38 ON pg_search_9f5218ccd209cdb7a0dc38.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("categories"."name"::text, ' ') AS pg_search_e9bf15bae1a2bbc36b9125 FROM "establishments" INNER JOIN "categories" ON "categories"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_71b84947f0ea36f75e4402 ON pg_search_71b84947f0ea36f75e4402.id = "establishments"."id" WHERE "profiles"."enable" = 't' AND (((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('xis') || ' ''' || ':*')))) ORDER BY pg_search_rank DESC, "establishments"."id" ASC
@marksim
marksim commented May 4, 2013

Are these two errors in fact the same? One points to the "wrong" alias (suggesting profile_establishments) and one points to a missing alias altogether. Hrm.

@marksim
marksim commented May 5, 2013

Workaround

Specify the join alias in the other queries. It appears that in the "enabled" query, Rails/ActiveRelation picks a different table alias if the table is already joined, making the "where" clause invalid in them, since they reference "profiles" explicitly.

The workaround is this:

scope :enabled,
  joins("INNER JOIN profiles as p01 ON p01.id = establishments.profile_id").
  where("po1.enabled IS true")

I'm not a big fan of it, but it will work right in combination with pg_search and any other scope.

@counterbeing

@marksim , Thanks a ton for looking into it. The project that I was working on at the time is currently resting for the moment. I'm hoping that one of these days the issue will just be resolved. But it is a project I will be getting back to, and I will try implementing the workaround if need be. Anyway, your time and expertise, are super appreciated. Thanks!

@danieldocki

@marksim

I tried, but not worked.

Model Establishment.rb

class Establishment < ActiveRecord::Base
  include PgSearch
  pg_search_scope :search,
    against: [:name, :street, :neighborhood],
    associated_against: { profile: :description, kind_of_foods: :name, products: [:name, :description], categories: :name },
    using: { tsearch: {prefix: true, any_word: true} },
    ignoring: :accents

  has_and_belongs_to_many :kind_of_foods
  has_many :products
  has_many :categories
  has_one :profile

  scope :enabled,
    joins("INNER JOIN profiles as p01 ON p01.id = establishments.profile_id").
    where("po1.enabled IS true")
end

SQL:

irb(main):026:0> Establishment.enabled.search("pizza").to_sql
=> "SELECT \"establishments\".*, ((ts_rank((to_tsvector('simple', unaccent(coalesce(\"establishments\".\"name\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(\"establishments\".\"street\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(\"establishments\".\"neighborhood\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))), (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')), 0))) AS pg_search_rank FROM \"establishments\" INNER JOIN profiles as p01 ON p01.id = establishments.profile_id LEFT OUTER JOIN (SELECT \"establishments\".\"id\" AS id, string_agg(\"profiles\".\"description\"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM \"establishments\" INNER JOIN \"profiles\" ON \"profiles\".\"establishment_id\" = \"establishments\".\"id\" GROUP BY \"establishments\".\"id\") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = \"establishments\".\"id\" LEFT OUTER JOIN (SELECT \"establishments\".\"id\" AS id, string_agg(\"kind_of_foods\".\"name\"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a FROM \"establishments\" INNER JOIN \"establishments_kind_of_foods\" ON \"establishments_kind_of_foods\".\"establishment_id\" = \"establishments\".\"id\" INNER JOIN \"kind_of_foods\" ON \"kind_of_foods\".\"id\" = \"establishments_kind_of_foods\".\"kind_of_food_id\" GROUP BY \"establishments\".\"id\") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = \"establishments\".\"id\" LEFT OUTER JOIN (SELECT \"establishments\".\"id\" AS id, string_agg(\"products\".\"name\"::text, ' ') AS pg_search_7f5d6448c03ef7607cc6b4, string_agg(\"products\".\"description\"::text, ' ') AS pg_search_703389b4628de4cc08c24e FROM \"establishments\" INNER JOIN \"products\" ON \"products\".\"establishment_id\" = \"establishments\".\"id\" GROUP BY \"establishments\".\"id\") pg_search_9f5218ccd209cdb7a0dc38 ON pg_search_9f5218ccd209cdb7a0dc38.id = \"establishments\".\"id\" LEFT OUTER JOIN (SELECT \"establishments\".\"id\" AS id, string_agg(\"categories\".\"name\"::text, ' ') AS pg_search_e9bf15bae1a2bbc36b9125 FROM \"establishments\" INNER JOIN \"categories\" ON \"categories\".\"establishment_id\" = \"establishments\".\"id\" GROUP BY \"establishments\".\"id\") pg_search_71b84947f0ea36f75e4402 ON pg_search_71b84947f0ea36f75e4402.id = \"establishments\".\"id\" WHERE (po1.enabled IS true) AND (((to_tsvector('simple', unaccent(coalesce(\"establishments\".\"name\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(\"establishments\".\"street\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(\"establishments\".\"neighborhood\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')))) ORDER BY pg_search_rank DESC, \"establishments\".\"id\" ASC"
irb(main):027:0> 

Error:

irb(main):023:0> Establishment.enabled.search("pizza")
  Establishment Load (1.4ms)  SELECT "establishments".*, ((ts_rank((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))), (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')), 0))) AS pg_search_rank FROM "establishments" INNER JOIN profiles as p01 ON p01.id = establishments.profile_id LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("profiles"."description"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM "establishments" INNER JOIN "profiles" ON "profiles"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("kind_of_foods"."name"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a FROM "establishments" INNER JOIN "establishments_kind_of_foods" ON "establishments_kind_of_foods"."establishment_id" = "establishments"."id" INNER JOIN "kind_of_foods" ON "kind_of_foods"."id" = "establishments_kind_of_foods"."kind_of_food_id" GROUP BY "establishments"."id") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("products"."name"::text, ' ') AS pg_search_7f5d6448c03ef7607cc6b4, string_agg("products"."description"::text, ' ') AS pg_search_703389b4628de4cc08c24e FROM "establishments" INNER JOIN "products" ON "products"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_9f5218ccd209cdb7a0dc38 ON pg_search_9f5218ccd209cdb7a0dc38.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("categories"."name"::text, ' ') AS pg_search_e9bf15bae1a2bbc36b9125 FROM "establishments" INNER JOIN "categories" ON "categories"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_71b84947f0ea36f75e4402 ON pg_search_71b84947f0ea36f75e4402.id = "establishments"."id" WHERE (po1.enabled IS true) AND (((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')))) ORDER BY pg_search_rank DESC, "establishments"."id" ASC
ActiveRecord::StatementInvalid: PG::Error: ERROR:  column establishments.profile_id does not exist
LINE 1: ...lishments" INNER JOIN profiles as p01 ON p01.id = establishm...
                                                             ^
: SELECT "establishments".*, ((ts_rank((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))), (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')), 0))) AS pg_search_rank FROM "establishments" INNER JOIN profiles as p01 ON p01.id = establishments.profile_id LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("profiles"."description"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM "establishments" INNER JOIN "profiles" ON "profiles"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("kind_of_foods"."name"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a FROM "establishments" INNER JOIN "establishments_kind_of_foods" ON "establishments_kind_of_foods"."establishment_id" = "establishments"."id" INNER JOIN "kind_of_foods" ON "kind_of_foods"."id" = "establishments_kind_of_foods"."kind_of_food_id" GROUP BY "establishments"."id") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("products"."name"::text, ' ') AS pg_search_7f5d6448c03ef7607cc6b4, string_agg("products"."description"::text, ' ') AS pg_search_703389b4628de4cc08c24e FROM "establishments" INNER JOIN "products" ON "products"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_9f5218ccd209cdb7a0dc38 ON pg_search_9f5218ccd209cdb7a0dc38.id = "establishments"."id" LEFT OUTER JOIN (SELECT "establishments"."id" AS id, string_agg("categories"."name"::text, ' ') AS pg_search_e9bf15bae1a2bbc36b9125 FROM "establishments" INNER JOIN "categories" ON "categories"."establishment_id" = "establishments"."id" GROUP BY "establishments"."id") pg_search_71b84947f0ea36f75e4402 ON pg_search_71b84947f0ea36f75e4402.id = "establishments"."id" WHERE (po1.enabled IS true) AND (((to_tsvector('simple', unaccent(coalesce("establishments"."name"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."street"::text, ''))) || to_tsvector('simple', unaccent(coalesce("establishments"."neighborhood"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')))) ORDER BY pg_search_rank DESC, "establishments"."id" ASC
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1161:in `exec'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1161:in `exec_no_cache'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:660:in `block in exec_query'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activesupport-3.2.13/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:659:in `exec_query'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1256:in `select'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/querying.rb:38:in `block in find_by_sql'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/explain.rb:41:in `logging_query_plan'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/querying.rb:37:in `find_by_sql'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/relation.rb:171:in `exec_queries'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/relation.rb:160:in `block in to_a'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/explain.rb:34:in `logging_query_plan'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/relation.rb:159:in `to_a'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/bullet-4.5.0/lib/bullet/active_record3x.rb:10:in `to_a'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/relation.rb:498:in `inspect'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/railties-3.2.13/lib/rails/commands/console.rb:47:in `start'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/railties-3.2.13/lib/rails/commands/console.rb:8:in `start'
    from /Users/danieldocki/.rbenv/versions/1.9.3-p327/lib/ruby/gems/1.9.1/gems/railties-3.2.13/lib/rails/commands.rb:41:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'irb(main):024:0> 
@marksim
marksim commented May 6, 2013

This is because establishment is a has_one, not belongs_to relationship to profile. It should be

scope :enabled,
    joins("INNER JOIN profiles as p01 ON p01.establishment_id = establishments.id").
    where("po1.enabled IS true")
@danieldocki

@marksim Working :), Thanks.

  scope :enabled,
    joins("INNER JOIN profiles as p01 ON p01.establishment_id = establishments.id").
    where("p01.enable IS true")

SQL

=> "SELECT \"establishments\".*, ((ts_rank((to_tsvector('simple', unaccent(coalesce(\"establishments\".\"name\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(\"establishments\".\"street\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(\"establishments\".\"neighborhood\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))), (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')), 0))) AS pg_search_rank FROM \"establishments\" INNER JOIN profiles as p01 ON p01.establishment_id = establishments.id LEFT OUTER JOIN (SELECT \"establishments\".\"id\" AS id, string_agg(\"profiles\".\"description\"::text, ' ') AS pg_search_0390bc50ffa570d5c86638 FROM \"establishments\" INNER JOIN \"profiles\" ON \"profiles\".\"establishment_id\" = \"establishments\".\"id\" GROUP BY \"establishments\".\"id\") pg_search_f6af6f731450ca5116c217 ON pg_search_f6af6f731450ca5116c217.id = \"establishments\".\"id\" LEFT OUTER JOIN (SELECT \"establishments\".\"id\" AS id, string_agg(\"kind_of_foods\".\"name\"::text, ' ') AS pg_search_7e789f2f700ef8b1d4a72a FROM \"establishments\" INNER JOIN \"establishments_kind_of_foods\" ON \"establishments_kind_of_foods\".\"establishment_id\" = \"establishments\".\"id\" INNER JOIN \"kind_of_foods\" ON \"kind_of_foods\".\"id\" = \"establishments_kind_of_foods\".\"kind_of_food_id\" GROUP BY \"establishments\".\"id\") pg_search_ce50fd37a020be338973bb ON pg_search_ce50fd37a020be338973bb.id = \"establishments\".\"id\" LEFT OUTER JOIN (SELECT \"establishments\".\"id\" AS id, string_agg(\"products\".\"name\"::text, ' ') AS pg_search_7f5d6448c03ef7607cc6b4, string_agg(\"products\".\"description\"::text, ' ') AS pg_search_703389b4628de4cc08c24e FROM \"establishments\" INNER JOIN \"products\" ON \"products\".\"establishment_id\" = \"establishments\".\"id\" GROUP BY \"establishments\".\"id\") pg_search_9f5218ccd209cdb7a0dc38 ON pg_search_9f5218ccd209cdb7a0dc38.id = \"establishments\".\"id\" LEFT OUTER JOIN (SELECT \"establishments\".\"id\" AS id, string_agg(\"categories\".\"name\"::text, ' ') AS pg_search_e9bf15bae1a2bbc36b9125 FROM \"establishments\" INNER JOIN \"categories\" ON \"categories\".\"establishment_id\" = \"establishments\".\"id\" GROUP BY \"establishments\".\"id\") pg_search_71b84947f0ea36f75e4402 ON pg_search_71b84947f0ea36f75e4402.id = \"establishments\".\"id\" WHERE (p01.enable IS true) AND (((to_tsvector('simple', unaccent(coalesce(\"establishments\".\"name\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(\"establishments\".\"street\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(\"establishments\".\"neighborhood\"::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_f6af6f731450ca5116c217.pg_search_0390bc50ffa570d5c86638::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_ce50fd37a020be338973bb.pg_search_7e789f2f700ef8b1d4a72a::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_7f5d6448c03ef7607cc6b4::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_9f5218ccd209cdb7a0dc38.pg_search_703389b4628de4cc08c24e::text, ''))) || to_tsvector('simple', unaccent(coalesce(pg_search_71b84947f0ea36f75e4402.pg_search_e9bf15bae1a2bbc36b9125::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('pizza') || ' ''' || ':*')))) ORDER BY unaccent(establishments.name) ASC, pg_search_rank DESC, \"establishments\".\"id\" ASC"
@Darkside73

issue-88 branch has no commits since failing spec was added
Any hopes for fixing?

@nertzy nertzy added a commit that referenced this issue Jul 5, 2013
@nertzy nertzy Add failing spec for Github issue #88 b6f51aa
@nertzy
Collaborator
nertzy commented Jul 5, 2013

I haven't had much time to look into this deeper. I just rebased the issue-88 branch on top of the current master to see if the issue is still there with Rails 4. Looks like it's still a problem. I'm going to investigate it for a little while right now and report back with what I find.

@nertzy
Collaborator
nertzy commented Jul 5, 2013

The best that I can figure out right now is that I don't know how to figure out ahead of time what the table name or alias name will be for the :joins call for a has_one association. And since :associated_against introduces a second :joins into the scope, I think Active Record is doing some trickery behind the scenes to try to keep the two names distinct even if they join into the same table.

@phlegx
phlegx commented Dec 13, 2014

@netzy i have the same problem. Can you see please my issue #206. Any idea how can I solve this problem? It is very similar. I have add a simple example.

@nertzy nertzy added a commit that referenced this issue Apr 25, 2015
@nertzy nertzy Add failing spec for Github issue #88 25f8eb8
@nertzy
Collaborator
nertzy commented Apr 25, 2015

I just re-ran my test case against pg_search 1.0.0. It passes against Active Record 4.1 and 4.2, but fails for earlier versions.

Does pg_search 1.0.0 fix the issue for you?

@nertzy nertzy added a commit that referenced this issue May 1, 2015
@nertzy nertzy Add failing spec for Github issue #88 2251211
@nertzy nertzy added a commit that referenced this issue May 1, 2015
@nertzy nertzy Add failing spec for Github issue #88 355b42a
@nertzy nertzy added a commit that referenced this issue May 1, 2015
@nertzy nertzy Add failing spec for Github issue #88 e1d3a7c
@etehtsea

@nertzy this is still the issue.
Your spec don't fail with activerecord 4.2 because .all returns Relation in latest versions. If you replace .all with .to_a you'll see an exception.

@nertzy
Collaborator
nertzy commented Aug 13, 2015

Thanks, I'll update my spec to populate the Relation using #to_a

@nertzy nertzy added a commit that referenced this issue Aug 13, 2015
@nertzy nertzy Add failing spec for Github issue #88 b97c29c
@nertzy nertzy added a commit that referenced this issue Aug 18, 2015
@nertzy nertzy Add failing spec for Github issue #88 540800c
@MatthewRDodds

My pretty horrible work around for this issue is to build a fresh relation by pulling the ids out of the relation returned by the search:

def sanitize_relation(relation_from_search)
  Model.where(id: relation_from_search.pluck('id'))
end

Maybe that helps someone.

@trustarun

Is there a fix for it ??
Facing the same problem .

I have Slide and Library_Slide_Type model

class Slide < ActiveRecord::Base
   include PgSearch

  has_and_belongs_to_many :slide_library_types, join_table: "slide_libraries"

  acts_as_taggable
  acts_as_taggable_on :content_tag

  pg_search_scope :search_by_content , 
                                :against => [:text_content, :title, :notes], 
                                :associated_against => {slide_library_types: :name}

 def self.search(query)
    if query.empty?
      self.all
    else 
      search_by_content(query)
    end
  end

  def self.tagged(tags)
    if tags.blank?
      self.all
    else 
      tagged_with(tags)
    end
  end

  def self.libraries(libs, current_user)
   libs = current_user.slide_library_types.pluck(:id) if libs.blank?
   includes(:slide_library_types)
  .where('slide_library_types.id in (?)', libs).references(:slide_library_types)
  end

end
class SlideLibraryType < ActiveRecord::Base
  has_and_belongs_to_many :slides, join_table: "slide_libraries"
end

Basically a User should search a slide with the tag or library_types or the content.

I have chained the method in controller which look as below

@library_slides =  Slide.libraries(library_types, current_user).tagged(tags).search(search_term)

But when content search is fired by user get the error as above :

SELECT "slide_library_types"."id" FROM "slide_library_types" INNER JOIN "user_libraries" ON "slide_library_types"."id" = "user_libraries"."slide_library_type_id" WHERE "user_libraries"."user_id" = $1 [["user_id", 1]]
SQL (2.1ms) SELECT DISTINCT "slides"."id", pg_search_fd2c8f1dc03ce50ff48749.rank, "slides"."id" AS alias_0 FROM "slides" LEFT OUTER JOIN "slide_libraries" "slides_slide_library_types_slides_join" ON "slides_slide_library_types_slides_join"."slide_id" = "slides"."id" LEFT OUTER JOIN "slide_library_types" "slide_library_types_slides" ON "slide_library_types_slides"."id" = "slides_slide_library_types_slides_join"."slide_library_type_id" INNER JOIN (SELECT "slides"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("slides"."text_content"::text, '')) || to_tsvector('simple', coalesce("slides"."title"::text, '')) || to_tsvector('simple', coalesce("slides"."notes"::text, '')) || to_tsvector('simple', coalesce(pg_search_18f11dc487a1e151dc85dd.pg_search_3f858e8a53fd0c6e6c13e7::text, ''))), (to_tsquery('simple', ''' ' || 'help' || ' ''')), 0)) AS rank FROM "slides" LEFT OUTER JOIN (SELECT "slides"."id" AS id, string_agg("slide_library_types"."name"::text, ' ') AS pg_search_3f858e8a53fd0c6e6c13e7 FROM "slides" INNER JOIN "slide_libraries" ON "slide_libraries"."slide_id" = "slides"."id" INNER JOIN "slide_library_types" ON "slide_library_types"."id" = "slide_libraries"."slide_library_type_id" GROUP BY "slides"."id") pg_search_18f11dc487a1e151dc85dd ON pg_search_18f11dc487a1e151dc85dd.id = "slides"."id" WHERE (((to_tsvector('simple', coalesce("slides"."text_content"::text, '')) || to_tsvector('simple', coalesce("slides"."title"::text, '')) || to_tsvector('simple', coalesce("slides"."notes"::text, '')) || to_tsvector('simple', coalesce(pg_search_18f11dc487a1e151dc85dd.pg_search_3f858e8a53fd0c6e6c13e7::text, ''))) @@ (to_tsquery('simple', ''' ' || 'help' || ' '''))))) AS pg_search_fd2c8f1dc03ce50ff48749 ON "slides"."id" = pg_search_fd2c8f1dc03ce50ff48749.pg_search_id WHERE (slide_library_types.id in (1)) ORDER BY pg_search_fd2c8f1dc03ce50ff48749.rank DESC, "slides"."id" ASC LIMIT 12 OFFSET 0

PG::UndefinedTable: ERROR: invalid reference to FROM-clause entry for table "slide_library_types"
LINE 1: ...search_fd2c8f1dc03ce50ff48749.pg_search_id WHERE (slide_libr...
^
HINT: Perhaps you meant to reference the table alias "slide_library_types_slides".
: SELECT DISTINCT "slides"."id", pg_search_fd2c8f1dc03ce50ff48749.rank, "slides"."id" AS alias_0 FROM "slides" LEFT OUTER JOIN "slide_libraries" "slides_slide_library_types_slides_join" ON "slides_slide_library_types_slides_join"."slide_id" = "slides"."id" LEFT OUTER JOIN "slide_library_types" "slide_library_types_slides" ON "slide_library_types_slides"."id" = "slides_slide_library_types_slides_join"."slide_library_type_id" INNER JOIN (SELECT "slides"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("slides"."text_content"::text, '')) || to_tsvector('simple', coalesce("slides"."title"::text, '')) || to_tsvector('simple', coalesce("slides"."notes"::text, '')) || to_tsvector('simple', coalesce(pg_search_18f11dc487a1e151dc85dd.pg_search_3f858e8a53fd0c6e6c13e7::text, ''))), (to_tsquery('simple', ''' ' || 'help' || ' ''')), 0)) AS rank FROM "slides" LEFT OUTER JOIN (SELECT "slides"."id" AS id, string_agg("slide_library_types"."name"::text, ' ') AS pg_search_3f858e8a53fd0c6e6c13e7 FROM "slides" INNER JOIN "slide_libraries" ON "slide_libraries"."slide_id" = "slides"."id" INNER JOIN "slide_library_types" ON "slide_library_types"."id" = "slide_libraries"."slide_library_type_id" GROUP BY "slides"."id") pg_search_18f11dc487a1e151dc85dd ON pg_search_18f11dc487a1e151dc85dd.id = "slides"."id" WHERE (((to_tsvector('simple', coalesce("slides"."text_content"::text, '')) || to_tsvector('simple', coalesce("slides"."title"::text, '')) || to_tsvector('simple', coalesce("slides"."notes"::text, '')) || to_tsvector('simple', coalesce(pg_search_18f11dc487a1e151dc85dd.pg_search_3f858e8a53fd0c6e6c13e7::text, ''))) @@ (to_tsquery('simple', ''' ' || 'help' || ' '''))))) AS pg_search_fd2c8f1dc03ce50ff48749 ON "slides"."id" = pg_search_fd2c8f1dc03ce50ff48749.pg_search_id WHERE (slide_library_types.id in (1)) ORDER BY pg_search_fd2c8f1dc03ce50ff48749.rank DESC, "slides"."id" ASC LIMIT 12 OFFSET 0
Completed 500 Internal Server Error in 254ms (ActiveRecord: 21.0ms)
** [Raven] Event not sent due to excluded environment: development

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: invalid reference to FROM-clause entry for table "slide_library_types"
LINE 1: ...search_fd2c8f1dc03ce50ff48749.pg_search_id WHERE (slide_libr...
^
HINT: Perhaps you meant to reference the table alias "slide_library_types_slides".
: SELECT DISTINCT "slides"."id", pg_search_fd2c8f1dc03ce50ff48749.rank, "slides"."id" AS alias_0 FROM "slides" LEFT OUTER JOIN "slide_libraries" "slides_slide_library_types_slides_join" ON "slides_slide_library_types_slides_join"."slide_id" = "slides"."id" LEFT OUTER JOIN "slide_library_types" "slide_library_types_slides" ON "slide_library_types_slides"."id" = "slides_slide_library_types_slides_join"."slide_library_type_id" INNER JOIN (SELECT "slides"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("slides"."text_content"::text, '')) || to_tsvector('simple', coalesce("slides"."title"::text, '')) || to_tsvector('simple', coalesce("slides"."notes"::text, '')) || to_tsvector('simple', coalesce(pg_search_18f11dc487a1e151dc85dd.pg_search_3f858e8a53fd0c6e6c13e7::text, ''))), (to_tsquery('simple', ''' ' || 'help' || ' ''')), 0)) AS rank FROM "slides" LEFT OUTER JOIN (SELECT "slides"."id" AS id, string_agg("slide_library_types"."name"::text, ' ') AS pg_search_3f858e8a53fd0c6e6c13e7 FROM "slides" INNER JOIN "slide_libraries" ON "slide_libraries"."slide_id" = "slides"."id" INNER JOIN "slide_library_types" ON "slide_library_types"."id" = "slide_libraries"."slide_library_type_id" GROUP BY "slides"."id") pg_search_18f11dc487a1e151dc85dd ON pg_search_18f11dc487a1e151dc85dd.id = "slides"."id" WHERE (((to_tsvector('simple', coalesce("slides"."text_content"::text, '')) || to_tsvector('simple', coalesce("slides"."title"::text, '')) || to_tsvector('simple', coalesce("slides"."notes"::text, '')) || to_tsvector('simple', coalesce(pg_search_18f11dc487a1e151dc85dd.pg_search_3f858e8a53fd0c6e6c13e7::text, ''))) @@ (to_tsquery('simple', ''' ' || 'help' || ' '''))))) AS pg_search_fd2c8f1dc03ce50ff48749 ON "slides"."id" = pg_search_fd2c8f1dc03ce50ff48749.pg_search_id WHERE (slide_library_types.id in (1)) ORDER BY pg_search_fd2c8f1dc03ce50ff48749.rank DESC, "slides"."id" ASC LIMIT 12 OFFSET 0):

@clthck
clthck commented May 22, 2016

@marksim thanks for your quick solution. it worked like a charm. I've been really fixing my hair for over an hour for this problem. 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment