Class: Gitlab::SQL::CTE

Inherits:
Object
  • Object
show all
Defined in:
lib/gitlab/sql/cte.rb

Overview

Class for easily building CTE statements.

Example:

cte = CTE.new(:my_cte_name)
ns = Arel::Table.new(:namespaces)

cte << Namespace.
  where(ns[:parent_id].eq(some_namespace_id))

Namespace
  with(cte.to_arel).
  from(cte.alias_to(ns))

To skip materialization of the CTE query by passing materialized: false More context: www.postgresql.org/docs/12/queries-with.html

cte = CTE.new(:my_cte_name, materialized: false)

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(name, query, materialized: true) ⇒ CTE

name - The name of the CTE as a String or Symbol.



28
29
30
31
32
 
# File 'lib/gitlab/sql/cte.rb', line 28

def initialize(name, query, materialized: true)
  @table = Arel::Table.new(name)
  @query = query
  @materialized = materialized
end

Instance Attribute Details

#query ⇒ Object (readonly)

Returns the value of attribute query.



25
26
27
 
# File 'lib/gitlab/sql/cte.rb', line 25

def query
  @query
end

#table ⇒ Object (readonly)

Returns the value of attribute table.



25
26
27
 
# File 'lib/gitlab/sql/cte.rb', line 25

def table
  @table
end

Instance Method Details

#alias_to(alias_table) ⇒ Object

Returns an “AS” statement that aliases the CTE name as the given table name. This allows one to trick ActiveRecord into thinking it’s selecting from an actual table, when in reality it’s selecting from a CTE.

alias_table - The Arel table to use as the alias.



46
47
48
 
# File 'lib/gitlab/sql/cte.rb', line 46

def alias_to(alias_table)
  Arel::Nodes::As.new(table, alias_table)
end

#apply_to(relation) ⇒ Object

Applies the CTE to the given relation, returning a new one that will query from it.



52
53
54
55
56
 
# File 'lib/gitlab/sql/cte.rb', line 52

def apply_to(relation)
  relation.except(:where)
    .with(to_arel)
    .from(alias_to(relation.model.arel_table))
end

#to_arel ⇒ Object

Returns the Arel relation for this CTE.



35
36
37
38
39
 
# File 'lib/gitlab/sql/cte.rb', line 35

def to_arel
  sql = Arel::Nodes::SqlLiteral.new("(#{query_as_sql})")

  Gitlab::Database::AsWithMaterialized.new(table, sql, materialized: @materialized)
end