Skip to content

Instantly share code, notes, and snippets.

Embed
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 => ['contacts.email = ? and promotions.id = ?', 'marcuss@hesonline.com', 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
Person.find(:all).map{|p|p.id}
## we can use
Person.pluck(:id)
## this avoids an N+1 query as stated by Richard W.
=begin
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 = users.map{ |u| u.id }
trip_sql = "SELECT trips.id 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 = users.map{ |u| u.id }
trip_ids = users.map{ |u| u.trips.map{ |t| t.id } }.flatten.uniq.compact
```
First line: you get the users, which rails does something along the lines of:
`SELECT * FROM users where users.promotion_id = #{self.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 = #{user1.id} LIMIT 1
# Map iteration 2
SELECT * FROM trips where trips.user_id = #{user2.id} LIMIT 1
# Map iteration 3
SELECT * FROM trips where trips.user_id = #{user3.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)
[8:49]
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 (http://bugs.hesonline.net/redmine/attachments/12603/Screen_Shot_2017-07-28_at_8.53.51_AM.png)
=end
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.