Auxiliary Statements

Provides a way to write auxiliary statements for use in a larger query. It's reconfigured on the model, and then can be used during querying process. PostgreSQL Docs

How it works

Model

First you need to configure the statements you want to use. These statements are very similar to scopes, but with a little more options:

# models/user.rb
class User < ActiveRecord::Base

  auxiliary_statement :last_comment do |cte|
    cte.query Comment.distinct_on(:user_id).order(:user_id, id: :desc)
    cte.attributes content: :last_comment_content
  end

end

The statement is lazy load, so the block is called only when a query requires its usage. To configure your statement, these are the options available:

cte.query The query that will be performed inside the auxiliary statement (WITH). It most likely will be one that brings extra information related to the main class, in this case the User class.

cte.attributes The list of attributes that will be exposed to the main query and after it is able to access through the entries fetch. It's read as The column form the query => The alias exposed. It accepts join columns in the left side as 'table.column' => 'alias'.

cte.join_type The type of join between the main query and statement query. By default it's set to :inner, that will perform an INNER JOIN. The options are :inner, :left, :right, :full.

cte.join The columns used on the join in the main query. It has similar behavior as the attributes, and it's read as The column from the main query == The column from the statement query. It accepts join columns in both sides as 'table.column' => 'table.column'.

Querying

with(*list)

Once you have configured all your statements, you can easily use them by calling with method.

user = User.with(:last_comment, :first_comment).first
user.last_comment_content

You are able to use all the exposed columns set on the right side of the attributes configuration in other methods like where, order, group, etc.

user = User.with(:last_comment).order(:last_comment_content)