Class: QueryKit::Query

Inherits:
Object
  • Object
show all
Defined in:
lib/querykit/query.rb

Overview

Query builder for constructing SQL SELECT statements.

Provides a fluent, chainable API for building complex SQL queries with automatic parameter binding for security.

Examples:

Basic query

query = Query.new('users')
  .select('id', 'name', 'email')
  .where('age', '>', 18)
  .order_by('name')
  .limit(10)

Complex query with joins

query = Query.new('users')
  .join('posts', 'users.id', 'posts.user_id')
  .where('users.active', true)
  .where('posts.published', true)
  .select('users.*', 'COUNT(posts.id) as post_count')
  .group_by('users.id')

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(table = nil) ⇒ Query

Initialize a new Query instance.

Examples:

query = Query.new('users')
query = Query.new  # table can be set later with from()

Parameters:

  • table (String, nil) (defaults to: nil) —

    the table name to query



58
59
60
61
62
63
64
65
66
67
68
69
70
71
 
# File 'lib/querykit/query.rb', line 58

def initialize(table = nil)
  @table = table
  @selects = []
  @wheres = []
  @joins = []
  @orders = []
  @groups = []
  @havings = []
  @limit_value = nil
  @offset_value = nil
  @bindings = []
  @distinct = false
  @unions = []
end

Instance Attribute Details

#bindings ⇒ Array

Returns the parameter bindings for safe query execution.

Returns:

  • (Array) —

    the parameter bindings for safe query execution



49
50
51
 
# File 'lib/querykit/query.rb', line 49

def bindings
  @bindings
end

#groups ⇒ Array<String> (readonly)

Returns the GROUP BY columns.

Returns:

  • (Array<String>) —

    the GROUP BY columns



40
41
42
 
# File 'lib/querykit/query.rb', line 40

def groups
  @groups
end

#joins ⇒ Array<Hash> (readonly)

Returns the JOIN clauses.

Returns:

  • (Array<Hash>) —

    the JOIN clauses



34
35
36
 
# File 'lib/querykit/query.rb', line 34

def joins
  @joins
end

#limit_value ⇒ Integer? (readonly)

Returns the LIMIT value.

Returns:

  • (Integer, nil) —

    the LIMIT value



43
44
45
 
# File 'lib/querykit/query.rb', line 43

def limit_value
  @limit_value
end

#offset_value ⇒ Integer? (readonly)

Returns the OFFSET value.

Returns:

  • (Integer, nil) —

    the OFFSET value



46
47
48
 
# File 'lib/querykit/query.rb', line 46

def offset_value
  @offset_value
end

#orders ⇒ Array<String> (readonly)

Returns the ORDER BY clauses.

Returns:

  • (Array<String>) —

    the ORDER BY clauses



37
38
39
 
# File 'lib/querykit/query.rb', line 37

def orders
  @orders
end

#selects ⇒ Array<String> (readonly)

Returns the columns to select.

Returns:

  • (Array<String>) —

    the columns to select



31
32
33
 
# File 'lib/querykit/query.rb', line 31

def selects
  @selects
end

#table ⇒ String? (readonly)

Returns the table name for this query.

Returns:

  • (String, nil) —

    the table name for this query



25
26
27
 
# File 'lib/querykit/query.rb', line 25

def table
  @table
end

#wheres ⇒ Array<Hash> (readonly)

Returns the WHERE conditions.

Returns:

  • (Array<Hash>) —

    the WHERE conditions



28
29
30
 
# File 'lib/querykit/query.rb', line 28

def wheres
  @wheres
end

Instance Method Details

#avg(column) ⇒ Object 



331
332
333
 
# File 'lib/querykit/query.rb', line 331

def avg(column)
  select("AVG(#{column}) as avg")
end

#count(column = '*') ⇒ Object

Aggregate shortcuts



327
328
329
 
# File 'lib/querykit/query.rb', line 327

def count(column = '*')
  select("COUNT(#{column}) as count")
end

#cross_join(table) ⇒ Object

CROSS JOIN



265
266
267
268
 
# File 'lib/querykit/query.rb', line 265

def cross_join(table)
  @joins << { type: 'CROSS', table: table }
  self
end

#distinct ⇒ Query

Set the query to return distinct results.

