StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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".</p> <p>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).</p> <h2>Simple Approach - has_one association</h2> <p>One simple approach is to define a has_one relationship, e.g.</p> <pre><code>class User has_many :addresses has_one :primary_address, :class_name => 'Address', :conditions => ["primary = ?", true] end </code></pre> <p>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.</p> <p>This makes it quite easy to collect users and a single address details:</p> <pre><code># 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 } </code></pre> <p>However, this is still visiting each row, so there will be an extra query (and ruby array handling) for every user's address.</p> <h2>Much Better - Arel</h2> <p>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.</p> <p>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:</p> <pre><code>address = Address.arel_table primary_addresses = Address.where( address[:id].in( address.group(address[:user_id]).project(address[:id].minimum) ) ) </code></pre> <p>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.</p> <pre><code>area_codes = primary_addresses.collect(&:area_code) </code></pre> <p>The query will actually generate SQL like this:</p> <pre><code>SELECT "addresses".* FROM "addresses" WHERE "addresses"."id" IN ( SELECT MAX("addresses"."id") AS max_id FROM "addresses" GROUP BY "addresses"."user_id" ) </code></pre> <p>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) </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload