Class: ClickHouse::Client::QueryBuilder

Inherits:
QueryLike
  • Object
show all
Defined in:
lib/click_house/client/query_builder.rb

Constant Summary collapse

VALID_NODES = 
[
  Arel::Nodes::In,
  Arel::Nodes::Equality,
  Arel::Nodes::LessThan,
  Arel::Nodes::LessThanOrEqual,
  Arel::Nodes::GreaterThan,
  Arel::Nodes::GreaterThanOrEqual,
  Arel::Nodes::NamedFunction,
  Arel::Nodes::NotIn,
  Arel::Nodes::NotEqual,
  Arel::Nodes::Between,
  Arel::Nodes::And,
  Arel::Nodes::Or,
  Arel::Nodes::Grouping,
  Arel::Nodes::Matches,
  Arel::Nodes::DoesNotMatch,
  Arel::Nodes::Division,
  Arel::Nodes::Multiplication,
  Arel::Nodes::As
].freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from QueryLike

#prepared_placeholders

Constructor Details

#initialize(table, alias_name = nil) ⇒ QueryBuilder

Returns a new instance of QueryBuilder.



35
36
37
38
39
40
41
42
43
 
# File 'lib/click_house/client/query_builder.rb', line 35

def initialize(table, alias_name = nil)
  @table = if table.is_a?(self.class) # subquery
             Arel::Nodes::TableAlias.new(table.to_arel, alias_name)
           else
             Arel::Table.new(table)
           end

  @manager = Arel::SelectManager.new(Arel::Table.engine).from(@table).project(Arel.star)
end

Instance Attribute Details

#manager ⇒ Object

Returns the value of attribute manager.



10
11
12
 
# File 'lib/click_house/client/query_builder.rb', line 10

def manager
  @manager
end

#table ⇒ Object (readonly)

Returns the value of attribute table.



9
10
11
 
# File 'lib/click_house/client/query_builder.rb', line 9

def table
  @table
end

Instance Method Details

#as(node, alias_name) ⇒ Arel::Nodes::As

Creates an alias for a node

Examples:

Alias an aggregate function

avg_node = query.avg(:price)
query.select(query.as(avg_node, 'average_price')).to_sql
# => "SELECT avg(`table`.`price`) AS average_price FROM `table`"

Parameters:

  • node (Arel::Nodes::Node) —

    The node to alias

  • alias_name (String, Symbol) —

    The alias name

Returns:

  • (Arel::Nodes::As) —

    The aliased node

Raises:

  • (ArgumentError) —

    if node is not an Arel Expression



415
416
417
418
419
 
# File 'lib/click_house/client/query_builder.rb', line 415

def as(node, alias_name)
  raise ArgumentError, "as requires an Arel node" unless node.is_a?(Arel::Expressions)

  node.as(alias_name.to_s)
end

#as_cte(name) ⇒ Arel::Nodes::Cte

Wraps this query as a CTE node with the given name, so it can be passed to #with: ‘query_builder.with(sub_query.as_cte(:foo))`. The name is rendered as a SQL identifier, not a quoted value.

