Class: SQB::Query

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

Constant Summary collapse

VALID_ORDERS = 
['ASC', 'DESC']

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(table_name, options = {}, &escape_block) ⇒ Query

Returns a new instance of Query.



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
# File 'lib/sqb/query.rb', line 10

def initialize(table_name, options = {}, &escape_block)
  @table_name = table_name
  @columns = []
  @joins = []
  @joins_name_mapping = {}
  @where = []
  @orders = []
  @groups = []
  @limit = nil
  @offset = nil
  @distinct = false
  @where_within_or = []
  @options = options
  @prepared_arguments = []

  if @options[:prepared] == false && escape_block.nil?
    raise EscapeBlockMissingError, "An escape block must be provided if prepared statements are disabled."
  else
    @escape_block = escape_block
  end
end

Instance Attribute Details

#prepared_arguments ⇒ Object (readonly)

Returns the value of attribute prepared_arguments.



6
7
8
 
# File 'lib/sqb/query.rb', line 6

def prepared_arguments
  @prepared_arguments
end

Instance Method Details

#column(column, options = {}) ⇒ Query

Add a column to the query

Parameters:

  • column (String, Symbol, Hash) —

    the column name (or a hash with table & column name)

  • options (Hash) (defaults to: {}) —

    a customizable set of options

Options Hash (options):

  • :function (String) —

    a function to wrap around the column

Returns:

  • (Query) —

    returns the query



82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/sqb/query.rb', line 82

def column(column, options = {})
  with_table_and_column(column) do |table, column|
    @columns << [].tap do |query|
      if options[:function]
        query << "#{escape_function(options[:function])}("
      end
      query << column_tuple(table, column)
      if options[:function]
        query << ")"
      end
      if options[:as]
        query << "AS"
        query << escape(options[:as])
      end
    end.join(' ')
  end
  self
end

#column!(*args) ⇒ Object

Replace all existing columns with the given column



102
103
104
105
 
# File 'lib/sqb/query.rb', line 102

def column!(*args)
  @columns = []
  column(*args)
end

#distinct ⇒ Object 



255
256
257
258
 
# File 'lib/sqb/query.rb', line 255

def distinct
  @distinct = true
  self
end

#group_by(column) ⇒ Query

Add a grouping

Parameters:

  • column (String, Symbol, Hash)

Returns:



200
201
202
203
204
205
 
# File 'lib/sqb/query.rb', line 200

def group_by(column)
  with_table_and_column(column) do |table, column|
    @groups << column_tuple(table, column)
  end
  self
end

#join(table_name, foreign_key, options = {}) ⇒ Query

Add a join

Parameters:

  • table_name (String, Symbol)
  • foreign_key (String, Symbol)
  • options (Hash) (defaults to: {}) —

    a customizable set of options

Options Hash (options):

  • :where (Hash)
  • :select (Array)

Returns:



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
 
# File 'lib/sqb/query.rb', line 214

def join(table_name, foreign_key, options = {})

  if options[:name]
    join_name = options[:name]
  else
    @joins_name_mapping[table_name] ||= 0
    join_name= "#{table_name}_#{@joins_name_mapping[table_name]}"
    @joins_name_mapping[table_name] += 1
  end

  @joins << [].tap do |query|
    query << "INNER JOIN"
    query << escape(table_name)
    query << "AS"
    query << escape(join_name)
    query << "ON"
    query << column_tuple(@table_name, 'id')
    query << "="
    query << column_tuple(join_name, foreign_key)
  end.join(' ')

  if options[:where]
    join_where = options[:where].each_with_object({}) do |(column, value), hash|
      hash[{join_name => column}] = value
    end
    where(join_where)
  end

  if columns = options[:columns]
    for field in columns
      column({join_name => field}, :as => "#{join_name}_#{field}")
    end
  end

  if g = options[:group_by]
    group_by(join_name => g.is_a?(Symbol) ? g : :id)
  end

  self
end

#limit(number) ⇒ Query

Limit the number of records return

Parameters:

  • number (Integer)

Returns:



152
153
154
155
 
# File 'lib/sqb/query.rb', line 152

def limit(number)
  @limit = number&.to_i
  self
end

#no_order! ⇒ Object

Remove all ordering for this query



192
193
194
 
# File 'lib/sqb/query.rb', line 192

def no_order!
  @orders = []
end

#offset(number) ⇒ Query

Set the offset

Parameters:

  • number (Integer)

Returns:



161
162
163
164
 
# File 'lib/sqb/query.rb', line 161

def offset(number)
  @offset = number&.to_i
  self
end

#or(&block) ⇒ Object

Set that all conditions added in this block should be joined using OR rather than AND.



122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/sqb/query.rb', line 122

def or(&block)
  # Start by making an array within the OR block for this calling
  @where_within_or << []
  # Execute the block. All queries to 'where' will be added to the last
  # array in the chain (created above)
  block.call
ensure
  # Start work on a full array of SQL fragments for all OR queries
  @where_within_or_sql ||= []
  # After each OR call, store up the SQL fragment for all where queries
  # executed within the block.
  if w = @where_within_or.pop
    @where_within_or_sql << w.map do |w|
      hash_to_sql(w, @table_name)
    end.join(' OR ')
  end

  # When there are no fragments in the chain left, add it to the main
  # where chain for the query.
  if @where_within_or.empty?
    @where << "(#{@where_within_or_sql.flatten.join(' OR ')})"
    @where_within_or_sql = nil
  end
  self
end

#order(column, direction = nil) ⇒ Query

Add an order column

Parameters:

  • column (String, Symbol, Hash)
  • direction (String) (defaults to: nil) —

    ‘ASC’ or ‘DESC’ (default ‘ASC’)

Returns:



171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/sqb/query.rb', line 171

def order(column, direction = nil)
  direction = direction ? direction.to_s.upcase : 'ASC'

  unless VALID_ORDERS.include?(direction)
    raise InvalidOrderDirectionError, "Invalid order direction #{direction}"
  end

  with_table_and_column(column) do |table, column|
    @orders << [column_tuple(table, column), direction].join(' ')
  end

  self
end

#order!(*args) ⇒ Object

Add an order replacing all previous ones



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

def order!(*args)
  @orders = []
  order(*args)
end

#to_sql ⇒ String

Generate the full SQL query for this query.

Returns:

  • (String) 


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
 
# File 'lib/sqb/query.rb', line 35

def to_sql
  [].tap do |query|
    query << "SELECT"
    query << "DISTINCT" if @distinct
    if @columns.empty?
      query << column_tuple(@table_name, '*')
    else
      query << @columns.join(', ')
    end
    query << "FROM"
    query << escape(@table_name)

    unless @joins.empty?
      query << @joins.join(' ')
    end

    unless @where.empty?
      query << "WHERE"
      query << @where.join(' AND ')
    end

    unless @groups.empty?
      query << "GROUP BY"
      query << @groups.join(', ')
    end

    unless @orders.empty?
      query << "ORDER BY"
      query << @orders.join(', ')
    end

    if @limit
      query << "LIMIT #{@limit.to_i}"
    end

    if @offset
      query << "OFFSET #{@offset.to_i}"
    end
  end.join(' ')
end

#where(hash) ⇒ Query

Add a condition to the query by providing a hash of keys and values.

Parameters:

  • hash (Hash)

Returns:



111
112
113
114
115
116
117
118
 
# File 'lib/sqb/query.rb', line 111

def where(hash)
  if @where_within_or.last
    @where_within_or.last << hash
  else
    @where << hash_to_sql(hash, @table_name)
  end
  self
end