Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

pg_search using associated_against gives error "column [model_name].[associated_column_name] does not exist"

I'm trying to use pg_search to search through an associated model. When I run a search I get the error "PG::Error: ERROR: column plans.name does not exist". I'm running the search in the "plans" model and trying to search against association the "place" with a column "name". The has_many :through model that connects these is polymorphic. Somehow the sql query is combining the two and throwing an error. I've run the associated_against migration (rails g pg_search:migration:associated_against), scoured the documentation, and looked for others with the error and come up with nothing, it must be that I'm just overlooking something. It runs correctly (without the more extensive search results) if I just remove the associated_against line in plan.rb. Any help would be appreciated!

Plan.rb:

class Plan < ActiveRecord::Base
    belongs_to :user
    has_many :plan_places, :dependent => :destroy
    has_many :places, through: :plan_places, source: :plan

    include PgSearch
    pg_search_scope :search, :against => [:title, :summary],
        associated_against: { places: [:name, :address]},
        using: {tsearch: {dictionary: "english"}},
        ignoring: :accents

    def self.text_query(query)
        if query.present?
          search(query)
        else
          scoped
        end
    end
end

Place.rb:

class Place < ActiveRecord::Base
    has_many :plan_places, as: :sortable #polymorphic -- could this be the issue??
    has_many :plans, through: :plan_places

   include PgSearch
        multisearchable :against => [:name, :address]
        pg_search_scope :search, against: [:name, :address],
            using: {tsearch: {dictionary: "english"}},
            ignoring: :accents
    def self.text_query(query)
            if query.present?
              search(query)
            else
              scoped
            end
    end
end

Controller:

def index
    query = params[:query]
    @plans = Plan.text_query(query)
  end

Full Error Message:

PG::Error: ERROR:  column plans.name does not exist
LINE 1: ...OUTER JOIN (SELECT "plans"."id" AS id, string_agg("plans"."n...
                                                             ^
: SELECT "plans".*, ((ts_rank((to_tsvector('english', unaccent(coalesce("plans"."title"::text, ''))) || to_tsvector('english', unaccent(coalesce("plans"."summary"::text, ''))) || to_tsvector('english', unaccent(coalesce(pg_search_ef8b0c36567cc241900c73.pg_search_1d546fcf34c118d2a7b8f6::text, ''))) || to_tsvector('english', unaccent(coalesce(pg_search_ef8b0c36567cc241900c73.pg_search_f3147101e01c522d780049::text, '')))), (to_tsquery('english', ''' ' || unaccent('giraffe') || ' ''')), 0))) AS pg_search_rank FROM "plans" LEFT OUTER JOIN (SELECT "plans"."id" AS id, string_agg("plans"."name"::text, ' ') AS pg_search_1d546fcf34c118d2a7b8f6, string_agg("plans"."address"::text, ' ') AS pg_search_f3147101e01c522d780049 FROM "plans" INNER JOIN "plan_places" ON "plan_places"."plan_id" = "plans"."id" INNER JOIN "plans" "places_plans" ON "places_plans"."id" = "plan_places"."plan_id" GROUP BY "plans"."id") pg_search_ef8b0c36567cc241900c73 ON pg_search_ef8b0c36567cc241900c73.id = "plans"."id" WHERE (((to_tsvector('english', unaccent(coalesce("plans"."title"::text, ''))) || to_tsvector('english', unaccent(coalesce("plans"."summary"::text, ''))) || to_tsvector('english', unaccent(coalesce(pg_search_ef8b0c36567cc241900c73.pg_search_1d546fcf34c118d2a7b8f6::text, ''))) || to_tsvector('english', unaccent(coalesce(pg_search_ef8b0c36567cc241900c73.pg_search_f3147101e01c522d780049::text, '')))) @@ (to_tsquery('english', ''' ' || unaccent('giraffe') || ' '''))))  ORDER BY pg_search_rank DESC, "plans"."id" ASC, created_at DESC
like image 340
scottier Avatar asked Nov 08 '13 09:11

scottier


1 Answers

I ended up using a somewhat hacky workaround if someone else experiences this. In the plan model's search method, I just run the search on the child model (places), get the associated plans of the results using the has_many :through table (plan_places), and append this list to the regular plan search results. It's ugly and probably very inefficient, so if anyone knows a "real" answer please submit!

def self.text_query(query)
    if query.present?
      place_ids = Place.text_query(query).map(&:id).uniq
      plan_ids = PlanPlace.where(sortable_id: place_ids, sortable_type: "Place").map(&:plan_id).uniq
      plans = find(plan_ids)
      plans = plans + search(query)
    else
      scoped
    end
  end
like image 177
scottier Avatar answered Nov 12 '22 12:11

scottier