ActiveRecord’s Queries Tricks

The original https://medium.com/rubyinside…

Conditions are used when associating table JOINS

# User model
scope :activated, ->{
  joins(:profile).where(profiles: { activated: true })
}

A better approach

# Profile model
scope :activated, ->{ where(activated: true) }
# User model
scope :activated, ->{ joins(:profile).merge(Profile.activated) }

About the merge https://apidock.com/rails/Act… https://api.rubyonrails.org/c…

Nested join differences

  • User has_one Profile
  • Profile has_many Skills
User.joins(:profiles).merge(Profile.joins(:skills)) => SELECT users.* FROM users INNER JOIN profiles ON profiles.user_id  = users.id LEFT OUTER JOIN skills ON skills.profile_id = profiles.id # So you'd rather use: User.joins(profiles: :skills) => SELECT users.* FROM users INNER JOIN profiles ON profiles.user_id = users.id INNER JOIN skills ON skills.profile_id = profiles.id

Inner link and outer link

Exist query

Existence and non-existence

# Post scope :famous, ->{ where("view_count > ?" , 1_000) } # User scope :without_famous_post, ->{ where(_not_exists(Post.where("posts.user_id = users.id").famous)) } def self._not_exists(scope) "NOT #{_exists(scope)}" end def self._exists(scope) "EXISTS(#{scope.to_sql})" end

Subqueries subquery

For example, query some user’s post.

Bad practice

Post.where(user_id: User.created_last_month.pluck(:id))

The catch here is that you will run two SQL queries: one to get the user’s ID and another to get the posts from these user_id

Write a query like this

Post.where(user_id: User.created_last_month)

basis

.to_sql generates the SQL statement string.explain to retrieve the query analysis

Booleans

SELECT user. * FROM users where user. tall <> ‘t’ SELECT all tall <> ‘t’; SELECT all tall FROM User where User

Including null should be written like this

User.where("users.tall IS NOT TRUE")

or

User.where(tall: [false, nil])