Class: Tina4::QueryBuilder

Inherits:
Object
  • Object
show all
Defined in:
lib/tina4/query_builder.rb

Overview

QueryBuilder — Fluent SQL query builder.

Usage:

# Standalone
result = Tina4::QueryBuilder.from_table("users", db: db)
  .select("id", "name")
  .where("active = ?", [1])
  .order_by("name ASC")
  .limit(10)
  .get

# From ORM model
result = User.query
  .where("age > ?", [18])
  .order_by("name")
  .get

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(table, db: nil) ⇒ QueryBuilder

Returns a new instance of QueryBuilder.



22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/tina4/query_builder.rb', line 22

def initialize(table, db: nil)
  @table = table
  @db = db
  @columns = ["*"]
  @wheres = []
  @params = []
  @joins = []
  @group_by_cols = []
  @havings = []
  @having_params = []
  @order_by_cols = []
  @limit_val = nil
  @offset_val = nil
end

Class Method Details

.from_table(table_name, db: nil) ⇒ QueryBuilder

Create a QueryBuilder for a table.

Parameters:

  • table_name (String) —

    The database table name.

  • db (Object, nil) (defaults to: nil) —

    Optional database connection.

Returns:



42
43
44
 
# File 'lib/tina4/query_builder.rb', line 42

def self.from_table(table_name, db: nil)
  new(table_name, db: db)
end

Instance Method Details

#count ⇒ Integer

Execute the query and return the row count.

Returns:

  • (Integer) —

    Number of matching rows.



186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
 
# File 'lib/tina4/query_builder.rb', line 186

def count
  ensure_db!

  # Build a count query by replacing columns
  original = @columns
  @columns = ["COUNT(*) as cnt"]
  sql = to_sql
  @columns = original

  all_params = @params + @having_params

  row = @db.fetch_one(sql, all_params.empty? ? [] : all_params)
  return 0 if row.nil?

  # Handle case-insensitive column names
  (row["cnt"] || row["CNT"] || row[:cnt] || row[:CNT] || 0).to_i
end

#exists? ⇒ Boolean

Check whether any matching rows exist.

Returns:

  • (Boolean) 


207
208
209
 
# File 'lib/tina4/query_builder.rb', line 207

def exists?
  count > 0
end

#first ⇒ Hash?

Execute the query and return a single row.

Returns:

  • (Hash, nil) —

    A single row hash, or nil.



175
176
177
178
179
180
181
 
# File 'lib/tina4/query_builder.rb', line 175

def first
  ensure_db!
  sql = to_sql
  all_params = @params + @having_params

  @db.fetch_one(sql, all_params.empty? ? [] : all_params)
end

#get ⇒ Object

Execute the query and return the database result.

Returns:

  • (Object) —

    The result from db.fetch.



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

def get
  ensure_db!
  sql = to_sql
  all_params = @params + @having_params

  @db.fetch(
    sql,
    all_params.empty? ? [] : all_params,
    limit: @limit_val || 100,
    offset: @offset_val || 0
  )
end

#group_by(column) ⇒ self

Add a GROUP BY column.

Parameters:

  • column (String) —

    Column name.

Returns:

  • (self) 


101
102
103
104
 
# File 'lib/tina4/query_builder.rb', line 101

def group_by(column)
  @group_by_cols << column
  self
end

#having(expression, params = []) ⇒ self

Add a HAVING clause.

Parameters:

  • expression (String) —

    HAVING expression with ? placeholders.

  • params (Array) (defaults to: []) —

    Parameter values.

Returns:

  • (self) 


111
112
113
114
115
 
# File 'lib/tina4/query_builder.rb', line 111

def having(expression, params = [])
  @havings << expression
  @having_params.concat(params)
  self
end

#join(table, on_clause) ⇒ self

Add an INNER JOIN.

Parameters:

  • table (String) —

    Table to join.

  • on_clause (String) —

    Join condition.

Returns:

  • (self) 


82
83
84
85
 
# File 'lib/tina4/query_builder.rb', line 82

def join(table, on_clause)
  @joins << "INNER JOIN #{table} ON #{on_clause}"
  self
end

#left_join(table, on_clause) ⇒ self

Add a LEFT JOIN.

Parameters:

  • table (String) —

    Table to join.

  • on_clause (String) —

    Join condition.

Returns:

  • (self) 


92
93
94
95
 
# File 'lib/tina4/query_builder.rb', line 92

