Class: SQLStatement::Select

Inherits:
Object show all
Defined in:
lib/sql/statement.rb

Overview

This class is used to represent a SELECT statement, or an incomplete set of parts to add to a SELECT statement. If we wanted to generate SQL code similar to

SELECT `value1`,`value2`
FROM `jointable`,`dictionary1`,`dictionary2`
WHERE `jointable`.`id1`=`dictionary1`.`id` AND `jointable`.`id2`=`dictionary2`.`id`

then it may make sense for your application to generate the dictionary1 code and the dictionary2 code separately, in different Select objects, then combine them into one Select.

def foo(x)
   Select.new do |s|
     s.field :"value#{x}"
     s.table :"dictionary#{x}"
     s.condition string_func("`jointable`.`id#{x}`=`dictionary#{x}`.`id`")
   end
end

stmt=Select.new
stmt.table :jointable
(1..2).each do |x|
   stmt << foo(x)
end
dbh.execute(stmt)

Direct Known Subclasses

SelectCreate, SelectInsert, Update

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize {|_self| ... } ⇒ Select

Initializes the various pieces of the SQL statement to values specified in the hash. Use Symbols corresponding to the attribute names to set an attribute here, otherwise a default empty array or hash will be used.

The block is a convenient way to scpe the creation of the whole statement into one block.

Yields:

  • (_self)

Yield Parameters:



169
170
171
172
173
174
175
176
177
178
179
180
181
 
# File 'lib/sql/statement.rb', line 169

def initialize
  @field_names=[]
  @field_aliases=[]
  @table_names=[]
  @table_aliases=[]
  @conditions=[]
  @groupby_fields=[]
  @orderby_fields=[]
  @leftjoin_alias=[]
  @leftjoin_table=[]
  @leftjoin_condition=[]
  yield self if block_given?
end

Instance Attribute Details

#distinct ⇒ Object

Select whether this is a SELECT statement or a SELECT DISTINCT statement. (non-distinct by default)



254
255
256
 
# File 'lib/sql/statement.rb', line 254

def distinct
  @distinct
end

#straight_join ⇒ Object

Determine whether to use MySQL’s STRAIGHT_JOIN modifier to override the query optimizer



257
258
259
 
# File 'lib/sql/statement.rb', line 257

def straight_join
  @straight_join
end

Class Method Details

.newlists(*lists) ⇒ Object

Use this to define additional array attributes in descendant classes with all of the necessary addition, initialization and merging (<<) semantics. You can only call this function once per class, as it redefines methods each time it is called.



367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
 
# File 'lib/sql/statement.rb', line 367

def newlists *lists
	 attr_accessor *lists

 	 lines=lists.collect do |listname|
	    ["@#{listname}=[]",
       "@#{listname} += parts.#{listname}"]
	 end.transpose
	 class_eval <<-"end;"
	    def initialize &block
  #{lines[0].join("\n")}
  super &block
	    end
	    def << (parts)
  super
  #{lines[1].join("\n")}
  self
	    end
	 end;
end

Instance Method Details

#+(rhs) ⇒ Object

Merge several SelectParts objects into one.



237
238
239
240
 
# File 'lib/sql/statement.rb', line 237

def +(rhs)
   b=self.dup
   b << rhs
end

#<<(parts) ⇒ Object

Merges the various parts of a SelectParts into the correct places in this SQL statement.



264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
 
# File 'lib/sql/statement.rb', line 264

def << (parts)
   parts.table_names.zip(parts.table_aliases).each do |name,thealias|
     next if @table_names.zip(@table_aliases).include? [name,thealias]
     @table_names << name
     @table_aliases << thealias
   end
   parts.leftjoin_alias.zip(parts.leftjoin_table,parts.leftjoin_condition).each \
       do |thealias,thetable,thecondition|
     next if @leftjoin_alias.zip(@leftjoin_table).include? [thealias,thetable]
     @leftjoin_alias << thealias
     @leftjoin_table << thetable
     @leftjoin_condition << thecondition
   end
   @field_names += parts.field_names
   @field_aliases += parts.field_aliases
   @conditions += parts.conditions
   @groupby_fields += parts.groupby_fields
   @orderby_fields += parts.orderby_fields
   self
