pg_search: pg_search_scope error with associations and scopes

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

About this issue

  • Original URL
  • State: open
  • Created 11 years ago
  • Comments: 26

Commits related to this issue

Most upvoted comments

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.

@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. 👍