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)?
Any help would be greatly appreciated.
On 23 Aug 2008, at 19:58, Mike J. wrote:
end
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 C. wrote:
On 23 Aug 2008, at 19:58, Mike J. wrote:
end
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
On 23 Aug 2008, at 20:56, Mike J. 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 =
'[email protected]”], :joins => :users_emails]
It didn’t seem to generate the join properly.
Any help would be greatly appreciated.
Mike -
On 23-Aug-08, at 10:29 PM, Mike J. wrote:
I tried the following, but it did not work. I think I’mmissing
something:
User.find(:all, :conditions => [“name = ‘joe’ and email =
'[email protected]”], :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 =
'[email protected]”], :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 S. wrote:
Mike -
On 23-Aug-08, at 10:29 PM, Mike J. wrote:
I tried the following, but it did not work. I think I’mmissing
something:
User.find(:all, :conditions => [“name = ‘joe’ and email =
'[email protected]”], :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
MIke - if you re-read my note, you’ll see I suggested “user_emails”.
(user is singular)
What is the name of your association ?
Mike,
What does:
UsersEmail.find(:all, :conditions => ["email = ‘[email protected]’ "])
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.
On Aug 23, 8:49 pm, Mike J. [email protected]
Jodi S. 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
hi,
UsersEmail.find(:all, :conditions => ["email = ‘[email protected]’ "])
Returns me an array of all users_emails rows which have email
‘[email protected]’
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 =
'[email protected]”], :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:
Mike,
What does:
UsersEmail.find(:all, :conditions => ["email = ‘[email protected]’ "])
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.
On Aug 23, 8:49�pm, Mike J. [email protected]
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 C. wrote:
On 24 Aug 2008, at 12:48, Mike J. <rails-mailing-list@andreas-
s.net> wrote:
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 =
'[email protected]”], :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
On 24 Aug 2008, at 13:18, Mike J. <rails-mailing-list@andreas-
s.net> 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
On 24 Aug 2008, at 12:48, Mike J. <rails-mailing-list@andreas-
s.net> wrote:
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 =
'[email protected]”], :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