module Sequel::SQL::Builders
These methods make it easier to create Sequel
expressions without using the core extensions.
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
Return a DateAdd
expression, adding the negative of the interval to the date/timestamp expr. Options:
- :cast
-
Cast
to the specified type instead of the default if casting
# File lib/sequel/extensions/date_arithmetic.rb 56 def date_sub(expr, interval, opts=OPTS) 57 if defined?(ActiveSupport::Duration) && interval.is_a?(ActiveSupport::Duration) 58 interval = interval.parts 59 end 60 parts = {} 61 interval.each do |k,v| 62 case v 63 when nil 64 # ignore 65 when Numeric 66 parts[k] = -v 67 else 68 parts[k] = Sequel::SQL::NumericExpression.new(:*, v, -1) 69 end 70 end 71 DateAdd.new(expr, parts, opts) 72 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 a Postgres::HStore
proxy for the given hash.
# File lib/sequel/extensions/pg_hstore.rb 313 def hstore(v) 314 case v 315 when Postgres::HStore 316 v 317 when Hash 318 Postgres::HStore.new(v) 319 else 320 # May not be defined unless the pg_hstore_ops extension is used 321 hstore_op(v) 322 end 323 end
Return the object wrapped in an Postgres::HStoreOp
.
# File lib/sequel/extensions/pg_hstore_ops.rb 380 def hstore_op(v) 381 case v 382 when Postgres::HStoreOp 383 v 384 else 385 Postgres::HStoreOp.new(v) 386 end 387 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 IsDistinctFrom
expression object, using the IS DISTINCT FROM operator with the given left hand side and right hand side.
# File lib/sequel/extensions/is_distinct_from.rb 37 def is_distinct_from(lhs, rhs) 38 BooleanExpression.new(:NOOP, IsDistinctFrom.new(lhs, rhs)) 39 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
Return a Postgres::PGArray
proxy for the given array and database array type.
# File lib/sequel/extensions/pg_array.rb 514 def pg_array(v, array_type=nil) 515 case v 516 when Postgres::PGArray 517 if array_type.nil? || v.array_type == array_type 518 v 519 else 520 Postgres::PGArray.new(v.to_a, array_type) 521 end 522 when Array 523 Postgres::PGArray.new(v, array_type) 524 else 525 # May not be defined unless the pg_array_ops extension is used 526 pg_array_op(v) 527 end 528 end
Return the object wrapped in an Postgres::ArrayOp
.
# File lib/sequel/extensions/pg_array_ops.rb 303 def pg_array_op(v) 304 case v 305 when Postgres::ArrayOp 306 v 307 else 308 Postgres::ArrayOp.new(v) 309 end 310 end
Return the expression wrapped in the Postgres::InetOp
.
# File lib/sequel/extensions/pg_inet_ops.rb 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
. Also handles Postgres::JSONObject
and JSONBObjects. For other objects, calls Sequel.pg_json_op
(which is defined by the pg_json_ops extension).
# File lib/sequel/extensions/pg_json.rb 522 def pg_json(v) 523 case v 524 when Postgres::JSONObject 525 v 526 when Array 527 Postgres::JSONArray.new(v) 528 when Hash 529 Postgres::JSONHash.new(v) 530 when Postgres::JSONBObject 531 v = v.__getobj__ 532 Postgres::JSONDatabaseMethods.json_primitive_wrapper(v).new(v) 533 else 534 Sequel.pg_json_op(v) 535 end 536 end
Return the object wrapped in an Postgres::JSONOp
.
# File lib/sequel/extensions/pg_json_ops.rb 761 def pg_json_op(v) 762 case v 763 when Postgres::JSONOp 764 v 765 else 766 Postgres::JSONOp.new(v) 767 end 768 end
Wraps Ruby array, hash, string, integer, float, true, false, and nil values with the appropriate JSON wrapper. Raises an exception for other types.
# File lib/sequel/extensions/pg_json.rb 541 def pg_json_wrap(v) 542 case v 543 when *Postgres::JSON_WRAP_CLASSES 544 Postgres::JSONDatabaseMethods.json_primitive_wrapper(v).new(v) 545 else 546 raise Error, "invalid value passed to Sequel.pg_json_wrap: #{v.inspect}" 547 end 548 end
Wrap the array or hash in a Postgres::JSONBArray
or Postgres::JSONBHash
. Also handles Postgres::JSONObject
and JSONBObjects. For other objects, calls Sequel.pg_json_op
(which is defined by the pg_json_ops extension).
# File lib/sequel/extensions/pg_json.rb 554 def pg_jsonb(v) 555 case v 556 when Postgres::JSONBObject 557 v 558 when Array 559 Postgres::JSONBArray.new(v) 560 when Hash 561 Postgres::JSONBHash.new(v) 562 when Postgres::JSONObject 563 v = v.__getobj__ 564 Postgres::JSONDatabaseMethods.jsonb_primitive_wrapper(v).new(v) 565 else 566 Sequel.pg_jsonb_op(v) 567 end 568 end
Return the object wrapped in an Postgres::JSONBOp
.
# File lib/sequel/extensions/pg_json_ops.rb 771 def pg_jsonb_op(v) 772 case v 773 when Postgres::JSONBOp 774 v 775 else 776 Postgres::JSONBOp.new(v) 777 end 778 end
Wraps Ruby array, hash, string, integer, float, true, false, and nil values with the appropriate JSONB wrapper. Raises an exception for other types.
# File lib/sequel/extensions/pg_json.rb 573 def pg_jsonb_wrap(v) 574 case v 575 when *Postgres::JSON_WRAP_CLASSES 576 Postgres::JSONDatabaseMethods.jsonb_primitive_wrapper(v).new(v) 577 else 578 raise Error, "invalid value passed to Sequel.pg_jsonb_wrap: #{v.inspect}" 579 end 580 end
Convert the object to a Postgres::PGMultiRange
.
# File lib/sequel/extensions/pg_multirange.rb 349 def pg_multirange(v, db_type) 350 case v 351 when Postgres::PGMultiRange 352 if v.db_type == db_type 353 v 354 else 355 Postgres::PGMultiRange.new(v, db_type) 356 end 357 when Array 358 Postgres::PGMultiRange.new(v, db_type) 359 else 360 # May not be defined unless the pg_range_ops extension is used 361 pg_range_op(v) 362 end 363 end
Convert the object to a Postgres::PGRange
.
# File lib/sequel/extensions/pg_range.rb 520 def pg_range(v, db_type=nil) 521 case v 522 when Postgres::PGRange 523 if db_type.nil? || v.db_type == db_type 524 v 525 else 526 Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type) 527 end 528 when Range 529 Postgres::PGRange.from_range(v, db_type) 530 else 531 # May not be defined unless the pg_range_ops extension is used 532 pg_range_op(v) 533 end 534 end
Return the expression wrapped in the Postgres::RangeOp
.
# File lib/sequel/extensions/pg_range_ops.rb 162 def pg_range_op(v) 163 case v 164 when Postgres::RangeOp 165 v 166 else 167 Postgres::RangeOp.new(v) 168 end 169 end
Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow
instance.
# File lib/sequel/extensions/pg_row.rb 550 def pg_row(expr) 551 case expr 552 when Array 553 Postgres::PGRow::ArrayRow.new(expr) 554 else 555 # Will only work if pg_row_ops extension is loaded 556 pg_row_op(expr) 557 end 558 end
Return a PGRowOp wrapping the given expression.
# File lib/sequel/extensions/pg_row_ops.rb 189 def pg_row_op(expr) 190 Postgres::PGRowOp.wrap(expr) 191 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
Skip auto parameterization for the given object when building queries.
# File lib/sequel/extensions/pg_auto_parameterize.rb 503 def skip_pg_auto_param(v) 504 Postgres::AutoParameterize::SkipAutoParam.new(v) 505 end
Return the object wrapped in an SQLite::JSONOp
.
# File lib/sequel/extensions/sqlite_json_ops.rb 270 def sqlite_json_op(v) 271 case v 272 when SQLite::JSONOp 273 v 274 else 275 SQLite::JSONOp.new(v) 276 end 277 end
Return the object wrapped in an SQLite::JSONBOp
.
# File lib/sequel/extensions/sqlite_json_ops.rb 280 def sqlite_jsonb_op(v) 281 case v 282 when SQLite::JSONBOp 283 v 284 else 285 SQLite::JSONBOp.new(v) 286 end 287 end
Return a StringAgg
expression for an aggregate string concatentation.
# File lib/sequel/extensions/string_agg.rb 65 def string_agg(*a) 66 StringAgg.new(*a) 67 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