search

Casecommons / pg_search

pg_search builds ActiveRecord named scopes that take advantage of PostgreSQL’s full text search
http://www.casebook.net
MIT License
1.32k stars 372 forks source link

pg_search_scope error with associations and scopes #88

Open danieldocki opened 11 years ago

danieldocki commented 11 years ago 
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 commented 11 years ago

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 commented 11 years ago

@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 commented 11 years ago

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 commented 11 years ago

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 commented 11 years ago

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 commented 11 years ago

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 commented 11 years ago

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

danieldocki commented 11 years ago

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 commented 11 years ago

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 commented 11 years ago

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 commented 11 years ago

@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 commented 11 years ago

@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 commented 11 years ago

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 commented 11 years ago

@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 commented 11 years ago

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

nertzy commented 11 years ago

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 commented 11 years ago

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 commented 9 years ago

@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 commented 9 years ago

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?

etehtsea commented 9 years ago

@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 commented 9 years ago

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

MatthewRDodds commented 9 years ago

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 commented 8 years ago

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):

elquimista commented 8 years ago

@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. :+1:

colszowka commented 5 years ago

Hey all, just want to leave a note here because I was struggling with a similar issue, trying to order single-table pg_search results by an included association record's attribute for https://github.com/rubytoolbox/rubytoolbox/pull/360. The hint by @marksim regarding the rails query resolving led me on the right track:

Just wanted to leave this here in case anyone stumbles into a similar issue in the future :)

seeurchin commented 5 years ago

Inspired by @marksim idea, I am just thinking about aliasing table name in relation method so the alias joins from active_record would work. Something like this

      def selects_for_singular_association
        columns.map do |column|
          "#{column.full_name("r01")}::text AS #{column.alias}"
        end.join(", ")
      end

      def selects_for_multiple_association
        columns.map do |column|
          "string_agg(#{column.full_name("r01")}::text, ' ') AS #{column.alias}"
        end.join(", ")
      end

      def relation(primary_key)
        result = @model.unscoped.joins("INNER JOIN #{table_name} AS r01 #{on_join_query}").select("#{primary_key} AS id, #{selects}")
        result = result.group(primary_key) unless singular_association?
        result
      end

      def on_join_query
        foreign_key = @model.reflect_on_association(@name).foreign_key
        if contain_foreign_key?
          "ON r01.id = #{@model.table_name}.#{foreign_key}"
        else
          "ON r01.#{foreign_key} = #{@model.table_name}.id"
        end
      end

      def contain_foreign_key?
        %i[belongs_to].include? @model.reflect_on_association(@name).macro 
      end

Then we need to randomize the alias name instead of hard coded "r01". A bit heavy though.