These methods make it easier to create Sequel expressions without using the core extensions.
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, line 314 314: def as(exp, aliaz, columns=nil) 315: SQL::AliasedExpression.new(exp, aliaz, columns) 316: 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, line 327 327: def asc(arg, opts=OPTS) 328: SQL::OrderedExpression.new(arg, false, opts) 329: end
Return an SQL::CaseExpression created with the given arguments.
Sequel.case([[{a: [2,3]}, 1]], 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END Sequel.case({a: 1}, 0, :b) # SQL: CASE b WHEN a THEN 1 ELSE 0 END
# File lib/sequel/sql.rb, line 346 346: def case(*args) 347: SQL::CaseExpression.new(*args) 348: 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, line 355 355: def cast(arg, sql_type) 356: SQL::Cast.new(arg, sql_type) 357: 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, line 365 365: def cast_numeric(arg, sql_type = nil) 366: cast(arg, sql_type || Integer).sql_number 367: 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, line 375 375: def cast_string(arg, sql_type = nil) 376: cast(arg, sql_type || String).sql_string 377: 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, line 384 384: def char_length(arg) 385: SQL::Function.new!(:char_length, [arg], :emulate=>true) 386: end
Return a DateAdd expression, adding the negative of the interval to the date/timestamp expr.
# File lib/sequel/extensions/date_arithmetic.rb, line 42 42: def date_sub(expr, interval) 43: interval = if interval.is_a?(Hash) 44: h = {} 45: interval.each{|k,v| h[k] = -v unless v.nil?} 46: h 47: else 48: -interval 49: end 50: DateAdd.new(expr, interval) 51: 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, line 394 394: def deep_qualify(qualifier, expr) 395: Sequel::Qualifier.new(qualifier).transform(expr) 396: 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, line 414 414: def delay(&block) 415: raise(Error, "Sequel.delay requires a block") unless block 416: SQL::DelayedEvaluation.new(block) 417: 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, line 428 428: def desc(arg, opts=OPTS) 429: SQL::OrderedExpression.new(arg, true, opts) 430: 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, line 448 448: def expr(arg=(no_arg=true), &block) 449: if block_given? 450: if no_arg 451: return expr(block) 452: else 453: raise Error, 'cannot provide both an argument and a block to Sequel.expr' 454: end 455: elsif no_arg 456: raise Error, 'must provide either an argument or a block to Sequel.expr' 457: end 458: 459: case arg 460: when Symbol 461: t, c, a = Sequel.split_symbol(arg) 462: 463: arg = if t 464: SQL::QualifiedIdentifier.new(t, c) 465: else 466: SQL::Identifier.new(c) 467: end 468: 469: if a 470: arg = SQL::AliasedExpression.new(arg, a) 471: end 472: 473: arg 474: when SQL::Expression, LiteralString, SQL::Blob 475: arg 476: when Hash 477: SQL::BooleanExpression.from_value_pairs(arg, :AND) 478: when Array 479: if condition_specifier?(arg) 480: SQL::BooleanExpression.from_value_pairs(arg, :AND) 481: else 482: SQL::Wrapper.new(arg) 483: end 484: when Numeric 485: SQL::NumericExpression.new(:NOOP, arg) 486: when String 487: SQL::StringExpression.new(:NOOP, arg) 488: when TrueClass, FalseClass 489: SQL::BooleanExpression.new(:NOOP, arg) 490: when Proc 491: expr(virtual_row(&arg)) 492: else 493: SQL::Wrapper.new(arg) 494: end 495: 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, line 501 501: def extract(datetime_part, exp) 502: SQL::NumericExpression.new(:extract, datetime_part, exp) 503: 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, line 510 510: def function(name, *args) 511: SQL::Function.new(name, *args) 512: end
Return a Postgres::HStore proxy for the given hash.
# File lib/sequel/extensions/pg_hstore.rb, line 292 292: def hstore(v) 293: case v 294: when Postgres::HStore 295: v 296: when Hash 297: Postgres::HStore.new(v) 298: else 299: # May not be defined unless the pg_hstore_ops extension is used 300: hstore_op(v) 301: end 302: end
Return the object wrapped in an Postgres::HStoreOp.
# File lib/sequel/extensions/pg_hstore_ops.rb, line 328 328: def hstore_op(v) 329: case v 330: when Postgres::HStoreOp 331: v 332: else 333: Postgres::HStoreOp.new(v) 334: end 335: end
Return the argument wrapped as an SQL::Identifier.
Sequel.identifier(:a) # "a"
# File lib/sequel/sql.rb, line 517 517: def identifier(name) 518: SQL::Identifier.new(name) 519: 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, line 554 554: def ilike(*args) 555: SQL::StringExpression.like(*(args << {:case_insensitive=>true})) 556: 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, line 530 530: def join(args, joiner=nil) 531: raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array) 532: if joiner 533: args = args.zip([joiner]*args.length).flatten 534: args.pop 535: end 536: 537: return SQL::StringExpression.new(:NOOP, '') if args.empty? 538: 539: args = args.map do |a| 540: case a 541: when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass 542: a 543: else 544: a.to_s 545: end 546: end 547: SQL::StringExpression.new('||''||', *args) 548: 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, line 562 562: def like(*args) 563: SQL::StringExpression.like(*args) 564: 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, line 579 579: def lit(s, *args) 580: if args.empty? 581: if s.is_a?(LiteralString) 582: s 583: else 584: LiteralString.new(s) 585: end 586: else 587: SQL::PlaceholderLiteralString.new(s, args) 588: end 589: 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, line 597 597: def negate(arg) 598: if condition_specifier?(arg) 599: SQL::BooleanExpression.from_value_pairs(arg, :AND, true) 600: else 601: raise Error, 'must pass a conditions specifier to Sequel.negate' 602: end 603: 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, line 611 611: def or(arg) 612: if condition_specifier?(arg) 613: SQL::BooleanExpression.from_value_pairs(arg, :OR, false) 614: else 615: raise Error, 'must pass a conditions specifier to Sequel.or' 616: end 617: end
Return a Postgres::PGArray proxy for the given array and database array type.
# File lib/sequel/extensions/pg_array.rb, line 465 465: def pg_array(v, array_type=nil) 466: case v 467: when Postgres::PGArray 468: if array_type.nil? || v.array_type == array_type 469: v 470: else 471: Postgres::PGArray.new(v.to_a, array_type) 472: end 473: when Array 474: Postgres::PGArray.new(v, array_type) 475: else 476: # May not be defined unless the pg_array_ops extension is used 477: pg_array_op(v) 478: end 479: end
Return the object wrapped in an Postgres::ArrayOp.
# File lib/sequel/extensions/pg_array_ops.rb, line 295 295: def pg_array_op(v) 296: case v 297: when Postgres::ArrayOp 298: v 299: else 300: Postgres::ArrayOp.new(v) 301: end 302: end
Return the expression wrapped in the Postgres::InetOp.
# File lib/sequel/extensions/pg_inet_ops.rb, line 171 171: def pg_inet_op(v) 172: case v 173: when Postgres::InetOp 174: v 175: else 176: Postgres::InetOp.new(v) 177: end 178: end
Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.
# File lib/sequel/extensions/pg_json.rb, line 261 261: def pg_json(v) 262: case v 263: when Postgres::JSONArray, Postgres::JSONHash 264: v 265: when Array 266: Postgres::JSONArray.new(v) 267: when Hash 268: Postgres::JSONHash.new(v) 269: when Postgres::JSONBArray 270: Postgres::JSONArray.new(v.to_a) 271: when Postgres::JSONBHash 272: Postgres::JSONHash.new(v.to_hash) 273: else 274: Sequel.pg_json_op(v) 275: end 276: end
Return the object wrapped in an Postgres::JSONOp.
# File lib/sequel/extensions/pg_json_ops.rb, line 470 470: def pg_json_op(v) 471: case v 472: when Postgres::JSONOp 473: v 474: else 475: Postgres::JSONOp.new(v) 476: end 477: end
Wrap the array or hash in a Postgres::JSONBArray or Postgres::JSONBHash.
# File lib/sequel/extensions/pg_json.rb, line 279 279: def pg_jsonb(v) 280: case v 281: when Postgres::JSONBArray, Postgres::JSONBHash 282: v 283: when Array 284: Postgres::JSONBArray.new(v) 285: when Hash 286: Postgres::JSONBHash.new(v) 287: when Postgres::JSONArray 288: Postgres::JSONBArray.new(v.to_a) 289: when Postgres::JSONHash 290: Postgres::JSONBHash.new(v.to_hash) 291: else 292: Sequel.pg_jsonb_op(v) 293: end 294: end
Return the object wrapped in an Postgres::JSONBOp.
# File lib/sequel/extensions/pg_json_ops.rb, line 480 480: def pg_jsonb_op(v) 481: case v 482: when Postgres::JSONBOp 483: v 484: else 485: Postgres::JSONBOp.new(v) 486: end 487: end
Convert the object to a Postgres::PGRange.
# File lib/sequel/extensions/pg_range.rb, line 509 509: def pg_range(v, db_type=nil) 510: case v 511: when Postgres::PGRange 512: if db_type.nil? || v.db_type == db_type 513: v 514: else 515: Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type) 516: end 517: when Range 518: Postgres::PGRange.from_range(v, db_type) 519: else 520: # May not be defined unless the pg_range_ops extension is used 521: pg_range_op(v) 522: end 523: end
Return the expression wrapped in the Postgres::RangeOp.
# File lib/sequel/extensions/pg_range_ops.rb, line 132 132: def pg_range_op(v) 133: case v 134: when Postgres::RangeOp 135: v 136: else 137: Postgres::RangeOp.new(v) 138: end 139: end
Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.
# File lib/sequel/extensions/pg_row.rb, line 547 547: def pg_row(expr) 548: case expr 549: when Array 550: Postgres::PGRow::ArrayRow.new(expr) 551: else 552: # Will only work if pg_row_ops extension is loaded 553: pg_row_op(expr) 554: end 555: end
Return a PGRowOp wrapping the given expression.
# File lib/sequel/extensions/pg_row_ops.rb, line 165 165: def pg_row_op(expr) 166: Postgres::PGRowOp.wrap(expr) 167: 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, line 624 624: def qualify(qualifier, identifier) 625: SQL::QualifiedIdentifier.new(qualifier, identifier) 626: 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, line 636 636: def subscript(exp, *subs) 637: SQL::Subscript.new(exp, subs.flatten) 638: 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, line 645 645: def trim(arg) 646: SQL::Function.new!(:trim, [arg], :emulate=>true) 647: 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, line 658 658: def value_list(arg) 659: raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array) 660: SQL::ValueList.new(arg) 661: end