Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Rails Queries
# rails 2.3.5
p = Promotion.find(:first, :conditions => ["subdomain = ?", "foobar"])
# to find based on multiple conditions based on multiple associations
User.find(:all, :include => [:contact, :promotion], :conditions => [' = ? and = ?', '', 22])
# to find last record in old rails apps, rails 2
p = Promotion.find(:all, :order => "id desc", :limit => 1) # newest first
# and in ascending order
p = Promotion.find(:all, :order => "id asc", :limit => 1) # oldest first
# rails 3+
## PLUCK returns an array of attribute values (casted to some data type)
## rather than loading all attribute of every object in a query then maping
## or selecting the attributes we want.
## instead of
## we can use
## this avoids an N+1 query as stated by Richard W.
richardw [8:36 AM]
Although to make it go faster, may want to pre-include models or fetch w/ a sql + in clause to avoid an N+1 query like....
``` user_ids ={ |u| }
trip_sql = "SELECT FROM trips WHERE trips.user_id IN (#{user_ids.join(',')})"
trip_ids = self.connection.exec_query(trip_sql).map{|row|row['id']}
marcuss [8:44 AM]
What do you mean by avoid an `n + 1` query?
richardw [8:47 AM]
```user_ids ={ |u| }
trip_ids ={ |u|{ |t| } }.flatten.uniq.compact
First line: you get the users, which rails does something along the lines of:
`SELECT * FROM users where users.promotion_id = #{}`
Which is your 1 query
Second line, you get the trips for all those users. You're doing it via a map without preloading trips, so it will do:
```# Map iteration 1
SELECT * FROM trips where trips.user_id = #{} LIMIT 1
# Map iteration 2
SELECT * FROM trips where trips.user_id = #{} LIMIT 1
# Map iteration 3
SELECT * FROM trips where trips.user_id = #{} LIMIT 1
Which are your N queries.
Solution is to either preload trips (`self.users.includes(:trips)` if I remember the syntax), or do the query yourself (1 query to get whatever you need from trips like what I posted in my previous comment using the `IN` clause)
Doesn't matter as much in this case as it's not a web request so speed isn't as important here, but it's something to keep in mind because it does bite us in web requests (
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.