Rails Queries

rails_queries.rb

# 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