SQL Statement for first row in a "has_many"-association

I'm trying to access only the first lines of associated tables. What is th开发者_如何学Pythone best and fastest way to do this? (Ideally an SQL statement)

Associations :

class User
  has_many :addresses


class Address
  belongs_to :user

The fastest way I could figure out was:

User.includes(:addresses).find(:all).map {|m| m.addresses.first.area_code}

But this operation is too slow for my requirements (imagine I have 200 Users with an average of 20 Adresses (~4000 Adresses overall))

This is just an example of what I am trying to do.

I thought that this could be expressed by an SQL JOIN statement, but I'm not seeing how to receive a table of every users first Address.

Example of expected output:

12345 #=>User.find(1).addresses.first.area_code

56789 #=>User.find(2).addresses.first.area_code 

23456 #=>User.find(3).addresses.first.area_code 

.....

area_codes = 
  Address.find_by_sql(
    "SELECT DISTINCT user_id, area_code FROM addresses"
  ).index_by(&:user_id)
User.find(:all).map{ |u| [u, area_codes[u.id]] }

if you want all of them in one piece.

There's a couple of approaches you can take for this. Even though 4000 records isn't all that much in the scheme of things, avoiding having to visit in multiple queries (like you hinted in your question) is a "good thing".

A key to the solution is how you choose which address you want to look at. "First" is relative and non-deterministic without a qualifier (order by, or some other attribute).

Simple Approach - has_one association

One simple approach is to define a has_one relationship, e.g.

class User
  has_many :addresses
  has_one :primary_address, :class_name => 'Address', :conditions => ["primary = ?", true]
end

That's assuming you have an attribute in Address you can use a decider. If you don't specify a condition, you'll just get whichever address entry comes "first" since has_one applies a SQL LIMIT 1 to the underlying query.

This makes it quite easy to collect users and a single address details:

# iterating over users
User.scoped.each { |user| puts user.primary_address.area_code }
# or collect area_codes
area_codes = User.scoped.collect{ |user| user.primary_address.area_code }

However, this is still visiting each row, so there will be an extra query (and ruby array handling) for every user's address.

Much Better - Arel

Once you've selceted the deciding criteria between the multiple addresses a user may have, it's possible to roll this up into a single query.

If I assume we'll just select the address by min(id) - and assuming you're on Rails 3 - here's an Arel approach for querying the distinct area_codes:

address = Address.arel_table
primary_addresses = Address.where(
  address[:id].in(
    address.group(address[:user_id]).project(address[:id].minimum)
  )
)

primary_addresses is then a scope that gives you the full collection of 'primary' addresses for users. You can then process/list/collect as you need e.g.

area_codes = primary_addresses.collect(&:area_code)

The query will actually generate SQL like this:

SELECT "addresses".* 
FROM "addresses" 
WHERE "addresses"."id" IN (
  SELECT MAX("addresses"."id") AS max_id FROM "addresses" GROUP BY "addresses"."user_id"
)

As you can see, the sub-select is returning the list of address id's - one for each user. If you want to use a different criteria for selecting the address to use, you can change the sub-query (e.g. to make it select on primary=true with limit 1)