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



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

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



264
265
266
267
 
# File 'lib/click_house/client/query_builder.rb', line 264

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



293
294
295
296
297
298
299
300
 
# File 'lib/click_house/client/query_builder.rb', line 293

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



309
310
311
312
313
 
# File 'lib/click_house/client/query_builder.rb', line 309

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



326
327
328
329
330
331
 
# File 'lib/click_house/client/query_builder.rb', line 326

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



360
361
362
363
364
 
# File 'lib/click_house/client/query_builder.rb', line 360

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:



205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/click_house/client/query_builder.rb', line 205

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



343
344
345
346
347
348
 
# File 'lib/click_house/client/query_builder.rb', line 343

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) 


241
242
243
 
# File 'lib/click_house/client/query_builder.rb', line 241

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



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

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) 


247
248
249
 
# File 'lib/click_house/client/query_builder.rb', line 247

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 



392
393
394
 
# File 'lib/click_house/client/query_builder.rb', line 392

def to_arel
  manager
end

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



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

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 



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

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