Hi, I have two tables, "users" and "users_emails".
users have the following columns: |id|name| users_emails have the following columns: |user_id|email|
The classes look like the following: class UsersEmail< ActiveRecord::Base belongs_to :user end
class User< ActiveRecord::Base has_many :users_emails end
Hi, I have two tables, "users" and "users_emails".
users have the following columns: |id|name| users_emails have the following columns: |user_id|email|
The classes look like the following: class UsersEmail< ActiveRecord::Base belongs_to :user end
class User< ActiveRecord::Base has_many :users_emails end
---
What is the best way (the rails way using ActiveRecord methods) to
find a user based on "User.name" and "UsersEmail.email". I want the User
who has a particular name and particular email.I can do something like: User.find(:all, :condition["name = :name", 'joe'], :join => ["do a join on Users and UsersEmail"]) where I specify the user.name in the :condition, but I was wondering
if there is a better way of doing this (without explicitly witting out
the join)?
If you do :joins => :users_emails ActiveRecord will write the join
clause for you
Fred
hm..thanks.
But I was wondering if there was a way to avoid the :join condition all together in the :find. Such as using ":though" in the has_many method in ActiveRecord?
Frederick Cheung wrote:
hm..thanks.
But I was wondering if there was a way to avoid the :join condition
all together in the :find. Such as using ":though" in the has_many method in ActiveRecord?
through is for something different (when you have a many to many
association) which you don't have here.
I'm not sure what your aversion to :joins is when what you're doing is
inherently a joiny sort of thing.
Fred
I tried the following, but it did not work. I think I'mmissing something:
User.find(:all, :conditions => ["name = 'joe' and email = 'foo@foo.com"], :joins => :users_emails]
It didn't seem to generate the join properly.
Any help would be greatly appreciated.
Mike -
I tried the following, but it did not work. I think I'mmissing something:
User.find(:all, :conditions => ["name = 'joe' and email = 'foo@foo.com"], :joins => :users_emails]
It didn't seem to generate the join properly.
Any help would be greatly appreciated.
the :joins key takes either a string or the name of the association (like the :include param).
So take a look at your User model, where it references UserEmail
It likely reads something like:
has_many :user_emails
if that's the case then the join will read ":join => :user_emails".
Jodi
Hi, I do have the ":joins => :users_emails"
User.find(:all, :conditions => ["name = 'joe' and email = 'foo@foo.com"], :joins => :users_emails]
But when I look at the SQL being generated, it looks like a join isn't taking place. The actual error I get is that it cannot find the attribute 'email'.
Any help would be greatly appreciated.
Jodi Showers wrote:
MIke - if you re-read my note, you'll see I suggested "user_emails".
(user is singular)
What is the name of your association ?
Jodi Showers wrote:
MIke - if you re-read my note, you'll see I suggested "user_emails". (user is singular)
What is the name of your association ?
I put the Model classes in my original post: The classes look like the following: class UsersEmail< ActiveRecord::Base belongs_to :user end
class User< ActiveRecord::Base has_many :users_emails end
Mike, What does:
UsersEmail.find(:all, :conditions => ["email = 'joe@example.com' "])
return? (Just do a quick check in console.)
Also, as a tip: people can help you out more if you post the actual info returned instead of a paraphrase of it.
hi, UsersEmail.find(:all, :conditions => ["email = 'joe@example.com' "]) Returns me an array of all users_emails rows which have email 'joe@example.com'
The problem is I need to also need to do a select based on users.name. Fred suggested doing a :join using with the syntax ":join => :users_emails"
But the following did not work. When I look at the SQL rails tried to generate, it did not seem to do the join. User.find(:all, :conditions => ["name = 'joe' and email = 'foo@foo.com"], :joins => :users_emails]
The actual error I get is "ActiveRedocrd::StatementInvalid: Mysql::Error: #42S22Unknown column 'email' in 'whre clause': SELECT * FROM users users_emails WHERE users_emails (email = 'joe')
Any help would be greatly appreciated.
smeade wrote:
hi, UsersEmail.find(:all, :conditions => ["email = 'joe@example.com' "]) Returns me an array of all users_emails rows which have email 'joe@example.com'
The problem is I need to also need to do a select based on users.name. Fred suggested doing a :join using with the syntax ":join => :users_emails"
But the following did not work. When I look at the SQL rails tried to generate, it did not seem to do the join. User.find(:all, :conditions => ["name = 'joe' and email = 'foo@foo.com"], :joins => :users_emails]
The actual error I get is "ActiveRedocrd::StatementInvalid: Mysql::Error: #42S22Unknown column 'email' in 'whre clause': SELECT * FROM users users_emails WHERE users_emails (email = 'joe')
It's very unhelpful when you type error messages in, introducing
errors in the process rather than just copy and pasting them. Be sure
that what you pass to joins is precisely the name of the association
(ie don't pluralize it unless it's a plural association etc)
Fred
Hi, The name of the assoication passed into the join is exactly the same as the one defined in the model class.
This is the exact error I get, the Join is not being generated in SQL.: ActiveRecord::StatementInvalid: Mysql::Error: #42S22Unknown column 'email' in 'w here clause': SELECT * FROM users users_emails WHERE (email ='joe')
Frederick Cheung wrote:
Hi, The name of the assoication passed into the join is exactly the same as the one defined in the model class.
:joins can work in two ways: association names or SQL fragments. It
looks like rails thinks you mean the latter when you mean the former.
I don't recall precisely what criteria is used but if you are using
strings then make sure you are using symbols
Fred