These methods make it easier to create Sequel
expressions without using the core extensions.
Methods
Public Instance
Public Instance methods
Create an SQL::AliasedExpression
for the given expression and alias.
Sequel.as(:column, :alias) # "column" AS "alias" Sequel.as(:column, :alias, [:col_alias1, :col_alias2]) # "column" AS "alias"("col_alias1", "col_alias2")
# File lib/sequel/sql.rb 329 def as(exp, aliaz, columns=nil) 330 SQL::AliasedExpression.new(exp, aliaz, columns) 331 end
Order the given argument ascending. Options:
:nulls |
Set to :first to use NULLS FIRST (so NULL values are ordered before other values), or :last to use NULLS LAST (so NULL values are ordered after other values). |
Sequel.asc(:a) # a ASC Sequel.asc(:b, nulls: :last) # b ASC NULLS LAST
# File lib/sequel/sql.rb 342 def asc(arg, opts=OPTS) 343 SQL::OrderedExpression.new(arg, false, opts) 344 end
Return an SQL::Blob
that holds the same data as this string. Blobs provide proper escaping of binary data. If given a blob, returns it directly.
# File lib/sequel/sql.rb 349 def blob(s) 350 if s.is_a?(SQL::Blob) 351 s 352 else 353 SQL::Blob.new(s) 354 end 355 end
Return an SQL::CaseExpression
created with the given arguments. The first argument are the WHEN
/THEN
conditions, specified as an array or a hash. The second argument is the ELSE
default value. The third optional argument is the CASE
expression.
Sequel.case({a: 1}, 0) # SQL: CASE WHEN a THEN 1 ELSE 0 END Sequel.case({a: 1}, 0, :b) # SQL: CASE b WHEN a THEN 1 ELSE 0 END Sequel.case({{a: [2,3]} => 1}, 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END Sequel.case([[{a: [2,3]}, 1]], 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END
# File lib/sequel/sql.rb 367 def case(*args) 368 SQL::CaseExpression.new(*args) 369 end
Cast
the reciever to the given SQL
type. You can specify a ruby class as a type, and it is handled similarly to using a database independent type in the schema methods.
Sequel.cast(:a, :integer) # CAST(a AS integer) Sequel.cast(:a, String) # CAST(a AS varchar(255))
# File lib/sequel/sql.rb 376 def cast(arg, sql_type) 377 SQL::Cast.new(arg, sql_type) 378 end
Cast
the reciever to the given SQL
type (or the database’s default Integer type if none given), and return the result as a NumericExpression
, so you can use the bitwise operators on the result.
Sequel.cast_numeric(:a) # CAST(a AS integer) Sequel.cast_numeric(:a, Float) # CAST(a AS double precision)
# File lib/sequel/sql.rb 386 def cast_numeric(arg, sql_type = nil) 387 cast(arg, sql_type || Integer).sql_number 388 end
Cast
the reciever to the given SQL
type (or the database’s default String type if none given), and return the result as a StringExpression
, so you can use + directly on the result for SQL
string concatenation.
Sequel.cast_string(:a) # CAST(a AS varchar(255)) Sequel.cast_string(:a, :text) # CAST(a AS text)
# File lib/sequel/sql.rb 396 def cast_string(arg, sql_type = nil) 397 cast(arg, sql_type || String).sql_string 398 end
Return an emulated function call for getting the number of characters in the argument:
Sequel.char_length(:a) # char_length(a) -- Most databases Sequel.char_length(:a) # length(a) -- SQLite
# File lib/sequel/sql.rb 405 def char_length(arg) 406 SQL::Function.new!(:char_length, [arg], :emulate=>true) 407 end
Do a deep qualification of the argument using the qualifier. This recurses into nested structures.
Sequel.deep_qualify(:table, :column) # "table"."column" Sequel.deep_qualify(:table, Sequel[:column] + 1) # "table"."column" + 1 Sequel.deep_qualify(:table, Sequel[:a].like('b')) # "table"."a" LIKE 'b' ESCAPE '\'
# File lib/sequel/sql.rb 415 def deep_qualify(qualifier, expr) 416 Sequel::Qualifier.new(qualifier).transform(expr) 417 end
Return a delayed evaluation that uses the passed block. This is used to delay evaluations of the code to runtime. For example, with the following code:
ds = DB[:table].where{column > Time.now}
The filter is fixed to the time that where was called. Unless you are only using the dataset once immediately after creating it, that’s probably not desired. If you just want to set it to the time when the query is sent to the database, you can wrap it in Sequel.delay:
ds = DB[:table].where{column > Sequel.delay{Time.now}}
Note that for dates and timestamps, you are probably better off using Sequel::CURRENT_DATE and Sequel::CURRENT_TIMESTAMP instead of this generic delayed evaluation facility.
# File lib/sequel/sql.rb 435 def delay(&block) 436 raise(Error, "Sequel.delay requires a block") unless block 437 SQL::DelayedEvaluation.new(block) 438 end
Order the given argument descending. Options:
:nulls |
Set to :first to use NULLS FIRST (so NULL values are ordered before other values), or :last to use NULLS LAST (so NULL values are ordered after other values). |
Sequel.desc(:a) # b DESC Sequel.desc(:b, nulls: :first) # b DESC NULLS FIRST
# File lib/sequel/sql.rb 449 def desc(arg, opts=OPTS) 450 SQL::OrderedExpression.new(arg, true, opts) 451 end
Wraps the given object in an appropriate Sequel
wrapper. If the given object is already a Sequel
object, return it directly. For condition specifiers (hashes and arrays of two pairs), true, and false, return a boolean expressions. For numeric objects, return a numeric expression. For strings, return a string expression. For procs or when the method is passed a block, evaluate it as a virtual row and wrap it appropriately. In all other cases, use a generic wrapper.
This method allows you to construct SQL
expressions that are difficult to construct via other methods. For example:
Sequel.expr(1) - :a # SQL: (1 - a)
On the Sequel
module, this is aliased as [], for easier use:
Sequel[1] - :a # SQL: (1 - a)
# File lib/sequel/sql.rb 469 def expr(arg=(no_arg=true), &block) 470 if defined?(yield) 471 if no_arg 472 return expr(block) 473 else 474 raise Error, 'cannot provide both an argument and a block to Sequel.expr' 475 end 476 elsif no_arg 477 raise Error, 'must provide either an argument or a block to Sequel.expr' 478 end 479 480 case arg 481 when Symbol 482 t, c, a = Sequel.split_symbol(arg) 483 484 arg = if t 485 SQL::QualifiedIdentifier.new(t, c) 486 else 487 SQL::Identifier.new(c) 488 end 489 490 if a 491 arg = SQL::AliasedExpression.new(arg, a) 492 end 493 494 arg 495 when SQL::Expression, LiteralString, SQL::Blob 496 arg 497 when Hash 498 SQL::BooleanExpression.from_value_pairs(arg, :AND) 499 when Array 500 if condition_specifier?(arg) 501 SQL::BooleanExpression.from_value_pairs(arg, :AND) 502 else 503 SQL::Wrapper.new(arg) 504 end 505 when Numeric 506 SQL::NumericExpression.new(:NOOP, arg) 507 when String 508 SQL::StringExpression.new(:NOOP, arg) 509 when TrueClass, FalseClass 510 SQL::BooleanExpression.new(:NOOP, arg) 511 when Proc 512 expr(virtual_row(&arg)) 513 else 514 SQL::Wrapper.new(arg) 515 end 516 end
Extract a datetime_part (e.g. year, month) from the given expression:
Sequel.extract(:year, :date) # extract(year FROM "date")
# File lib/sequel/sql.rb 522 def extract(datetime_part, exp) 523 SQL::NumericExpression.new(:extract, datetime_part, exp) 524 end
Returns a Sequel::SQL::Function
with the function name and the given arguments.
Sequel.function(:now) # SQL: now() Sequel.function(:substr, :a, 1) # SQL: substr(a, 1)
# File lib/sequel/sql.rb 531 def function(name, *args) 532 SQL::Function.new(name, *args) 533 end
Return the argument wrapped as an SQL::Identifier
.
Sequel.identifier(:a) # "a"
# File lib/sequel/sql.rb 538 def identifier(name) 539 SQL::Identifier.new(name) 540 end
Create a BooleanExpression
case insensitive (if the database supports it) pattern match of the receiver with the given patterns. See SQL::StringExpression.like
.
Sequel.ilike(:a, 'A%') # "a" ILIKE 'A%' ESCAPE '\'
# File lib/sequel/sql.rb 575 def ilike(*args) 576 SQL::StringExpression.like(*(args << {:case_insensitive=>true})) 577 end
Return a Sequel::SQL::StringExpression
representing an SQL
string made up of the concatenation of the given array’s elements. If an argument is passed, it is used in between each element of the array in the SQL
concatenation.
Sequel.join([:a]) # SQL: a Sequel.join([:a, :b]) # SQL: a || b Sequel.join([:a, 'b']) # SQL: a || 'b' Sequel.join(['a', :b], ' ') # SQL: 'a' || ' ' || b
# File lib/sequel/sql.rb 551 def join(args, joiner=nil) 552 raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array) 553 if joiner 554 args = args.zip([joiner]*args.length).flatten 555 args.pop 556 end 557 558 return SQL::StringExpression.new(:NOOP, '') if args.empty? 559 560 args = args.map do |a| 561 case a 562 when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass 563 a 564 else 565 a.to_s 566 end 567 end 568 SQL::StringExpression.new(:'||', *args) 569 end
Create a SQL::BooleanExpression
case sensitive (if the database supports it) pattern match of the receiver with the given patterns. See SQL::StringExpression.like
.
Sequel.like(:a, 'A%') # "a" LIKE 'A%' ESCAPE '\'
# File lib/sequel/sql.rb 583 def like(*args) 584 SQL::StringExpression.like(*args) 585 end
Converts a string into a Sequel::LiteralString
, in order to override string literalization, e.g.:
DB[:items].where(abc: 'def').sql #=> "SELECT * FROM items WHERE (abc = 'def')" DB[:items].where(abc: Sequel.lit('def')).sql #=> "SELECT * FROM items WHERE (abc = def)"
You can also provide arguments, to create a Sequel::SQL::PlaceholderLiteralString
:
DB[:items].select{|o| o.count(Sequel.lit('DISTINCT ?', :a))}.sql #=> "SELECT count(DISTINCT a) FROM items"
# File lib/sequel/sql.rb 600 def lit(s, *args) 601 if args.empty? 602 if s.is_a?(LiteralString) 603 s 604 else 605 LiteralString.new(s) 606 end 607 else 608 SQL::PlaceholderLiteralString.new(s, args) 609 end 610 end
Return a Sequel::SQL::BooleanExpression
created from the condition specifier, matching none of the conditions.
Sequel.negate(a: true) # SQL: a IS NOT TRUE Sequel.negate([[:a, true]]) # SQL: a IS NOT TRUE Sequel.negate([[:a, 1], [:b, 2]]) # SQL: ((a != 1) AND (b != 2))
# File lib/sequel/sql.rb 618 def negate(arg) 619 if condition_specifier?(arg) 620 SQL::BooleanExpression.from_value_pairs(arg, :AND, true) 621 else 622 raise Error, 'must pass a conditions specifier to Sequel.negate' 623 end 624 end
Return a Sequel::SQL::BooleanExpression
created from the condition specifier, matching any of the conditions.
Sequel.or(a: true) # SQL: a IS TRUE Sequel.or([[:a, true]]) # SQL: a IS TRUE Sequel.or([[:a, 1], [:b, 2]]) # SQL: ((a = 1) OR (b = 2))
# File lib/sequel/sql.rb 632 def or(arg) 633 if condition_specifier?(arg) 634 SQL::BooleanExpression.from_value_pairs(arg, :OR, false) 635 else 636 raise Error, 'must pass a conditions specifier to Sequel.or' 637 end 638 end
Create a qualified identifier with the given qualifier and identifier
Sequel.qualify(:table, :column) # "table"."column" Sequel.qualify(:schema, :table) # "schema"."table" Sequel.qualify(:table, :column).qualify(:schema) # "schema"."table"."column"
# File lib/sequel/sql.rb 645 def qualify(qualifier, identifier) 646 SQL::QualifiedIdentifier.new(qualifier, identifier) 647 end
Return an SQL::Subscript
with the given arguments, representing an SQL
array access.
Sequel.subscript(:array, 1) # array[1] Sequel.subscript(:array, 1, 2) # array[1, 2] Sequel.subscript(:array, [1, 2]) # array[1, 2] Sequel.subscript(:array, 1..2) # array[1:2] Sequel.subscript(:array, 1...3) # array[1:2]
# File lib/sequel/sql.rb 657 def subscript(exp, *subs) 658 SQL::Subscript.new(exp, subs.flatten) 659 end
Return an emulated function call for trimming a string of spaces from both sides (similar to ruby’s String#strip).
Sequel.trim(:a) # trim(a) -- Most databases Sequel.trim(:a) # ltrim(rtrim(a)) -- Microsoft SQL Server
# File lib/sequel/sql.rb 666 def trim(arg) 667 SQL::Function.new!(:trim, [arg], :emulate=>true) 668 end
Return a SQL::ValueList
created from the given array. Used if the array contains all two element arrays and you want it treated as an SQL
value list (IN predicate) instead of as a conditions specifier (similar to a hash). This is not necessary if you are using this array as a value in a filter, but may be necessary if you are using it as a value with placeholder SQL:
DB[:a].where([:a, :b]=>[[1, 2], [3, 4]]) # SQL: (a, b) IN ((1, 2), (3, 4)) DB[:a].where('(a, b) IN ?', [[1, 2], [3, 4]]) # SQL: (a, b) IN ((1 = 2) AND (3 = 4)) DB[:a].where('(a, b) IN ?', Sequel.value_list([[1, 2], [3, 4]])) # SQL: (a, b) IN ((1, 2), (3, 4))
# File lib/sequel/sql.rb 679 def value_list(arg) 680 raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array) 681 SQL::ValueList.new(arg) 682 end