1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
# frozen_string_literal: true
# rubocop:disable CodeReuse/ActiveRecord
module ClickHouse
class QueryBuilder
attr_reader :table
attr_accessor :conditions, :manager
VALID_NODES = [
Arel::Nodes::In,
Arel::Nodes::Equality,
Arel::Nodes::LessThan,
Arel::Nodes::LessThanOrEqual,
Arel::Nodes::GreaterThan,
Arel::Nodes::GreaterThanOrEqual
].freeze
def initialize(table_name)
@table = Arel::Table.new(table_name)
@manager = Arel::SelectManager.new(Arel::Table.engine).from(@table).project(Arel.star)
@conditions = []
end
# The `where` method currently does 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 conditions 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 conditions 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.
# @return [ClickHouse::QueryBuilder] New instance of query builder.
def where(conditions)
validate_condition_type!(conditions)
new_instance = deep_clone
if conditions.is_a?(Arel::Nodes::Node)
new_instance.conditions << conditions
else
add_conditions_to(new_instance, conditions)
end
new_instance
end
def select(*fields)
new_instance = deep_clone
existing_fields = new_instance.manager.projections.filter_map do |projection|
if projection.is_a?(Arel::Attributes::Attribute)
projection.name.to_s
elsif projection.to_s == '*'
nil
end
end
new_projections = existing_fields + fields.map(&:to_s)
new_instance.manager.projections = new_projections.uniq.map { |field| new_instance.table[field] }
new_instance
end
def order(field, direction = :asc)
validate_order_direction!(direction)
new_instance = deep_clone
new_order = new_instance.table[field].public_send(direction.to_s.downcase) # rubocop:disable GitlabSecurity/PublicSend
new_instance.manager.order(new_order)
new_instance
end
def limit(count)
manager.take(count)
self
end
def offset(count)
manager.skip(count)
self
end
def to_sql
apply_conditions!
manager.to_sql
end
def to_redacted_sql
::ClickHouse::Redactor.redact(self)
end
private
def validate_condition_type!(condition)
return unless condition.is_a?(Arel::Nodes::Node) && VALID_NODES.exclude?(condition.class)
raise ArgumentError, "Unsupported Arel node type for QueryBuilder: #{condition.class.name}"
end
def add_conditions_to(instance, conditions)
conditions.each do |key, value|
instance.conditions << if value.is_a?(Array)
instance.table[key].in(value)
else
instance.table[key].eq(value)
end
end
end
def deep_clone
new_instance = self.class.new(table.name)
new_instance.manager = manager.clone
new_instance.conditions = conditions.map(&:clone)
new_instance
end
def apply_conditions!
manager.constraints.clear
conditions.each { |condition| manager.where(condition) }
end
def validate_order_direction!(direction)
return if %w[asc desc].include?(direction.to_s.downcase)
raise ArgumentError, "Invalid order direction '#{direction}'. Must be :asc or :desc"
end
end
end
# rubocop:enable CodeReuse/ActiveRecord
|