def left_join(table, on_clause)
  @joins << "LEFT JOIN #{table} ON #{on_clause}"
  self
end

#limit(count, offset = nil) ⇒ self

Set LIMIT and optional OFFSET.

Parameters:

  • count (Integer) —

    Maximum rows to return.

  • offset (Integer, nil) (defaults to: nil) —

    Number of rows to skip.

Returns:

  • (self) 


131
132
133
134
135
 
# File 'lib/tina4/query_builder.rb', line 131

def limit(count, offset = nil)
  @limit_val = count
  @offset_val = offset unless offset.nil?
  self
end

#or_where(condition, params = []) ⇒ self

Add a WHERE condition with OR.

Parameters:

  • condition (String) —

    SQL condition with ? placeholders.

  • params (Array) (defaults to: []) —

    Parameter values.

Returns:

  • (self) 


71
72
73
74
75
 
# File 'lib/tina4/query_builder.rb', line 71

def or_where(condition, params = [])
  @wheres << ["OR", condition]
  @params.concat(params)
  self
end

#order_by(expression) ⇒ self

Add an ORDER BY clause.

Parameters:

  • expression (String) —

    Column and direction (e.g. “name ASC”).

Returns:

  • (self) 


121
122
123
124
 
# File 'lib/tina4/query_builder.rb', line 121

def order_by(expression)
  @order_by_cols << expression
  self
end

#select(*columns) ⇒ self

Set the columns to select.

Parameters:

  • columns (Array<String>) —

    Column names.

Returns:

  • (self) 


50
51
52
53
 
# File 'lib/tina4/query_builder.rb', line 50

def select(*columns)
  @columns = columns unless columns.empty?
  self
end

#to_mongo ⇒ Hash

Convert the fluent builder state into a MongoDB-compatible query hash.

Returns:

  • (Hash) —

    with keys :filter, :projection, :sort, :limit, :skip (only non-empty).



214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
 
# File 'lib/tina4/query_builder.rb', line 214

def to_mongo
  result = {}

  # -- projection --
  if @columns != ["*"]
    result[:projection] = @columns.each_with_object({}) { |col, h| h[col.strip] = 1 }
  end

  # -- filter --
  unless @wheres.empty?
    param_index = 0
    and_conditions = []
    or_conditions = []

    @wheres.each_with_index do |(connector, condition), i|
      mongo_cond, param_index = parse_condition_to_mongo(condition, param_index)
      if i == 0 || connector == "AND"
        and_conditions << mongo_cond
      else
        or_conditions << mongo_cond
      end
    end

    if or_conditions.any?
      and_merged = merge_mongo_conditions(and_conditions)
      all_branches = [and_merged] + or_conditions
      result[:filter] = { "$or" => all_branches }
    else
      result[:filter] = merge_mongo_conditions(and_conditions)
    end
  end

  # -- sort --
  unless @order_by_cols.empty?
    sort = {}
    @order_by_cols.each do |expr|
      parts = expr.strip.split(/\s+/)
      field = parts[0]
      direction = (parts[1] && parts[1].upcase == "DESC") ? -1 : 1
      sort[field] = direction
    end
    result[:sort] = sort
  end

  # -- limit / skip --
  result[:limit] = @limit_val unless @limit_val.nil?
  result[:skip] = @offset_val unless @offset_val.nil?

  result
end

#to_sql ⇒ String

Build and return the SQL string without executing.

Returns:

  • (String) —

    The constructed SQL query.



140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/tina4/query_builder.rb', line 140

def to_sql
  sql = "SELECT #{@columns.join(', ')} FROM #{@table}"

  sql += " #{@joins.join(' ')}" unless @joins.empty?

  sql += " WHERE #{build_where}" unless @wheres.empty?

  sql += " GROUP BY #{@group_by_cols.join(', ')}" unless @group_by_cols.empty?

  sql += " HAVING #{@havings.join(' AND ')}" unless @havings.empty?

  sql += " ORDER BY #{@order_by_cols.join(', ')}" unless @order_by_cols.empty?

  sql
end

#where(condition, params = []) ⇒ self

Add a WHERE condition with AND.

Parameters:

  • condition (String) —

    SQL condition with ? placeholders.

  • params (Array) (defaults to: []) —

    Parameter values.

Returns:

  • (self) 


60
61
62
63
64
 
# File 'lib/tina4/query_builder.rb', line 60

def where(condition, params = [])
  @wheres << ["AND", condition]
  @params.concat(params)
  self
end