Examples:

query.select('country').distinct

Returns:

  • (Query) —

    self for method chaining



110
111
112
113
 
# File 'lib/querykit/query.rb', line 110

def distinct
  @distinct = true
  self
end

#from(table) ⇒ Query

Set the table name for this query.

Examples:

query.from('users')

Parameters:

  • table (String) —

    the table name

Returns:

  • (Query) —

    self for method chaining



80
81
82
83
 
# File 'lib/querykit/query.rb', line 80

def from(table)
  @table = table
  self
end

#group_by(*columns) ⇒ Object

GROUP BY



282
283
284
285
 
# File 'lib/querykit/query.rb', line 282

def group_by(*columns)
  @groups.concat(columns.flatten)
  self
end

#having(column, operator = nil, value = nil) ⇒ Object

HAVING



288
289
290
291
292
293
294
295
296
297
 
# File 'lib/querykit/query.rb', line 288

def having(column, operator = nil, value = nil)
  if value.nil? && !operator.nil?
    value = operator
    operator = '='
  end

  @havings << { column: column, operator: operator, value: value, boolean: 'AND' }
  @bindings << value unless value.nil?
  self
end

#join(table, first, operator = nil, second = nil) ⇒ Object

JOIN clauses



235
236
237
238
239
240
241
242
 
# File 'lib/querykit/query.rb', line 235

def join(table, first, operator = nil, second = nil)
  if operator.nil?
    operator = '='
    second = first
  end
  @joins << { type: 'INNER', table: table, first: first, operator: operator, second: second }
  self
end

#left_join(table, first, operator = nil, second = nil) ⇒ Object

LEFT JOIN



245
246
247
248
249
250
251
252
 
# File 'lib/querykit/query.rb', line 245

def left_join(table, first, operator = nil, second = nil)
  if operator.nil?
    operator = '='
    second = first
  end
  @joins << { type: 'LEFT', table: table, first: first, operator: operator, second: second }
  self
end

#limit(value) ⇒ Object

LIMIT and OFFSET



300
301
302
303
 
# File 'lib/querykit/query.rb', line 300

def limit(value)
  @limit_value = value
  self
end

#max(column) ⇒ Object 



343
344
345
 
# File 'lib/querykit/query.rb', line 343

def max(column)
  select("MAX(#{column}) as max")
end

#min(column) ⇒ Object 



339
340
341
 
# File 'lib/querykit/query.rb', line 339

def min(column)
  select("MIN(#{column}) as min")
end

#offset(value) ⇒ Object

Set the query offset



306
307
308
309
 
# File 'lib/querykit/query.rb', line 306

def offset(value)
  @offset_value = value
  self
end

#or_where(column, operator = nil, value = nil) ⇒ Query

Add an OR WHERE condition to the query.

Examples:

query.where('status', 'active').or_where('priority', 'high')

Parameters:

  • column (String) —

    the column name

  • operator (String, Object) (defaults to: nil) —

    the operator or value (if value is nil)

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

    the value to compare

Returns:

  • (Query) —

    self for method chaining



167
168
169
170
171
172
173
174
175
176
 
# File 'lib/querykit/query.rb', line 167

def or_where(column, operator = nil, value = nil)
  if value.nil? && !operator.nil?
    value = operator
    operator = '='
  end

  @wheres << { type: 'basic', column: column, operator: operator, value: value, boolean: 'OR' }
  @bindings << value unless value.nil?
  self
end

#order_by(column, direction = 'ASC') ⇒ Object

ORDER BY



271
272
273
274
 
# File 'lib/querykit/query.rb', line 271

def order_by(column, direction = 'ASC')
  @orders << { column: column, direction: direction.upcase }
  self
end

#order_by_desc(column) ⇒ Object

Set the query to order results in descending order



277
278
279
 
# File 'lib/querykit/query.rb', line 277

def order_by_desc(column)
  order_by(column, 'DESC')
end

#page(page_number, per_page = 15) ⇒ Object

Pagination



322
323
324
 
# File 'lib/querykit/query.rb', line 322

def page(page_number, per_page = 15)
  offset((page_number - 1) * per_page).limit(per_page)
end

#right_join(table, first, operator = nil, second = nil) ⇒ Object

RIGHT JOIN



