activerecord find conditions for associated models

Question

so i havent found much documentation about using conditions on activerecord find methods for associated models, but i have found a variety of examples. although none of them seem to be working for me.

user has_one avatar
avatar belongs_to user

Avatar.find(:all, :include => :user, :conditions => {:user => {:login => 'admin'}})

returns the ridiculously long error

SQLite3::SQLException: no such column: user.login: SELECT "avatars"."id" AS t0_r0, "avatars"."user_id" AS t0_r1, "avatars"."featured" AS t0_r2, "avatars"."avatar_file_name" AS t0_r3, "avatars"."avatar_content_type" AS t0_r4, "avatars"."avatar_file_size" AS t0_r5, "avatars"."created_at" AS t0_r6, "avatars"."updated_at" AS t0_r7, "users"."id" AS t1_r0, "users"."login" AS t1_r1, "users"."email" AS t1_r2, "users"."crypted_password" AS t1_r3, "users"."password_salt" AS t1_r4, "users"."persistence_token" AS t1_r5, "users"."perishable_token" AS t1_r6, "users"."login_count" AS t1_r7, "users"."failed_login_count" AS t1_r8, "users"."last_request_at" AS t1_r9, "users"."current_login_at" AS t1_r10, "users"."last_login_at" AS t1_r11, "users"."current_login_ip" AS t1_r12, "users"."last_login_ip" AS t1_r13, "users"."created_at" AS t1_r14, "users"."updated_at" AS t1_r15 FROM "avatars"  LEFT OUTER JOIN "users" ON "users".id = "avatars".user_id WHERE ("user"."login" = 'admin') 

i have tried a variety of other patterns including

:conditions => "user.login == 'admin'"

but nothing is working.

this is with a rails 2.3.8 app, but rails3 is installed as well. so i have activerecord 3.0.0 & 2.3.8 installed. i thought this may be an issue, but it seems unlikely.

Answer 1

instead of

:user => {:login => 'admin'}

try

:users => {:login => 'admin'}

user*s*

Answer 2

The standard Rails format for table names in the database itself is "all lower case, plural."

So you can debug your SQL error and see:

no such column: user.login

Since we know the table name is "users" the problem is that you're referencing table "user"

Fix depends on the version of Active Record. I prefer old school since it makes it more clear that the condition clause is SQL, not Ruby/Rails:

Avatar.find(:all, :include => :user,
  :conditions => "users.login = 'admin'")

Note: Rails 3 is different from the above...

Added:

Remember that if you're looking up a login supplied as a parameter from the user, you must properly escape the parameter. Rails makes this easy:

user_login = "admin"  # or, for example, params['user']
Avatar.find(:all, :include => :user,
  :conditions => ["users.login = ?", user_login])

Answer 3

so now i am trying to elaborate on that query

user has_one avatar
user has_one profile
avatar belongs_to user
profile belongs_to user

and i am actually able to get this to work...

Avatar.find(:all, :include => {:user => :profile}, :conditions => {:users => {:profiles => {:description => 'foo'}}})

however i want it to return an avatar if the profile.description is NOT NULL.

Avatar.find(:all, :include => {:user => :profile}, :conditions => {:users => {:profiles => "profiles.description IS NOT NULL"}})

so when i change the query to follow the SQL syntax rather than the railsesque syntax, i get an error "No such column user.profiles"

it doesnt seem to matter what statement i use for "profiles.description IS NOT NULL", the error is the same. it must be something else with that structure.