Newbie question

I have a following simple model structure

User belongs_to Address
Address has_one User

I therefore have a address_id foreign key in users table. I am trying to
find all users who are in a particular city (in the addresses table).
The
addresses table does not have a user_id foreign key. What is the best
way to
accomplish that?

I did something like Address.find_by_city(“San Jose”) but then I get a
list
of records - how do I extract the corresponding user records most
efficiently? I could go in a loop and for each address record found
above do
a User.find_by_address_id and pass in the address_id - but that would be
very inefficient. There has to be something simple and quick for this.

Thanks,
Sanjay.

I did something like Address.find_by_city(“San Jose”) but then I get a list
of records - how do I extract the corresponding user records most
efficiently? I could go in a loop and for each address record found above do
a User.find_by_address_id and pass in the address_id - but that would be
very inefficient. There has to be something simple and quick for this.

Unless it’s because I’m hungry and not thinking straight, I think you
have
your model/db mixed up…

User has_one Address
Address belongs_to User

And you have a ‘user_id’ field in your ‘addresses’ table.

You can then do:

Address.find_by_city(“San Jose”, :include => [:user])

-philip

Philip H. wrote:

I did something like Address.find_by_city(“San Jose”) but then I get a list
of records - how do I extract the corresponding user records most
efficiently? I could go in a loop and for each address record found above do
a User.find_by_address_id and pass in the address_id - but that would be
very inefficient. There has to be something simple and quick for this.

Unless it’s because I’m hungry and not thinking straight, I think you
have
your model/db mixed up…

User has_one Address
Address belongs_to User

And you have a ‘user_id’ field in your ‘addresses’ table.

You can then do:

Address.find_by_city(“San Jose”, :include => [:user])

-philip

Thanks Philip. I had that upside down. However I ran into a subsequent
problem.

Normally I would be able to create an array like following
@all_needed_users = User.find(:all, :conditions => “city = ‘San
Jose’”).map{|c| [c.username, c.id]}

and I would have an @all_needed_users array to be used for option_tags
in a select_tag

In this case I did the following
@all_needed_users = Address.find(:all, :conditions =>
“addresses.company_id = 12 and addresses.city = ‘Bangalore’” , :include
=> [:user]).map{|c| [c.username, c.id]} and there are two problems -

  1. The c.id is ambiguous as both addresss and user tables return the id
    column
  2. The SQL generated indicates that all column names are now named to
    something else as shown in the SQL below and therefore c.username and
    c.id are not understood. So the question is whether there is a simple
    way like the example above to create an array that I can use for
    option_tags?

SQL generated now is -

SELECT addresses.id AS t0_r0, addresses.street AS t0_r1,
addresses.city AS t0_r2, addresses.zip AS t0_r3,
addresses.company_id AS t0_r4, addresses.user_id AS t0_r5,
users.id AS t1_r0, users.firstname AS t1_r1, users.lastname AS
t1_r2, users.created_at AS t1_r3, users.login_count AS t1_r4,
users.last_login_at AS t1_r5, users.username AS t1_r6,
users.hashed_pwd AS t1_r7, users.salt AS t1_r8 FROM addresses
LEFT OUTER JOIN users ON users.id = addresses.user_id WHERE
(addresses.company_id = 12 and addresses.city = ‘Bangalore’)

Lastly is there a way to restrict the number of columns being returned
by the :include option - I don’t see a way to do that.

TIA,
ST

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs