Dreyfuzz Dreyfuzz - 1 year ago 78
Ruby Question

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}%"])

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.


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.

Answer Source

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}%"])