module Sequel::SQL::Builders
These methods make it easier to create Sequel
expressions without using the core extensions.
Public Instance Methods
Source
# File lib/sequel/sql.rb 332 def as(exp, aliaz, columns=nil) 333 SQL::AliasedExpression.new(exp, aliaz, columns) 334 end
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")
Source
# File lib/sequel/sql.rb 345 def asc(arg, opts=OPTS) 346 SQL::OrderedExpression.new(arg, false, opts) 347 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
Source
# File lib/sequel/sql.rb 352 def blob(s) 353 if s.is_a?(SQL::Blob) 354 s 355 else 356 SQL::Blob.new(s) 357 end 358 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.
Source
# File lib/sequel/sql.rb 370 def case(*args) 371 SQL::CaseExpression.new(*args) 372 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
Source
# File lib/sequel/sql.rb 379 def cast(arg, sql_type) 380 SQL::Cast.new(arg, sql_type) 381 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))
Source
# File lib/sequel/sql.rb 389 def cast_numeric(arg, sql_type = nil) 390 cast(arg, sql_type || Integer).sql_number 391 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)
Source
# File lib/sequel/sql.rb 399 def cast_string(arg, sql_type = nil) 400 cast(arg, sql_type || String).sql_string 401 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)
Source
# File lib/sequel/sql.rb 408 def char_length(arg) 409 SQL::Function.new!(:char_length, [arg], :emulate=>true) 410 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
Source
Source
# 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
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
Source
# File lib/sequel/sql.rb 418 def deep_qualify(qualifier, expr) 419 Sequel::Qualifier.new(qualifier).transform(expr) 420 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 '\'
Source
# File lib/sequel/sql.rb 438 def delay(&block) 439 raise(Error, "Sequel.delay requires a block") unless block 440 SQL::DelayedEvaluation.new(block) 441 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.
Source
# File lib/sequel/sql.rb 452 def desc(arg, opts=OPTS) 453 SQL::OrderedExpression.new(arg, true, opts) 454 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
Source
# File lib/sequel/sql.rb 472 def expr(arg=(no_arg=true), &block) 473 if defined?(yield) 474 if no_arg 475 return expr(block) 476 else 477 raise Error, 'cannot provide both an argument and a block to Sequel.expr' 478 end 479 elsif no_arg 480 raise Error, 'must provide either an argument or a block to Sequel.expr' 481 end 482 483 case arg 484 when Symbol 485 t, c, a = Sequel.split_symbol(arg) 486 487 arg = if t 488 SQL::QualifiedIdentifier.new(t, c) 489 else 490 SQL::Identifier.new(c) 491 end 492 493 if a 494 arg = SQL::AliasedExpression.new(arg, a) 495 end 496 497 arg 498 when SQL::Expression, LiteralString, SQL::Blob 499 arg 500 when Hash 501 SQL::BooleanExpression.from_value_pairs(arg, :AND) 502 when Array 503 if condition_specifier?(arg) 504 SQL::BooleanExpression.from_value_pairs(arg, :AND) 505 else 506 SQL::Wrapper.new(arg) 507 end 508 when Numeric 509 SQL::NumericExpression.new(:NOOP, arg) 510 when String 511 SQL::StringExpression.new(:NOOP, arg) 512 when TrueClass, FalseClass 513 SQL::BooleanExpression.new(:NOOP, arg) 514 when Proc 515 expr(virtual_row(&arg)) 516 else 517 SQL::Wrapper.new(arg) 518 end 519 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)
Source
# File lib/sequel/sql.rb 525 def extract(datetime_part, exp) 526 SQL::NumericExpression.new(:extract, datetime_part, exp) 527 end
Extract a datetime_part (e.g. year, month) from the given expression:
Sequel.extract(:year, :date) # extract(year FROM "date")
Source
# File lib/sequel/sql.rb 534 def function(name, *args) 535 SQL::Function.new(name, *args) 536 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)
Source
# 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 a Postgres::HStore
proxy for the given hash.
Source
# 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 object wrapped in an Postgres::HStoreOp
.
Source
# File lib/sequel/sql.rb 541 def identifier(name) 542 SQL::Identifier.new(name) 543 end
Return the argument wrapped as an SQL::Identifier
.
Sequel.identifier(:a) # "a"
Source
# File lib/sequel/sql.rb 578 def ilike(*args) 579 SQL::StringExpression.like(*(args << {:case_insensitive=>true})) 580 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 '\'
Source
# 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 IsDistinctFrom
expression object, using the IS DISTINCT FROM operator with the given left hand side and right hand side.
Source
# File lib/sequel/sql.rb 554 def join(args, joiner=nil) 555 raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array) 556 if joiner 557 args = args.zip([joiner]*args.length).flatten 558 args.pop 559 end 560 561 return SQL::StringExpression.new(:NOOP, '') if args.empty? 562 563 args = args.map do |a| 564 case a 565 when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass 566 a 567 else 568 a.to_s 569 end 570 end 571 SQL::StringExpression.new(:'||', *args) 572 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
Source
# File lib/sequel/sql.rb 586 def like(*args) 587 SQL::StringExpression.like(*args) 588 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 '\'
Source
# File lib/sequel/sql.rb 603 def lit(s, *args) 604 if args.empty? 605 if s.is_a?(LiteralString) 606 s 607 else 608 LiteralString.new(s) 609 end 610 else 611 SQL::PlaceholderLiteralString.new(s, args) 612 end 613 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"
Source
# File lib/sequel/sql.rb 621 def negate(arg) 622 if condition_specifier?(arg) 623 SQL::BooleanExpression.from_value_pairs(arg, :AND, true) 624 else 625 raise Error, 'must pass a conditions specifier to Sequel.negate' 626 end 627 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))
Source
# File lib/sequel/sql.rb 635 def or(arg) 636 if condition_specifier?(arg) 637 SQL::BooleanExpression.from_value_pairs(arg, :OR, false) 638 else 639 raise Error, 'must pass a conditions specifier to Sequel.or' 640 end 641 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))
Source
# 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 a Postgres::PGArray
proxy for the given array and database array type.
Source
# 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 object wrapped in an Postgres::ArrayOp
.
Source
# 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
Return the expression wrapped in the Postgres::InetOp
.
Source
# 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
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).
Source
# File lib/sequel/extensions/pg_json_ops.rb 1394 def pg_json_op(v) 1395 case v 1396 when Postgres::JSONOp 1397 v 1398 else 1399 Postgres::JSONOp.new(v) 1400 end 1401 end
Return the object wrapped in an Postgres::JSONOp
.
Source
# 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
Wraps Ruby array, hash, string, integer, float, true, false, and nil values with the appropriate JSON wrapper. Raises an exception for other types.
Source
# 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
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).
Source
# File lib/sequel/extensions/pg_json_ops.rb 1404 def pg_jsonb_op(v) 1405 case v 1406 when Postgres::JSONBOp 1407 v 1408 else 1409 Postgres::JSONBOp.new(v) 1410 end 1411 end
Return the object wrapped in an Postgres::JSONBOp
.
Source
# 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
Wraps Ruby array, hash, string, integer, float, true, false, and nil values with the appropriate JSONB wrapper. Raises an exception for other types.
Source
# 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::PGMultiRange
.
Source
# 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
Convert the object to a Postgres::PGRange
.
Source
# 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
Return the expression wrapped in the Postgres::RangeOp
.
Source
# File lib/sequel/extensions/pg_row.rb 552 def pg_row(expr) 553 case expr 554 when Array 555 Postgres::PGRow::ArrayRow.new(expr) 556 else 557 # Will only work if pg_row_ops extension is loaded 558 pg_row_op(expr) 559 end 560 end
Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow
instance.
Source
# File lib/sequel/extensions/pg_row_ops.rb 189 def pg_row_op(expr) 190 Postgres::PGRowOp.wrap(expr) 191 end
Return a PGRowOp wrapping the given expression.
Source
# File lib/sequel/sql.rb 648 def qualify(qualifier, identifier) 649 SQL::QualifiedIdentifier.new(qualifier, identifier) 650 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"
Source
# File lib/sequel/extensions/pg_auto_parameterize.rb 503 def skip_pg_auto_param(v) 504 Postgres::AutoParameterize::SkipAutoParam.new(v) 505 end
Skip auto parameterization for the given object when building queries.
Source
# 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::JSONOp
.
Source
# 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 the object wrapped in an SQLite::JSONBOp
.
Source
# File lib/sequel/extensions/string_agg.rb 66 def string_agg(*a) 67 StringAgg.new(*a) 68 end
Return a StringAgg
expression for an aggregate string concatentation.
Source
# File lib/sequel/sql.rb 660 def subscript(exp, *subs) 661 SQL::Subscript.new(exp, subs.flatten) 662 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]
Source
# File lib/sequel/sql.rb 669 def trim(arg) 670 SQL::Function.new!(:trim, [arg], :emulate=>true) 671 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
Source
# File lib/sequel/sql.rb 682 def value_list(arg) 683 raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array) 684 SQL::ValueList.new(arg) 685 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))