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 the 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'.

Query option

You have to think about the query as the command that will bring the information from all the records, so don't use where or similar conditions, because they will be calculated using join.

For this option, you can use either a String, a Proc, or an ActiveRecord::Relation. For the first 2 options, you need to manually provide the table name as the first argument.

cte.query :comments, 'SELECT * FROM comments'
cte.query :comments, -> { Comments.all }
cte.query Comments.all

Accessing attributes

The class provided in the |cte| has many ways to facilitate accessing columns and other query stuff.

cte.query_table          # Gives an Arel::Table of the defined statement
cte.query_table['col']   # Gives an Arel::Attributes::Attribute
cte.col('col')           # Same as the above
cte.sql('MAX(col)')      # A literal SQL string with Arel properties

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)

The advantage of the String and Proc query option on an auxiliary statement is that they allow arguments. Which means that the can be further configured based on what you provide on the :args key. CAUTION if you use with with multiple values and the :args for arguments, the list of arguments will be used for all String and Proc queries.

user = User.with(:comments, args: {id: 1}).order(:last_comment_content)

You can also change the name of the key used to pass arguments using auxiliary_statement.send_arguments_key config.