CODEWITHSUNDEEP
ruby-on-rails
NullVoxPopuli
NullVoxPopuli

Reputation: 65143

Ruby on Rails: How do I search between two tables?

I figured the first think I'd do is use an :include, but I get an error telling me all the columns from the address table don't exist -- probably because it still doesn't know about the address table, and is still checking against the users table.

search code:

search = params[:search]
    conditions = "real_name LIKE '%#{search}%' OR login LIKE '%#{search}%' OR email LIKE '%#{search}%'"
    address_conditions = "number LIKE '%#{search}%' OR street LIKE '%#{search}%' OR city LIKE '%#{search}%' OR state LIKE '%#{search}%' OR zip_code LIKE '%#{search}%'" 
    conditions = conditions + " OR " + address_conditions
    @paid = User.find(:all, :include => :addresses, :conditions => "has_paid = 't' AND (#{conditions})")

EDIT: changed the :include to a :joins and I get this error:

SQLite3::SQLException: no such column: number: 
         SELECT * FROM "users" 
         WHERE (has_paid != 't' AND 
                     (real_name LIKE '%12%' OR login LIKE '%12%' OR email LIKE '%12%' OR
                      number LIKE '%12%' OR street LIKE '%12%' OR city LIKE '%12%' OR
                      state LIKE '%12%' OR zip_code LIKE '%12%'))

Upvotes: 0

Views: 370

Answers (2)

idlefingers
idlefingers

Reputation: 32037

As @house9 said, you need to use :joins, but also you need to use the table name on the address fields in the query. So rather than "name LIKE '%#{search}%' ...", you need "addresses.name LIKE '%#{search}%' ...", etc. For clarity, you might want to add users. to the user fields, but it's implied in the way the query is built.

Something you should try to do in these situations is to run your query directly on your database. If it still throws errors then (which this would), you know it's your query, not your ruby.

Upvotes: 4

house9
house9

Reputation: 20614

try :joins instead of :include

also check log/development.log all sql is logged there

Upvotes: 1

Related Questions