Stupid ActiveRecord Tricks

Short presentation given at the August 2016 indy.rb meetup covering some lesser-knowns bits of Rails' ActiveRecord (and just a touch of Arel)

Transcript

1. Stupid
   ActiveRecord
   Tricks
   Little used goodness in Rails' database API
   

   View Slide

2. replace
   Replaces a collection with an array of new items and
   diffs just the new stuff.
   @post = Post.find(params[:id])
   new_tags = params[:tags].split(",").map(&:strip).map do |tag_name|
   Tag.find_or_initialize_by(name: tag_name)
   end
   @post.tags.replace(new_tags)
   @post.tags.replace(["doo", "ggie", "gaga"])
   # => ActiveRecord::AssociationTypeMismatch: Tag expected, got String
   

   View Slide

3. many? & any?
   Do we have one of these? Do we have more than one
   of these?
   @post.tags.any? # Boolean
   @post.tags.many? # Boolean
   

   View Slide

4. More than first and last
   @post.tags.second
   @post.tags.second_to_last
   @post.tags.third
   @post.tags.third_to_last
   @post.tags.fourth
   @post.tags.fifth
   @post.tags.forty_two
   

   View Slide

5. Not the whole thing
   Several methods to limit the returned attributes:
   Post.all.select(:id)
   # SELECT "posts"."id" FROM "posts"
   # => #]>
   Post.all.pluck(:id)
   # SELECT "posts"."id" FROM "posts"
   # => [1]
   Post.all.ids
   # SELECT "posts"."id" FROM "posts"
   # => [1]
   

   View Slide

6. Maths
   AR methods that do math in the database
   Book.average(:price)
   # SELECT AVG("books"."price") FROM "books"
   Book.minimum(:price)
   # SELECT MIN("books"."price") FROM "books"
   Book.maximum(:price)
   # SELECT MAX("books"."price") FROM "books"
   Book.sum(:price)
   # SELECT SUM("books"."price") FROM "books"
   

   View Slide

7. Group and having
   Adding conditions to your groupings.
   Post.group(:word_count).having("created_at > ?", 1.years.ago)
   # Posts grouped by their word_count if they were created in the last year
   

   View Slide

8. New in Rails 5: or
   Generate an OR SQL query. Works great with scope
   class Post < ApplicationRecord
   scope :featured, -> { where(featured: true) }
   scope :recent, -> { where("created_at > ?", 2.days.ago) }
   end
   Post.featured.or(Post.recent)
   # SELECT "posts".* FROM "posts"
   # WHERE ("posts"."featured" = ?
   # OR (created_at > '2016-08-08 17:07:58.750856')) [["featured", true]]
   

   View Slide

9. New in Rails 5: left_outer_joins
   # Rails 4
   authors = Author.join('LEFT OUTER JOIN "posts" ON "posts"."author_id" = "authors"."id"')
   .uniq
   .select("authors.*, COUNT(posts.*) as posts_count")
   .group("authors.id")
   # Rails 5
   authors = Author.left_outer_joins(:posts)
   .uniq
   .select("authors.*, COUNT(posts.*) as posts_count")
   .group("authors.id")
   # Also
   Author.left_joins :posts, :comments
   # SELECT "authors".* FROM "authors"
   # LEFT OUTER JOIN "posts" ON "posts"."author_id" = "authors"."id"
   # LEFT OUTER JOIN "comments" ON "comments"."author_id" = "authors"."id"
   

   View Slide

10. Arel predicates
    Who wants to write SQL? Not this guy. Arel is the
    code that powers ActiveRecord's queries, and we can
    use it to write Ruby instead of SQL fragments.
    

    View Slide

11. class Post < ApplicationRecord
    # Helper method to make things a bit easier
    def self.[](name)
    self.arel_table[name]
    end
    end
    Post.where(Post[:created_at].gt 2.days.ago)
    # Same as : Post.where(Post.arel_table[:created_at].gt 2.days.ago)
    # Same as: Post.where('created_at > ?', 2.days.ago)
    # SELECT "posts".* FROM "posts" WHERE ("posts"."created_at" > '2016-08-08 17:38:46.068891')
    

    View Slide

12. Gem recommendation
    

    View Slide

13. Groupdate
    Post.group_by_day(:created_at).count
    # {
    # Sat, 28 May 2016 => 50,
    # Sun, 29 May 2016 => 100,
    # Mon, 30 May 2016 => 34
    # }
    https://github.com/ankane/groupdate
    

    View Slide