end

#allfields ⇒ Object

Returns two lists: a list of field names (aliases) in the expression, and a list of field values. If you overriding this class (or any of its subclasses) to constructing other fields from other lists, override this function, call super, construct the additional fields here, and add them to the result of super. Then return the results.



248
249
250
 
# File 'lib/sql/statement.rb', line 248

def allfields
   [@field_aliases.dup, @field_names.dup]
end

#condition(c) ⇒ Object

Add a condition to the WHERE clause of the SQL statment. Conditions will be joined by AND. This should either be an array representing an s-expression for the condition, or it should be a SQLStatement::Function containing a string for the condition.



187
188
189
190
 
# File 'lib/sql/statement.rb', line 187

def condition c
  @conditions << c
  self
end

#field(thefield, thealias = nil) ⇒ Object

Add a field or expression to be retrieved in the expression. In a SELECT statement, this appears immediately after the SELECT keyword. In an UPDATE statement, this appears after the SET keyword. (And in other places for other kinds of queries. The SQL inventors were nothing if not consistent.)

thefield is a field or expression to put in the result of this Select statement thealias is a name to use for the column, and can be omitted to use the default



209
210
211
212
 
# File 'lib/sql/statement.rb', line 209

def field thefield, thealias=nil
  @field_names << thefield
  @field_aliases << thealias
end

#groupby(c) ⇒ Object 



191
192
193
194
 
# File 'lib/sql/statement.rb', line 191

def groupby c
  @groupby_fields << c
  self
end

#leftjoin(table, condition, the_alias = nil) ⇒ Object

Adds a left-join with join conditon



226
227
228
229
230
231
 
# File 'lib/sql/statement.rb', line 226

def leftjoin table, condition, the_alias=nil
  return if @leftjoin_table.zip(@leftjoin_alias).include? [table,the_alias]
   @leftjoin_alias << the_alias
   @leftjoin_table << table
   @leftjoin_condition << condition
end

#orderby(c) ⇒ Object 



195
196
197
198
 
# File 'lib/sql/statement.rb', line 195

def orderby c
  @orderby_fields << c
  self
end

#placeheld ⇒ Object 



301
302
303
304
305
306
307
308
309
310
 
# File 'lib/sql/statement.rb', line 301

def placeheld
   (
     allfields[1] +
     @table_names +
     @leftjoin_table.zip(@leftjoin_alias,@leftjoin_condition).flatten +
     conditions +
     groupby_fields +
     orderby_fields
   ).collect{|x| x.placeheld}.flatten
end

#table(thetable, thealias = nil) ⇒ Object

Add a table to join into the expression, either as the first table, or by using an inner join

thefield is a field or expression to put in the result of this Select statement thealias is a name to use for the column, and can be omitted to use the default

If the table/alias pair is already included in the query, it is not included again, but if a table can be included again by using a new alias



220
221
222
223
224
 
# File 'lib/sql/statement.rb', line 220

def table thetable, thealias=nil
  return if @table_names.zip(@table_aliases).include? [thetable,thealias]
  @table_names << thetable
  @table_aliases << thealias
end

#to_s ⇒ Object

Returns the SQL string that you would actually want to execute on the database. it may contain placeholders for actual data. Those actual data can be retrieved with the placeheld method, and used in a manner similar to

dbh.execute(s.to_s,*s.placeheld)



289
290
291
292
293
294
295
296
297
298
299
 
# File 'lib/sql/statement.rb', line 289

def to_s
   statement="SELECT"
   statement << " DISTINCT" if distinct 
   statement << " STRAIGHT_JOIN" if straight_join
   statement << "\n " << fields_s
   statement << "\nFROM " << tables_s
   v=conditions_s; statement << "\nWHERE "<< v if v
   v=groupby_s; statement << "\nGROUP BY "<< v if v
   v=orderby_s; statement << "\nORDER BY "<< v if v
   return statement
end

#to_sqlpart ⇒ Object

This is useful for writing nested queries.



313
314
315
 
# File 'lib/sql/statement.rb', line 313

def to_sqlpart
   "("+to_s+")"
end