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_name) ⇒ QueryBuilder

Returns a new instance of QueryBuilder.



32
33
34
35
 
# File 'lib/click_house/client/query_builder.rb', line 32

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

Instance Attribute Details

#manager ⇒ Object

Returns the value of attribute manager.



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

def manager
  @manager
end

#table ⇒ Object (readonly)

Returns the value of attribute table.



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

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



306
307
308
309
310
 
# File 'lib/click_house/client/query_builder.rb', line 306

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



195
196
197
198
 
# File 'lib/click_house/client/query_builder.rb', line 195

def avg(column)
  column_node = normalize_operand(column)
  Arel::Nodes::NamedFunction.new('avg', [column_node])
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



224
225
226
227
228
229
230
231
 
# File 'lib/click_house/client/query_builder.rb', line 224

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



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

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



257
258
259
260
261
262
 
# File 'lib/click_house/client/query_builder.rb', line 257

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



291
292
293
294
295
 
# File 'lib/click_house/client/query_builder.rb', line 291

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

#from(subquery, alias_name) ⇒ Object 



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

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

#group(*columns) ⇒ Object 



130
131
132
133
134
 
# File 'lib/click_house/client/query_builder.rb', line 130

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.



76
77
78
79
80
81
82
 
# File 'lib/click_house/client/query_builder.rb', line 76

def having(constraints)
  validate_constraint_type!(constraints)

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

#initialize_copy(other) ⇒ Object 



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

def initialize_copy(other)
  super

  @manager = other.manager.clone
end

#joins(table_name, constraint = nil) ⇒ Object 



156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
 
# File 'lib/click_house/client/query_builder.rb', line 156

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 



136
137
138
139
 
# File 'lib/click_house/client/query_builder.rb', line 136

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



274
275
276
277
278
279
 
# File 'lib/click_house/client/query_builder.rb', line 274

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

#offset(count) ⇒ Object 



141
142
143
144
 
# File 'lib/click_house/client/query_builder.rb', line 141

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

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



114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/click_house/client/query_builder.rb', line 114

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



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

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

#select(*fields) ⇒ Object 



84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/click_house/client/query_builder.rb', line 84

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 



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

def to_arel
  manager
end

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



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

def to_redacted_sql(bind_index_manager = ClickHouse::Client::BindIndexManager.new)
  ClickHouse::Client::Redactor.redact(self, bind_index_manager)
end

#to_sql ⇒ Object 



312
313
314
315
 
# File 'lib/click_house/client/query_builder.rb', line 312

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.



58
59
60
61
62
63
64
 
# File 'lib/click_house/client/query_builder.rb', line 58

def where(constraints)
  validate_constraint_type!(constraints)

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