Dreyfuzz Dreyfuzz - 3 months ago 18
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}%"])
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.

Answer

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