255
256
257
258
259
260
261
262
 
# File 'lib/querykit/query.rb', line 255

def right_join(table, first, operator = nil, second = nil)
  if operator.nil?
    operator = '='
    second = first
  end
  @joins << { type: 'RIGHT', table: table, first: first, operator: operator, second: second }
  self
end

#select(*columns) ⇒ Query

Specify columns to select.

Examples:

Select specific columns

query.select('id', 'name', 'email')

Select with aliases

query.select('users.id', 'users.name as user_name')

Select all columns (default)

query.select  # equivalent to SELECT *

Parameters:

  • columns (Array<String>) —

    column names to select. Defaults to ‘*’ if none provided.

Returns:

  • (Query) —

    self for method chaining



98
99
100
101
102
 
# File 'lib/querykit/query.rb', line 98

def select(*columns)
  columns = ['*'] if columns.empty?
  @selects.concat(columns.flatten)
  self
end

#skip(value) ⇒ Object

Alias methods for offset and limit



312
313
314
 
# File 'lib/querykit/query.rb', line 312

def skip(value)
  offset(value)
end

#sum(column) ⇒ Object 



335
336
337
 
# File 'lib/querykit/query.rb', line 335

def sum(column)
  select("SUM(#{column}) as sum")
end

#take(value) ⇒ Object

Alias methods for offset and limit



317
318
319
 
# File 'lib/querykit/query.rb', line 317

def take(value)
  limit(value)
end

#to_s ⇒ Object 



441
442
443
 
# File 'lib/querykit/query.rb', line 441

def to_s
  to_sql
end

#to_sql ⇒ Object

Build SQL



359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
 
# File 'lib/querykit/query.rb', line 359

def to_sql
  raise "No table specified" unless @table

  sql = []
  sql << "SELECT"
  sql << "DISTINCT" if @distinct
  sql << (@selects.empty? ? '*' : @selects.join(', '))
  sql << "FROM #{@table}"

  # JOINs
  # JOINs
  @joins.each do |join|
    if join[:type] == 'CROSS'
      sql << "CROSS JOIN #{join[:table]}"
    else
      sql << "#{join[:type]} JOIN #{join[:table]} ON #{join[:first]} #{join[:operator]} #{join[:second]}"
    end
  end

  # WHERE
  unless @wheres.empty?
    sql << "WHERE"
    where_clauses = []
    @wheres.each_with_index do |where, index|
      clause = build_where_clause(where)
      if index == 0
        where_clauses << clause
      else
        where_clauses << "#{where[:boolean]} #{clause}"
      end
    end
    sql << where_clauses.join(' ')
  end

  # GROUP BY
  unless @groups.empty?
    sql << "GROUP BY #{@groups.join(', ')}"
  end

  # HAVING
  unless @havings.empty?
    sql << "HAVING"
    having_clauses = []
    @havings.each_with_index do |having, index|
      clause = "#{having[:column]} #{having[:operator]} ?"
      if index == 0
        having_clauses << clause
      else
        having_clauses << "#{having[:boolean]} #{clause}"
      end
    end
    sql << having_clauses.join(' ')
  end

  # ORDER BY
  unless @orders.empty?
    sql << "ORDER BY #{@orders.map { |o| "#{o[:column]} #{o[:direction]}" }.join(', ')}"
  end

  # LIMIT
  sql << "LIMIT #{@limit_value}" if @limit_value

  # OFFSET
  sql << "OFFSET #{@offset_value}" if @offset_value

  # Build main query
  main_sql = sql.join(' ')

  # UNION / UNION ALL
  unless @unions.empty?
    union_parts = [main_sql]
    @unions.each do |union|
      union_sql = union[:query].is_a?(String) ? union[:query] : union[:query].to_sql
      union_parts << "#{union[:type]} #{union_sql}"
      @bindings.concat(union[:query].bindings) if union[:query].respond_to?(:bindings)
    end
    return union_parts.join(' ')
  end

  main_sql
end

#union(query) ⇒ Object

UNION / UNION ALL



348
349
350
351
 
# File 'lib/querykit/query.rb', line 348

def union(query)
  @unions << { type: 'UNION', query: query }
  self
end

#union_all(query) ⇒ Object 



353
354
355
356
 
