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



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

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

#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



254
255
256
257
 
# File 'lib/click_house/client/query_builder.rb', line 254

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



283
284
285
286
287
288
289
290
 
# File 'lib/click_house/client/query_builder.rb', line 283

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



299
300
301
302
303
 
# File 'lib/click_house/client/query_builder.rb', line 299

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



316
317
318
319
320
321
 
# File 'lib/click_house/client/query_builder.rb', line 316

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



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

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(table_name, constraint = nil) ⇒ Object 



201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
 
# File 'lib/click_house/client/query_builder.rb', line 201

def joins(table_name, constraint = nil)
  clone.tap do |new_instance|
    join_table = table_name.is_a?(Arel::Table) ? table_name : Arel::Table.new(table_name)

    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_table[right]
                         left_field.eq(right_field)
                       end

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

    if join_condition
      new_instance.manager.join(join_table).on(join_condition)
    else
      new_instance.manager.join(join_table)
    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



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

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) 


231
232
233
 
# File 'lib/click_house/client/query_builder.rb', line 231

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



269
270
271
272
 
# File 'lib/click_house/client/query_builder.rb', line 269

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) 


237
238
239
 
# File 'lib/click_house/client/query_builder.rb', line 237

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 



382
383
384
 
# File 'lib/click_house/client/query_builder.rb', line 382

def to_arel
  manager
end

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



376
377
378
379
380
 
# File 'lib/click_house/client/query_builder.rb', line 376

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 



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

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