CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlsearchjoin
Dreyfuzz
Dreyfuzz

Reputation: 476

Single search box produces "ERROR: column reference "name" is ambiguous"

I am trying to use a single search field to filter any column following Railscasts 240. The key piece for defining the search function is in the Contact model.

  def self.search(search)
    if search
      joins(:school).where(['name ILIKE ? OR email ILIKE ? OR school.name ILIKE ?', "%#{search}%", "%#{search}%", "%#{search}%"])
    else
      all
    end
  end

This works without the join and school.name. Contact belongs to school and has a school_id column. The exact error is:

PG::AmbiguousColumn: ERROR:  column reference "name" is ambiguous

I'm guessing the ambiguous error is because I am trying to search both contact.name and school.name. Looking for a suggestion to allow searching both without adding another search field or needing the user to specify the search type.

Edit:

Good suggestions below to use contact.name to deal with the ambiguity, but that leads to another error:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "contact"
LINE 1: ...ON "schools"."id" = "contacts"."school_id" WHERE (contact.na...
                                                             ^
: SELECT  "contacts".* FROM "contacts" INNER JOIN "schools" ON "schools"."id" = "contacts"."school_id" WHERE (contact.name ILIKE '%joseph%' OR email ILIKE '%joseph%' OR school.name ILIKE '%joseph%')  ORDER BY name asc LIMIT 50 OFFSET 0

I thought this was due to the inner join moving the entire query into the schools table, but the error persists even if I remove the other two queries and ONLY search on school.name.

Upvotes: 0

Views: 889

Answers (2)

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

if columns that same names is more then must add alias

try this, i add contacts.name 

 def self.search(search)
    if search
      joins(:school).where(['contacts.name ILIKE ? OR email ILIKE ? school.name ILIKE ?', "%#{search}%", "%#{search}%", "%#{search}%"])
    else
      all
    end
  end

Upvotes: 1

siemakuba
siemakuba

Reputation: 191

The ambiguous errors is caused by PG not knowing which name column the query reffers to - contacts.name or schools.name. You can fix it by changing your query to:

joins(:school).where(['contacts.name ILIKE ? OR email ILIKE ? OR schools.name ILIKE ?', "%#{search}%", "%#{search}%", "%#{search}%"])

Upvotes: 2

Related Questions