# File 'lib/querykit/query.rb', line 353

def union_all(query)
  @unions << { type: 'UNION ALL', query: query }
  self
end

#where(column, value) ⇒ Query #where(column, operator, value) ⇒ Query #where(hash) ⇒ Query

Add a WHERE condition to the query.

Supports multiple calling patterns for flexibility. Values are automatically parameterized for SQL injection protection.

Overloads:

  • #where(column, value) ⇒ Query

    Examples:

    query.where('status', 'active')

    Parameters:

    • column (String) —

      the column name

    • value (Object) —

      the value to compare (assumes = operator)

  • #where(column, operator, value) ⇒ Query

    Examples:

    query.where('age', '>', 18)
query.where('name', 'LIKE', 'John%')

    Parameters:

    • column (String) —

      the column name

    • operator (String) —

      comparison operator (=, >, <, >=, <=, !=, LIKE)

    • value (Object) —

      the value to compare

  • #where(hash) ⇒ Query

    Examples:

    query.where(status: 'active', country: 'USA')

    Parameters:

    • hash (Hash) —

      column-value pairs (all use = operator)

Returns:

  • (Query) —

    self for method chaining



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

def where(column, operator = nil, value = nil)
  # Handle different argument patterns
  if column.is_a?(Hash)
    column.each { |k, v| where(k, '=', v) }
    return self
  end

  if value.nil? && !operator.nil?
    value = operator
    operator = '='
  end

  @wheres << { type: 'basic', column: column, operator: operator, value: value, boolean: 'AND' }
  @bindings << value unless value.nil?
  self
end

#where_between(column, min, max) ⇒ Object

WHERE BETWEEN



205
206
207
208
209
 
# File 'lib/querykit/query.rb', line 205

def where_between(column, min, max)
  @wheres << { type: 'between', column: column, min: min, max: max, boolean: 'AND' }
  @bindings << min << max
  self
end

#where_exists(subquery) ⇒ Object

WHERE EXISTS



219
220
221
222
223
224
 
# File 'lib/querykit/query.rb', line 219

def where_exists(subquery)
  sql = subquery.is_a?(String) ? subquery : subquery.to_sql
  @wheres << { type: 'exists', sql: sql, boolean: 'AND' }
  @bindings.concat(subquery.bindings) if subquery.respond_to?(:bindings)
  self
end

#where_in(column, values) ⇒ Object

WHERE IN clause



179
180
181
182
183
 
# File 'lib/querykit/query.rb', line 179

def where_in(column, values)
  @wheres << { type: 'in', column: column, values: values, boolean: 'AND' }
  @bindings.concat(values)
  self
end

#where_not_exists(subquery) ⇒ Object

WHERE NOT EXISTS



227
228
229
230
231
232
 
# File 'lib/querykit/query.rb', line 227

def where_not_exists(subquery)
  sql = subquery.is_a?(String) ? subquery : subquery.to_sql
  @wheres << { type: 'not_exists', sql: sql, boolean: 'AND' }
  @bindings.concat(subquery.bindings) if subquery.respond_to?(:bindings)
  self
end

#where_not_in(column, values) ⇒ Object

WHERE NOT IN clause



186
187
188
189
190
 
# File 'lib/querykit/query.rb', line 186

def where_not_in(column, values)
  @wheres << { type: 'not_in', column: column, values: values, boolean: 'AND' }
  @bindings.concat(values)
  self
end

#where_not_null(column) ⇒ Object

WHERE IS NOT NULL



199
200
201
202
 
# File 'lib/querykit/query.rb', line 199

def where_not_null(column)
  @wheres << { type: 'not_null', column: column, boolean: 'AND' }
  self
end

#where_null(column) ⇒ Object

WHERE IS NULL / IS NOT NULL



193
194
195
196
 
# File 'lib/querykit/query.rb', line 193

def where_null(column)
  @wheres << { type: 'null', column: column, boolean: 'AND' }
  self
end

#where_raw(sql, *bindings) ⇒ Object

Raw WHERE clause



212
213
214
215
216
 
# File 'lib/querykit/query.rb', line 212

def where_raw(sql, *bindings)
  @wheres << { type: 'raw', sql: sql, boolean: 'AND' }
  @bindings.concat(bindings)
  self
end