method

with

v7.1.3.4 - Show latest stable - 0 notes - Class: ActiveRecord::QueryMethods

1.0.0
1.1.6
1.2.6
2.0.3
2.1.0
2.2.1
2.3.8
3.0.0
3.0.9
3.1.0
3.2.1
3.2.8
3.2.13
4.0.2
4.1.8
4.2.1
4.2.7
4.2.9
5.0.0.1
5.1.7
5.2.3
6.0.0
6.1.3.1
6.1.7.7
7.0.0
7.1.3.2 (0)
7.1.3.4 (0)
What's this?

with(*args) public

Add a Common Table Expression (CTE) that you can then reference within another SELECT statement.

Note: CTE’s are only supported in MySQL for versions 8.0 and above. You will not be able to use CTE’s with MySQL 5.7.

Post.with(posts_with_tags: Post.where("tags_count > ?", 0))
# => ActiveRecord::Relation
# WITH posts_with_tags AS (
#   SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts

Once you define Common Table Expression you can use custom FROM value or JOIN to reference it.

Post.with(posts_with_tags: Post.where("tags_count > ?", 0)).from("posts_with_tags AS posts")
# => ActiveRecord::Relation
# WITH posts_with_tags AS (
#  SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts_with_tags AS posts

Post.with(posts_with_tags: Post.where("tags_count > ?", 0)).joins("JOIN posts_with_tags ON posts_with_tags.id = posts.id")
# => ActiveRecord::Relation
# WITH posts_with_tags AS (
#   SELECT * FROM posts WHERE (tags_count > 0)
# )
# SELECT * FROM posts JOIN posts_with_tags ON posts_with_tags.id = posts.id

It is recommended to pass a query as ActiveRecord::Relation. If that is not possible and you have verified it is safe for the database, you can pass it as SQL literal using Arel.

Post.with(popular_posts: Arel.sql("... complex sql to calculate posts popularity ..."))

Great caution should be taken to avoid SQL injection vulnerabilities. This method should not be used with unsafe values that include unsanitized input.

To add multiple CTEs just pass multiple key-value pairs

Post.with(
  posts_with_comments: Post.where("comments_count > ?", 0),
  posts_with_tags: Post.where("tags_count > ?", 0)
)

or chain multiple .with calls

Post
  .with(posts_with_comments: Post.where("comments_count > ?", 0))
  .with(posts_with_tags: Post.where("tags_count > ?", 0))

Show source

# File activerecord/lib/active_record/relation/query_methods.rb, line 459
    def with(*args)
      check_if_method_has_arguments!(__callee__, args)
      spawn.with!(*args)
    end

with

Related methods