pg_search icon indicating copy to clipboard operation
pg_search copied to clipboard

Error when searching a table with a composite primary key

Open adriely-dourado opened this issue 1 year ago • 0 comments

Create the table

 create_table :people, primary_key: [:project_id, :person_id] do |t|
    t.binary :project_id
    t.binary :person_id
    t.string :name, null: false
 end

The model looks like this:

 class Person < ApplicationRecord
   self.primary_key = [:project_id, :person_id]
   
   pg_search_scope :search,  against: :name
 end

When I run rails c on my console and type: Person.search("name") this is the result:

Person Load (3.1ms)  SELECT "people".* FROM "people" INNER JOIN (SELECT "people"."[""project_id"", ""person_id""]" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("people"."name"::text, ''))), (to_tsquery('simple', ''' ' || 'name' || ' ''')), 0)) AS rank FROM "people" WHERE ((to_tsvector('simple', coalesce("people"."name"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'name' || ' ''')))) AS pg_search_c9022680f888674e2b2274 ON "people"."[""project_id"", ""person_id""]" = pg_search_c9022680f888674e2b2274.pg_search_id /* loading for pp */ ORDER BY pg_search_c9022680f888674e2b2274.rank DESC, "people"."[""project_id"", ""person_id""]" ASC LIMIT $1 `

and that is the error:

An error occurred when inspecting the object: #<ActiveRecord::StatementInvalid:"PG::UndefinedColumn: ERROR:  column people.[\"project_id\", \"person_id\"] does not exist\nLINE 1: ...ELECT \"people\".* FROM \"people\" INNER JOIN (SELECT \"people\".\"...\n  

adriely-dourado avatar Mar 06 '24 19:03 adriely-dourado