CTE.rb

require "rubygems"

gem "pry-byebug"
gem "minitest"
gem "activerecord", "4.1.16"

require "active_record"
require "minitest/autorun"
require "logger"
require "pry"

ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts do |t|
    t.string :title
    t.text :article
  end

  create_table :comments do |t|
    t.integer :post_id
    t.text :text
  end
end

class Post < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

post1 = Post.create(title: "Title 1", article: "My article 1")
post2 = Post.create(title: "Title 2", article: "My article 2")

10.times do |i|
  post1.comments.create(text: "Comment 1.#{i}")
  post2.comments.create(text: "Comment 2.#{i}")
end

posts = Post.arel_table
comments = Comment.arel_table

cte_table = Arel::Table.new(:posts_cte)
composed_cte = Arel::Nodes::As.new(cte_table, posts.project(posts[:id]).where(posts[:id].gt(1)))
query = comments.project(Arel.star).join(cte_table).on(cte_table[:id].eq(comments[:post_id])).with(composed_cte).to_sql

puts "#" * 80
puts "#" * 80
puts query
puts ActiveRecord::Base.connection.execute(query).count
puts "#" * 80
puts "#" * 80

################################################################################
################################################################################
WITH "posts_cte" AS (SELECT "posts"."id" FROM "posts"  WHERE "posts"."id" > 1) SELECT * FROM "comments" INNER JOIN "posts_cte" ON "posts_cte"."id" = "comments"."post_id"
D, [2017-01-03T16:55:43.616218 #29168] DEBUG -- :    (0.3ms)  WITH "posts_cte" AS (SELECT "posts"."id" FROM "posts"  WHERE "posts"."id" > 1) SELECT * FROM "comments" INNER JOIN "posts_cte" ON "posts_cte"."id" = "comments"."post_id"
10
################################################################################
################################################################################