Question about ActiveRecord assoication and :join

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