The manager is cloned so later in-place mutations on this builder (e.g. #limit or #offset) do not alter the captured CTE body.

Parameters:

  • name (String, Symbol) —

    name the CTE is referenced by

Returns:

  • (Arel::Nodes::Cte) 


210
211
212
 
# File 'lib/click_house/client/query_builder.rb', line 210

def as_cte(name)
  Arel::Nodes::Cte.new(Arel::Nodes::SqlLiteral.new(name.to_s), to_arel.clone)
end

#avg(column) ⇒ Arel::Nodes::NamedFunction

Creates an AVG aggregate function node

Examples:

Basic average

query.select(query.avg(:duration)).to_sql
# => "SELECT avg(`table`.`duration`) FROM `table`"

Average with alias

query.select(query.avg(:price).as('average_price')).to_sql
# => "SELECT avg(`table`.`price`) AS average_price FROM `table`"

Parameters:

  • column (Symbol, String, Arel::Expressions) —

    The column to average

Returns:

  • (Arel::Nodes::NamedFunction) —

    The AVG function node



304
305
306
307
 
# File 'lib/click_house/client/query_builder.rb', line 304

def avg(column)
  column_node = normalize_operand(column)
  Arel::Nodes::NamedFunction.new('avg', [column_node])
end

#build ⇒ ClickHouse::QueryBuilder

Evaluates given block in scope of current builder. Example: query = ClickHouse::Client::QueryBuilder.new(‘test_table’).build do

select(named_func('argMax', [table[:id], table[:timestamp]]).as('max_id')).limit(10)

end

Returns:

  • (ClickHouse::QueryBuilder) —

    New instance of query builder.



79
80
81
 
# File 'lib/click_house/client/query_builder.rb', line 79

def build(&)
  instance_eval(&)
end

#count(column = nil) ⇒ Arel::Nodes::NamedFunction

Creates a COUNT aggregate function node

Examples:

Count all rows

query.select(query.count).to_sql
# => "SELECT count() FROM `table`"

Count specific column

query.select(query.count(:id)).to_sql
# => "SELECT count(`table`.`id`) FROM `table`"

Parameters:

  • column (Symbol, String, Arel::Expressions, nil) (defaults to: nil) —

    The column to count, or nil for COUNT(*)

Returns:

  • (Arel::Nodes::NamedFunction) —

    The COUNT function node



333
334
335
336
337
338
339
340
 
# File 'lib/click_house/client/query_builder.rb', line 333

def count(column = nil)
  if column.nil?
    Arel::Nodes::NamedFunction.new('count', [])
  else
    column_node = normalize_operand(column)
    Arel::Nodes::NamedFunction.new('count', [column_node])
  end
end

#count_if(condition) ⇒ Arel::Nodes::NamedFunction

Creates a countIf aggregate function node

Examples:

Count rows matching a condition

query.select(query.count_if(query.table[:status].eq('active'))).to_sql
# => "SELECT countIf(`table`.`status` = 'active') FROM `table`"

Parameters:

  • condition (Arel::Nodes::Node) —

    The condition to count

Returns:

  • (Arel::Nodes::NamedFunction) —

    The countIf function node

Raises:

  • (ArgumentError) —

    if condition is not an Arel node



349
350
351
352
353
 
# File 'lib/click_house/client/query_builder.rb', line 349

def count_if(condition)
  raise ArgumentError, "countIf requires an Arel node as condition" unless condition.is_a?(Arel::Nodes::Node)

  Arel::Nodes::NamedFunction.new('countIf', [condition])
end

#division(left, right) ⇒ Arel::Nodes::Grouping

Creates a division node with grouping

Examples:

Simple division

query.select(query.division(:completed, :total)).to_sql
# => "SELECT (`table`.`completed` / `table`.`total`) FROM `table`"

Calculate percentage

rate = query.division(:success_count, :total_count)
query.select(query.multiply(rate, 100).as('success_rate')).to_sql
# => "SELECT ((`table`.`success_count` / `table`.`total_count`) * 100) AS success_rate FROM `table`"

Parameters:

  • left (Arel::Expressions, Symbol, String, Numeric) —

    The dividend

  • right (Arel::Expressions, Symbol, String, Numeric) —

    The divisor

Returns:

  • (Arel::Nodes::Grouping) —

    The grouped division node for proper precedence



366
367
368
369
370
371
 
# File 'lib/click_house/client/query_builder.rb', line 366

def division(left, right)
  left_node = normalize_operand(left)
  right_node = normalize_operand(right)

  Arel::Nodes::Grouping.new(Arel::Nodes::Division.new(left_node, right_node))
end

#equality(left, right) ⇒ Arel::Nodes::Equality

Creates an equality node

Examples:

Use in WHERE clause

query.where(query.equality(:status, 'active')).to_sql
# => "SELECT * FROM `table` WHERE `table`.`status` = 'active'"

Use with countIf

query.select(query.count_if(query.equality(:type, 'premium'))).to_sql
# => "SELECT countIf(`table`.`type` = 'premium') FROM `table`"

Parameters:

  • left (Arel::Expressions, Symbol, String) —

    The left side of the comparison

  • right (Arel::Expressions, Symbol, String, Numeric, Boolean) —

    The right side of the comparison

Returns:

  • (Arel::Nodes::Equality) —

    The equality node



400
401
402
403
404
 
# File 'lib/click_house/client/query_builder.rb', line 400

def equality(left, right)
  left_node = normalize_operand(left)
  right_node = normalize_operand(right)
  Arel::Nodes::Equality.new(left_node, right_node)
end

#final ⇒ ClickHouse::Client::QueryBuilder

Applies the ClickHouse ‘FINAL` modifier to the main FROM table. See clickhouse.com/docs/en/sql-reference/statements/select/from#final-modifier

Note: ‘FINAL` only makes sense on a ClickHouse MergeTree-family table.

WARNING: Using ‘FINAL` in production code can cause excessive I/O and affect ClickHouse availability. Prefer using it only in test environments or behind a feature flag.

Examples:

query.final.to_sql
# => "SELECT * FROM `test_table` FINAL"

Returns:



177
178
179
180
181
182
183
 
# File 'lib/click_house/client/query_builder.rb', line 177

def final
  clone.tap do |new_instance|
    source = new_instance.manager.source.left
    wrapped = source.is_a?(ArelExtensions::Nodes::Final) ? source : ArelExtensions::Nodes::Final.new(source)
    new_instance.manager.from(wrapped)
  end
end

#from(subquery, alias_name) ⇒ Object 



185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
 
# File 'lib/click_house/client/query_builder.rb', line 185

def from(subquery, alias_name)
  clone.tap do |new_instance|
    new_from = if subquery.is_a?(self.class)
                 subquery.to_arel.as(alias_name)
               else
                 Arel::Nodes::TableAlias.new(subquery, alias_name)
               end

    if new_instance.manager.source.left.is_a?(ArelExtensions::Nodes::Final)
      new_from = ArelExtensions::Nodes::Final.new(new_from)
    end

    new_instance.manager.from(new_from)
  end
end

#group(*columns) ⇒ Object 



147
148
149
150
151
 
# File 'lib/click_house/client/query_builder.rb', line 147

def group(*columns)
  clone.tap do |new_instance|
    new_instance.manager.group(*columns)
  end
end

#having(constraints) ⇒ ClickHouse::QueryBuilder

The ‘having` method applies constraints to the HAVING clause, similar to how `where` applies constraints to the WHERE clause. It supports the same constraint types. Correct usage:

query.group(:name).having(count: 5).to_sql
"SELECT * FROM \"table\" GROUP BY \"table\".\"name\" HAVING \"table\".\"count\" = 5"

query.group(:name).having(query.table[:count].gt(10)).to_sql
"SELECT * FROM \"table\" GROUP BY \"table\".\"name\" HAVING \"table\".\"count\" > 10"

Returns:

  • (ClickHouse::QueryBuilder) —

    New instance of query builder.



93
94
95
96
97
98
99
 
# File 'lib/click_house/client/query_builder.rb', line 93

def having(constraints)
  validate_constraint_type!(constraints)

  clone.tap do |new_instance|
    apply_constraints(new_instance, constraints, :having)
  end
end

#initialize_copy(other) ⇒ Object 



45
46
47
48
49
 
# File 'lib/click_house/client/query_builder.rb', line 45

def initialize_copy(other)
  super

  @manager = other.manager.clone
end

#joins(source, constraint = nil, type: :inner) ⇒ ClickHouse::Client::QueryBuilder

Adds a JOIN clause. Pass ‘type: :outer` for `LEFT OUTER JOIN`. To join a subquery, pre-alias it via `QueryBuilder.new(sub, ’x’).table` or ‘sub.to_arel.as(’x’)‘ and pass that.

Returns:



245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
 
# File 'lib/click_house/client/query_builder.rb', line 245

def joins(source, constraint = nil, type: :inner)
  validate_join_type!(type)

  clone.tap do |new_instance|
    join_target = case source
                  when Arel::Table, Arel::Nodes::TableAlias then source
                  else Arel::Table.new(source)
                  end
    join_class = type == :outer ? Arel::Nodes::OuterJoin : Arel::Nodes::InnerJoin

    join_condition = case constraint
                     when Hash
                       # Handle hash based constraints like { table1.id: table2.ref_id } or {id: :ref_id}
                       constraint_conditions = constraint.map do |left, right|
                         left_field = left.is_a?(Arel::Attributes::Attribute) ? left : new_instance.table[left]
                         right_field = right.is_a?(Arel::Attributes::Attribute) ? right : join_target[right]
                         left_field.eq(right_field)
                       end

                       constraint_conditions.reduce(&:and)
                     when Proc
                       constraint.call(new_instance.table, join_target)
                     when Arel::Nodes::Node, Arel::Nodes::SqlLiteral
                       constraint
                     end

    if join_condition
      new_instance.manager.join(join_target, join_class).on(join_condition)
    else
      new_instance.manager.join(join_target, join_class)
    end
  end
end

#limit(count) ⇒ Object 



153
154
155
156
 
# File 'lib/click_house/client/query_builder.rb', line 153

def limit(count)
  manager.take(count)
  self
end

#multiply(left, right) ⇒ Arel::Nodes::Grouping

Creates a multiplication node with grouping

Examples:

Multiply columns

query.select(query.multiply(:quantity, :unit_price)).to_sql
# => "SELECT (`table`.`quantity` * `table`.`unit_price`) FROM `table`"

Convert to percentage

query.select(query.multiply(:rate, 100).as('percentage')).to_sql
# => "SELECT (`table`.`rate` * 100) AS percentage FROM `table`"

Parameters:

  • left (Arel::Expressions, Symbol, String, Numeric) —

    The left operand

  • right (Arel::Expressions, Symbol, String, Numeric) —

    The right operand

Returns:

  • (Arel::Nodes::Grouping) —

    The grouped multiplication node for proper precedence



383
384
385
386
387
388
 
# File 'lib/click_house/client/query_builder.rb', line 383

def multiply(left, right)
  left_node = normalize_operand(left)
  right_node = normalize_operand(right)

  Arel::Nodes::Grouping.new(Arel::Nodes::Multiplication.new(left_node, right_node))
end

#named_func(*args) ⇒ Arel::Nodes::NamedFunction Also known as: func

Shortcut for ‘Arel::Nodes::NamedFunction.new`

Returns:

  • (Arel::Nodes::NamedFunction) 


281
282
283
 
# File 'lib/click_house/client/query_builder.rb', line 281

def named_func(*args)
  Arel::Nodes::NamedFunction.new(*args)
end

#offset(count) ⇒ Object 



158
159
160
161
 
# File 'lib/click_house/client/query_builder.rb', line 158

def offset(count)
  manager.skip(count)
  self
end

#order(field, direction = :asc) ⇒ Object 



131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
 
# File 'lib/click_house/client/query_builder.rb', line 131

def order(field, direction = :asc)
  validate_order_direction!(direction)

  clone.tap do |new_instance|
    order_node = case field
                 when Arel::Nodes::SqlLiteral, Arel::Nodes::Node, Arel::Attribute
                   field
                 else
                   new_instance.table[field]
                 end

    new_order = direction.to_s.casecmp('desc').zero? ? order_node.desc : order_node.asc
    new_instance.manager.order(new_order)
  end
end

#quantile(level, column) ⇒ Arel::Nodes::NamedFunction

Creates a quantile aggregate function node

Examples:

Calculate median (50th percentile)

query.select(query.quantile(0.5, :response_time)).to_sql
# => "SELECT quantile(0.5)(`table`.`response_time`) FROM `table`"

Calculate 95th percentile with alias

query.select(query.quantile(0.95, :latency).as('p95')).to_sql
# => "SELECT quantile(0.95)(`table`.`latency`) AS p95 FROM `table`"

Parameters:

  • level (Float) —

    The quantile level (e.g., 0.5 for median)

  • column (Symbol, String, Arel::Expressions) —

    The column to calculate quantile for

Returns:

  • (Arel::Nodes::NamedFunction) —

    The quantile function node



319
320
321
322
 
# File 'lib/click_house/client/query_builder.rb', line 319

def quantile(level, column)
  column_node = normalize_operand(column)
  Arel::Nodes::NamedFunction.new("quantile(#{level})", [column_node])
end

#quote ⇒ Arel::Nodes::Node

Shortcut for ‘Arel::Nodes.build_quoted`

Returns:

  • (Arel::Nodes::Node) 


287
288
289
 
# File 'lib/click_house/client/query_builder.rb', line 287

def quote(...)
  Arel::Nodes.build_quoted(...)
end

#select(*fields) ⇒ Object 



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/click_house/client/query_builder.rb', line 101

def select(*fields)
  clone.tap do |new_instance|
    existing_fields = new_instance.manager.projections.filter_map do |projection|
      if projection.respond_to?(:to_s) && projection.to_s == '*'
        nil
      elsif projection.is_a?(Arel::Attributes::Attribute)
        projection.name.to_s
      elsif projection.is_a?(Arel::Expressions)
        projection
      end
    end

    new_projections = (existing_fields + fields).map do |field|
      if field.is_a?(Symbol)
        field.to_s
      else
        field
      end
    end

    new_instance.manager.projections = new_projections.uniq.map do |field|
      if field.is_a?(Arel::Expressions)
        field
      else
        new_instance.table[field.to_s]
      end
    end
  end
end

#to_arel ⇒ Object 



432
433
434
 
# File 'lib/click_house/client/query_builder.rb', line 432

def to_arel
  manager
end

#to_redacted_sql(bind_index_manager = ClickHouse::Client::BindIndexManager.new) ⇒ Object 



426
427
428
429
430
 
# File 'lib/click_house/client/query_builder.rb', line 426

def to_redacted_sql(bind_index_manager = ClickHouse::Client::BindIndexManager.new)
  visitor = ClickHouse::Client::ToRedactedSqlVisitor.new(ClickHouse::Client::ArelEngine.new,
    bind_manager: bind_index_manager)
  visitor.accept(manager.ast, Arel::Collectors::SQLString.new).value
end

#to_sql ⇒ Object 



421
422
423
424
 
# File 'lib/click_house/client/query_builder.rb', line 421

def to_sql
  visitor = ClickHouse::Client::ArelVisitor.new(ClickHouse::Client::ArelEngine.new)
  visitor.accept(manager.ast, Arel::Collectors::SQLString.new).value
end

#where(constraints) ⇒ ClickHouse::QueryBuilder

The ‘where` method currently only supports IN and equal to queries along with above listed VALID_NODES. For example, using a range (start_date..end_date) will result in incorrect SQL. If you need to query a range, use greater than and less than constraints with Arel.

Correct usage:

query.where(query.table[:created_at].lteq(Date.today)).to_sql
"SELECT * FROM \"table\" WHERE \"table\".\"created_at\" <= '2023-08-01'"

This also supports array constraints which will result in an IN query.

query.where(entity_id: [1,2,3]).to_sql
"SELECT * FROM \"table\" WHERE \"table\".\"entity_id\" IN (1, 2, 3)"

Range support and more ‘Arel::Nodes` could be considered for future iterations.

Returns:

  • (ClickHouse::QueryBuilder) —

    New instance of query builder.



66
67
68
69
70
71
72
 
# File 'lib/click_house/client/query_builder.rb', line 66

def where(constraints)
  validate_constraint_type!(constraints)

  clone.tap do |new_instance|
    apply_constraints(new_instance, constraints, :where)
  end
end

#with(cte) ⇒ ClickHouse::Client::QueryBuilder

Attaches a CTE node, emitting ‘WITH name AS (…)` before the SELECT. The CTE can then be referenced by name in FROM and IN-subquery positions, which is useful when the same subquery is needed in more than one place.

Build the node with #as_cte, for example ‘query_builder.with(sub_query.as_cte(:foo))`.

Chained calls accumulate: ‘query_builder.with(a).with(b)` emits both CTEs. This differs from `Arel::SelectManager#with`, which replaces any previously set CTE and would keep only `b`.

Parameters:

  • cte (Arel::Nodes::Cte) —

    a CTE node from #as_cte

Returns:



227
228
229
230
231
232
233
234
235
236
237
238
239
 
# File 'lib/click_house/client/query_builder.rb', line 227

def with(cte)
  unless cte.is_a?(Arel::Nodes::Cte)
    raise ArgumentError, "expected Arel::Nodes::Cte, got #{cte.class}. " \
      'Use #as_cte(name) to create one.'
  end

  clone.tap do |new_instance|
    existing = new_instance.manager.ast.with
    all_ctes = existing ? existing.children + [cte] : [cte]
    validate_unique_cte_names!(all_ctes)
    new_instance.manager.ast.with = Arel::Nodes::With.new(all_ctes)
  end
end