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
44
45
46
 
# File 'lib/click_house/client/query_builder.rb', line 35

def initialize(table, alias_name = nil)
  @table = case table
           when self.class # subquery
             Arel::Nodes::TableAlias.new(table.to_arel, alias_name)
           when Arel::Nodes::TableAlias
             table
           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



482
483
484
485
486
 
# File 'lib/click_house/client/query_builder.rb', line 482

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) 


252
253
254
 
# File 'lib/click_house/client/query_builder.rb', line 252

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



371
372
373
374
 
# File 'lib/click_house/client/query_builder.rb', line 371

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.



82
83
84
 
# File 'lib/click_house/client/query_builder.rb', line 82

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



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

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



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

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



433
434
435
436
437
438
 
# File 'lib/click_house/client/query_builder.rb', line 433

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



467
468
469
470
471
 
# File 'lib/click_house/client/query_builder.rb', line 467

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 https://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:



204
205
206
207
208
209
210
 
# File 'lib/click_house/client/query_builder.rb', line 204

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 



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

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 



174
175
176
177
178
 
# File 'lib/click_house/client/query_builder.rb', line 174

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.



96
97
98
99
100
101
102
 
# File 'lib/click_house/client/query_builder.rb', line 96

def having(constraints)
  validate_constraint_type!(constraints)

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

#initialize_copy(other) ⇒ Object 



48
49
50
51
52
 
# File 'lib/click_house/client/query_builder.rb', line 48

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:



312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
 
# File 'lib/click_house/client/query_builder.rb', line 312

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 



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

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



450
451
452
453
454
455
 
# File 'lib/click_house/client/query_builder.rb', line 450

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) 


348
349
350
 
# File 'lib/click_house/client/query_builder.rb', line 348

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

#none ⇒ ClickHouse::Client::QueryBuilder

Returns a query that yields no rows.

Examples:

query.none.to_sql
# => "SELECT * FROM `test_table` WHERE 1 = 0"

Returns:



219
220
221
222
223
224
225
 
# File 'lib/click_house/client/query_builder.rb', line 219

def none
  clone.tap do |new_instance|
    new_instance.manager.where(
      Arel.sql('1 = 0')
    )
  end
end

#offset(count) ⇒ Object 



185
186
187
188
 
# File 'lib/click_house/client/query_builder.rb', line 185

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

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



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

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



386
387
388
389
 
# File 'lib/click_house/client/query_builder.rb', line 386

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) 


354
355
356
 
# File 'lib/click_house/client/query_builder.rb', line 354

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

#reverse_order ⇒ ClickHouse::Client::QueryBuilder

Inverts the direction of all ORDER BY clauses: ASC becomes DESC and vice versa.

Examples:

query.order(:id).reverse_order.to_sql
# => "SELECT * FROM `test_table` ORDER BY `test_table`.`id` DESC"

Returns:

Raises:

  • (ArgumentError) —

    if any ordering node has no explicit direction



159
160
161
162
163
164
165
166
167
168
169
170
171
172
 
# File 'lib/click_house/client/query_builder.rb', line 159

def reverse_order
  clone.tap do |new_instance|
    new_instance.manager.ast.orders = new_instance.manager.ast.orders.map do |order|
      case order
      when Arel::Nodes::Ascending
        Arel::Nodes::Descending.new(order.expr)
      when Arel::Nodes::Descending
        Arel::Nodes::Ascending.new(order.expr)
      else
        raise ArgumentError, "Cannot reverse ordering node without explicit direction: #{order.class.name}"
      end
    end
  end
end

#select(*fields) ⇒ Object 



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
130
131
132
 
# File 'lib/click_house/client/query_builder.rb', line 104

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 



498
499
500
 
# File 'lib/click_house/client/query_builder.rb', line 498

def to_arel
  manager
end

#to_redacted_sql ⇒ Object 



493
494
495
496
 
# File 'lib/click_house/client/query_builder.rb', line 493

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

#to_sql ⇒ Object 



488
489
490
491
 
# File 'lib/click_house/client/query_builder.rb', line 488

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

#union(*others, type: :distinct, alias_name: 'union_subquery') ⇒ ClickHouse::Client::QueryBuilder

Combines this query with one or more other queries using UNION and returns a new builder that selects from the combined result as an aliased subquery, so further chaining (#where, #order, #limit, ...) applies to the outer SELECT.

Parameters:

  • others (Array<ClickHouse::Client::QueryBuilder>) —

    queries to combine with

  • type (Symbol) (defaults to: :distinct) —

    :distinct or :all

  • alias_name (String, Symbol) (defaults to: 'union_subquery') —

    alias for the union subquery

Returns:

Raises:

  • (ArgumentError) 


292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
 
# File 'lib/click_house/client/query_builder.rb', line 292

def union(*others, type: :distinct, alias_name: 'union_subquery')
  validate_union_type!(type)
  raise ArgumentError, 'at least one query to union with is required' if others.empty?

  others.each do |other|
    raise ArgumentError, "expected #{self.class}, got #{other.class}" unless other.is_a?(self.class)
  end

  node_class = type == :all ? Arel::Nodes::UnionAll : Arel::Nodes::Union
  union_node = others.reduce(to_arel.clone.ast) do |left, other|
    node_class.new(left, other.to_arel.clone.ast)
  end

  self.class.new(Arel::Nodes::TableAlias.new(union_node, alias_name.to_s))
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.



69
70
71
72
73
74
75
 
# File 'lib/click_house/client/query_builder.rb', line 69

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:



269
270
271
272
273
274
275
276
277
278
279
280
281
 
# File 'lib/click_house/client/query_builder.rb', line 269

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