Class Sequel::Dataset

  1. lib/sequel/dataset.rb
  2. lib/sequel/dataset/graph.rb
  3. lib/sequel/dataset/convenience.rb
  4. lib/sequel/dataset/features.rb
  5. lib/sequel/dataset/actions.rb
  6. lib/sequel/dataset/prepared_statements.rb
  7. lib/sequel/dataset/query.rb
  8. lib/sequel/dataset/sql.rb
  9. show all
Parent: Object

A dataset represents an SQL query, or more generally, an abstract set of rows in the database. Datasets can be used to create, retrieve, update and delete records.

Query results are always retrieved on demand, so a dataset can be kept around and reused indefinitely (datasets never cache results):

  my_posts = DB[:posts].filter(:author => 'david') # no records are retrieved
  my_posts.all # records are retrieved
  my_posts.all # records are retrieved again

Most dataset methods return modified copies of the dataset (functional style), so you can reuse different datasets to access data:

  posts = DB[:posts]
  davids_posts = posts.filter(:author => 'david')
  old_posts = posts.filter('stamp < ?', Date.today - 7)
  davids_old_posts = davids_posts.filter('stamp < ?', Date.today - 7)

Datasets are Enumerable objects, so they can be manipulated using any of the Enumerable methods, such as map, inject, etc.

Methods

public class

  1. clause_methods
  2. def_mutation_method
  3. new

public instance

  1. <<
  2. []
  3. []=
  4. add_graph_aliases
  5. aliased_expression_sql
  6. all
  7. and
  8. array_sql
  9. as
  10. avg
  11. bind
  12. boolean_constant_sql
  13. call
  14. case_expression_sql
  15. cast_sql
  16. clone
  17. column_all_sql
  18. columns
  19. columns!
  20. complex_expression_sql
  21. constant_sql
  22. count
  23. def_mutation_method
  24. delete
  25. delete_sql
  26. distinct
  27. each
  28. each_server
  29. empty?
  30. except
  31. exclude
  32. exists
  33. fetch_rows
  34. filter
  35. first
  36. first_source
  37. first_source_alias
  38. from
  39. from_self
  40. function_sql
  41. get
  42. graph
  43. grep
  44. group
  45. group_and_count
  46. group_by
  47. having
  48. import
  49. insert
  50. insert_multiple
  51. insert_sql
  52. inspect
  53. intersect
  54. interval
  55. invert
  56. join_clause_sql
  57. join_on_clause_sql
  58. join_table
  59. join_using_clause_sql
  60. last
  61. limit
  62. literal
  63. map
  64. max
  65. min
  66. multi_insert
  67. multi_insert_sql
  68. naked
  69. negative_boolean_constant_sql
  70. or
  71. order
  72. order_by
  73. order_more
  74. ordered_expression_sql
  75. placeholder_literal_string_sql
  76. prepare
  77. qualified_identifier_sql
  78. qualify
  79. qualify_to
  80. qualify_to_first_source
  81. quote_identifier
  82. quote_identifiers?
  83. quote_schema_table
  84. quoted_identifier
  85. range
  86. requires_sql_standard_datetimes?
  87. reverse
  88. reverse_order
  89. schema_and_table
  90. select
  91. select_all
  92. select_hash
  93. select_map
  94. select_more
  95. select_order_map
  96. select_sql
  97. server
  98. set
  99. set_defaults
  100. set_graph_aliases
  101. set_overrides
  102. single_record
  103. single_value
  104. sql
  105. subscript_sql
  106. sum
  107. supports_cte?
  108. supports_distinct_on?
  109. supports_intersect_except?
  110. supports_intersect_except_all?
  111. supports_is_true?
  112. supports_join_using?
  113. supports_modifying_joins?
  114. supports_multiple_column_in?
  115. supports_timestamp_timezones?
  116. supports_timestamp_usecs?
  117. supports_window_functions?
  118. to_csv
  119. to_hash
  120. truncate
  121. truncate_sql
  122. unfiltered
  123. ungraphed
  124. ungrouped
  125. union
  126. unlimited
  127. unordered
  128. update
  129. update_sql
  130. where
  131. window_function_sql
  132. window_sql
  133. with
  134. with_recursive
  135. with_sql

protected instance

  1. _insert_sql
  2. _update_sql
  3. compound_from_self
  4. options_overlap
  5. simple_select_all?
  6. to_prepared_statement

Included modules

  1. Metaprogramming
  2. Enumerable

Constants

COLUMN_CHANGE_OPTS = [:select, :sql, :from, :join].freeze   The dataset options that require the removal of cached columns if changed.
MUTATION_METHODS = %w'add_graph_aliases and distinct except exclude filter from from_self full_outer_join graph group group_and_count group_by having inner_join intersect invert join join_table left_outer_join limit naked or order order_by order_more paginate qualify query reverse reverse_order right_outer_join select select_all select_more server set_defaults set_graph_aliases set_overrides unfiltered ungraphed ungrouped union unlimited unordered where with with_recursive with_sql'.collect{|x| x.to_sym}   All methods that should have a ! method added that modifies the receiver.
NON_SQL_OPTIONS = [:server, :defaults, :overrides, :graph, :eager_graph, :graph_aliases]   Which options don’t affect the SQL generation. Used by simple_select_all? to determine if this is a simple SELECT * FROM table.
NOTIMPL_MSG = "This method must be overridden in Sequel adapters".freeze
WITH_SUPPORTED = :select_with_sql
COMMA_SEPARATOR = ', '.freeze
COUNT_OF_ALL_AS_COUNT = SQL::Function.new(:count, LiteralString.new('*'.freeze)).as(:count)
ARRAY_ACCESS_ERROR_MSG = 'You cannot call Dataset#[] with an integer or with no arguments.'.freeze
ARG_BLOCK_ERROR_MSG = 'Must use either an argument or a block, not both'.freeze
IMPORT_ERROR_MSG = 'Using Sequel::Dataset#import an empty column array is not allowed'.freeze
PREPARED_ARG_PLACEHOLDER = LiteralString.new('?').freeze
CONDITIONED_JOIN_TYPES = [:inner, :full_outer, :right_outer, :left_outer, :full, :right, :left]   These symbols have _join methods created (e.g. inner_join) that call join_table with the symbol, passing along the arguments and block from the method call.
UNCONDITIONED_JOIN_TYPES = [:natural, :natural_left, :natural_right, :natural_full, :cross]   These symbols have _join methods created (e.g. natural_join) that call join_table with the symbol. They only accept a single table argument which is passed to join_table, and they raise an error if called with a block.
AND_SEPARATOR = " AND ".freeze
BOOL_FALSE = "'f'".freeze
BOOL_TRUE = "'t'".freeze
COLUMN_REF_RE1 = /\A([\w ]+)__([\w ]+)___([\w ]+)\z/.freeze
COLUMN_REF_RE2 = /\A([\w ]+)___([\w ]+)\z/.freeze
COLUMN_REF_RE3 = /\A([\w ]+)__([\w ]+)\z/.freeze
COUNT_FROM_SELF_OPTS = [:distinct, :group, :sql, :limit, :compounds]
DATASET_ALIAS_BASE_NAME = 't'.freeze
IS_LITERALS = {nil=>'NULL'.freeze, true=>'TRUE'.freeze, false=>'FALSE'.freeze}.freeze
IS_OPERATORS = ::Sequel::SQL::ComplexExpression::IS_OPERATORS
N_ARITY_OPERATORS = ::Sequel::SQL::ComplexExpression::N_ARITY_OPERATORS
NULL = "NULL".freeze
QUALIFY_KEYS = [:select, :where, :having, :order, :group]
QUESTION_MARK = '?'.freeze
DELETE_CLAUSE_METHODS = clause_methods(:delete, %w'from where')
INSERT_CLAUSE_METHODS = clause_methods(:insert, %w'into columns values')
SELECT_CLAUSE_METHODS = clause_methods(:select, %w'with distinct columns from join where group having compounds order limit')
UPDATE_CLAUSE_METHODS = clause_methods(:update, %w'table set where')
TIMESTAMP_FORMAT = "'%Y-%m-%d %H:%M:%S%N%z'".freeze
STANDARD_TIMESTAMP_FORMAT = "TIMESTAMP #{TIMESTAMP_FORMAT}".freeze
TWO_ARITY_OPERATORS = ::Sequel::SQL::ComplexExpression::TWO_ARITY_OPERATORS
WILDCARD = '*'.freeze
SQL_WITH = "WITH ".freeze

External Aliases

inner_join -> join

Attributes

db [RW] The database that corresponds to this dataset
identifier_input_method [RW] Set the method to call on identifiers going into the database for this dataset
identifier_output_method [RW] Set the method to call on identifiers coming the database for this dataset
opts [RW] The hash of options for this dataset, keys are symbols.
quote_identifiers [W] Whether to quote identifiers for this dataset
row_proc [RW] The row_proc for this database, should be a Proc that takes a single hash argument and returns the object you want each to return.

Public class methods

clause_methods (type, clauses)

Given a type (e.g. select) and an array of clauses, return an array of methods to call to build the SQL string.

[show source]
   # File lib/sequel/dataset/sql.rb, line 5
5:     def self.clause_methods(type, clauses)
6:       clauses.map{|clause| "#{type}_#{clause}_sql""#{type}_#{clause}_sql"}.freeze
7:     end
def_mutation_method (*meths)

Setup mutation (e.g. filter!) methods. These operate the same as the non-! methods, but replace the options of the current dataset with the options of the resulting dataset.

[show source]
    # File lib/sequel/dataset.rb, line 91
91:     def self.def_mutation_method(*meths)
92:       meths.each do |meth|
93:         class_eval("def #{meth}!(*args, &block); mutation_method(:#{meth}, *args, &block) end", __FILE__, __LINE__)
94:       end
95:     end
new (db, opts = nil)

Constructs a new Dataset instance with an associated database and options. Datasets are usually constructed by invoking the Database#[] method:

  DB[:posts]

Sequel::Dataset is an abstract class that is not useful by itself. Each database adaptor should provide a subclass of Sequel::Dataset, and have the Database#dataset method return an instance of that class.

[show source]
    # File lib/sequel/dataset.rb, line 77
77:     def initialize(db, opts = nil)
78:       @db = db
79:       @quote_identifiers = db.quote_identifiers? if db.respond_to?(:quote_identifiers?)
80:       @identifier_input_method = db.identifier_input_method if db.respond_to?(:identifier_input_method)
81:       @identifier_output_method = db.identifier_output_method if db.respond_to?(:identifier_output_method)
82:       @opts = opts || {}
83:       @row_proc = nil
84:     end

Public instance methods

<< (*args)

Alias for insert, but not aliased directly so subclasses don’t have to override both methods.

[show source]
   # File lib/sequel/dataset/actions.rb, line 5
5:     def <<(*args)
6:       insert(*args)
7:     end
[] (*conditions)

Returns the first record matching the conditions. Examples:

  ds[:id=>1] => {:id=1}
[show source]
    # File lib/sequel/dataset/convenience.rb, line 12
12:     def [](*conditions)
13:       raise(Error, ARRAY_ACCESS_ERROR_MSG) if (conditions.length == 1 and conditions.first.is_a?(Integer)) or conditions.length == 0
14:       first(*conditions)
15:     end
[]= (conditions, values)

Update all records matching the conditions with the values specified. Examples:

  ds[:id=>1] = {:id=>2} # SQL: UPDATE ... SET id = 2 WHERE id = 1
[show source]
    # File lib/sequel/dataset/convenience.rb, line 21
21:     def []=(conditions, values)
22:       filter(conditions).update(values)
23:     end
add_graph_aliases (graph_aliases)

Adds the given graph aliases to the list of graph aliases to use, unlike set_graph_aliases, which replaces the list. See set_graph_aliases.

[show source]
    # File lib/sequel/dataset/graph.rb, line 6
 6:     def add_graph_aliases(graph_aliases)
 7:       ds = select_more(*graph_alias_columns(graph_aliases))
 8:       ds.opts[:graph_aliases] = (ds.opts[:graph_aliases] || ds.opts[:graph][:column_aliases] || {}).merge(graph_aliases)
 9:       ds
10:     end
aliased_expression_sql (ae)

SQL fragment for the aliased expression

[show source]
    # File lib/sequel/dataset/sql.rb, line 45
45:     def aliased_expression_sql(ae)
46:       as_sql(literal(ae.expression), ae.aliaz)
47:     end
all (&block)

Returns an array with all records in the dataset. If a block is given, the array is iterated over after all items have been loaded.

[show source]
    # File lib/sequel/dataset/actions.rb, line 11
11:     def all(&block)
12:       a = []
13:       each{|r| a << r}
14:       post_load(a)
15:       a.each(&block) if block
16:       a
17:     end
and (*cond, &block)

Adds an further filter to an existing filter using AND. If no filter exists an error is raised. This method is identical to filter except it expects an existing filter.

  ds.filter(:a).and(:b) # SQL: WHERE a AND b
[show source]
    # File lib/sequel/dataset/query.rb, line 8
 8:     def and(*cond, &block)
 9:       raise(InvalidOperation, "No existing filter found.") unless @opts[:having] || @opts[:where]
10:       filter(*cond, &block)
11:     end
array_sql (a)

SQL fragment for the SQL array.

[show source]
    # File lib/sequel/dataset/sql.rb, line 50
50:     def array_sql(a)
51:       a.empty? ? '(NULL)' : "(#{expression_list(a)})"     
52:     end
as (aliaz)

Return the dataset as an aliased expression with the given alias. You can use this as a FROM or JOIN dataset, or as a column if this dataset returns a single row and column.

[show source]
     # File lib/sequel/dataset.rb, line 102
102:     def as(aliaz)
103:       ::Sequel::SQL::AliasedExpression.new(self, aliaz)
104:     end
avg (column)

Returns the average value for the given column.

[show source]
    # File lib/sequel/dataset/convenience.rb, line 26
26:     def avg(column)
27:       aggregate_dataset.get{avg(column)}
28:     end
bind (bind_vars={})

Set the bind variables to use for the call. If bind variables have already been set for this dataset, they are updated with the contents of bind_vars.

[show source]
     # File lib/sequel/dataset/prepared_statements.rb, line 173
173:     def bind(bind_vars={})
174:       clone(:bind_vars=>@opts[:bind_vars] ? @opts[:bind_vars].merge(bind_vars) : bind_vars)
175:     end
boolean_constant_sql (constant)

SQL fragment for BooleanConstants

[show source]
    # File lib/sequel/dataset/sql.rb, line 55
55:     def boolean_constant_sql(constant)
56:       literal(constant)
57:     end
call (type, bind_variables={}, *values, &block)

For the given type (:select, :insert, :update, or :delete), run the sql with the bind variables specified in the hash. values is a hash of passed to insert or update (if one of those types is used), which may contain placeholders.

[show source]
     # File lib/sequel/dataset/prepared_statements.rb, line 182
182:     def call(type, bind_variables={}, *values, &block)
183:       prepare(type, nil, *values).call(bind_variables, &block)
184:     end
case_expression_sql (ce)

SQL fragment for specifying given CaseExpression.

[show source]
    # File lib/sequel/dataset/sql.rb, line 60
60:     def case_expression_sql(ce)
61:       sql = '(CASE '
62:       sql << "#{literal(ce.expression)} " if ce.expression
63:       ce.conditions.collect{ |c,r|
64:         sql << "WHEN #{literal(c)} THEN #{literal(r)} "
65:       }
66:       sql << "ELSE #{literal(ce.default)} END)"
67:     end
cast_sql (expr, type)

SQL fragment for the SQL CAST expression.

[show source]
    # File lib/sequel/dataset/sql.rb, line 70
70:     def cast_sql(expr, type)
71:       "CAST(#{literal(expr)} AS #{db.cast_type_literal(type)})"
72:     end
clone (opts = {})

Returns a new clone of the dataset with with the given options merged. If the options changed include options in COLUMN_CHANGE_OPTS, the cached columns are deleted.

[show source]
     # File lib/sequel/dataset.rb, line 109
109:     def clone(opts = {})
110:       c = super()
111:       c.opts = @opts.merge(opts)
112:       c.instance_variable_set(:@columns, nil) if opts.keys.any?{|o| COLUMN_CHANGE_OPTS.include?(o)}
113:       c
114:     end
column_all_sql (ca)

SQL fragment for specifying all columns in a given table.

[show source]
    # File lib/sequel/dataset/sql.rb, line 75
75:     def column_all_sql(ca)
76:       "#{quote_schema_table(ca.table)}.*"
77:     end
columns ()

Returns the columns in the result set in order. If the columns are currently cached, returns the cached value. Otherwise, a SELECT query is performed to get a single row. Adapters are expected to fill the columns cache with the column information when a query is performed. If the dataset does not have any rows, this may be an empty array depending on how the adapter is programmed.

If you are looking for all columns for a single table and maybe some information about each column (e.g. type), see Database#schema.

[show source]
    # File lib/sequel/dataset/actions.rb, line 28
28:     def columns
29:       return @columns if @columns
30:       ds = unfiltered.unordered.clone(:distinct => nil, :limit => 1)
31:       ds.each{break}
32:       @columns = ds.instance_variable_get(:@columns)
33:       @columns || []
34:     end
columns! ()

Remove the cached list of columns and do a SELECT query to find the columns.

[show source]
    # File lib/sequel/dataset/actions.rb, line 38
38:     def columns!
39:       @columns = nil
40:       columns
41:     end
complex_expression_sql (op, args)

SQL fragment for complex expressions

[show source]
     # File lib/sequel/dataset/sql.rb, line 80
 80:     def complex_expression_sql(op, args)
 81:       case op
 82:       when *IS_OPERATORS
 83:         r = args.at(1)
 84:         if r.nil? || supports_is_true?
 85:           raise(InvalidOperation, 'Invalid argument used for IS operator') unless v = IS_LITERALS[r]
 86:           "(#{literal(args.at(0))} #{op} #{v})"
 87:         elsif op == :IS
 88:           complex_expression_sql(:"=", args)
 89:         else
 90:           complex_expression_sql(:OR, [SQL::BooleanExpression.new(:"!=", *args), SQL::BooleanExpression.new(:IS, args.at(0), nil)])
 91:         end
 92:       when :IN, :"NOT IN"
 93:         cols = args.at(0)
 94:         if !supports_multiple_column_in? && cols.is_a?(Array)
 95:           expr = SQL::BooleanExpression.new(:OR, *args.at(1).to_a.map{|vals| SQL::BooleanExpression.from_value_pairs(cols.zip(vals).map{|col, val| [col, val]})})
 96:           literal(op == :IN ? expr : ~expr)
 97:         else
 98:           "(#{literal(cols)} #{op} #{literal(args.at(1))})"
 99:         end
100:       when *TWO_ARITY_OPERATORS
101:         "(#{literal(args.at(0))} #{op} #{literal(args.at(1))})"
102:       when *N_ARITY_OPERATORS
103:         "(#{args.collect{|a| literal(a)}.join(" #{op} ")})"
104:       when :NOT
105:         "NOT #{literal(args.at(0))}"
106:       when :NOOP
107:         literal(args.at(0))
108:       when :'B~'
109:         "~#{literal(args.at(0))}"
110:       else
111:         raise(InvalidOperation, "invalid operator #{op}")
112:       end
113:     end
constant_sql (constant)

SQL fragment for constants

[show source]
     # File lib/sequel/dataset/sql.rb, line 116
116:     def constant_sql(constant)
117:       constant.to_s
118:     end
count ()

Returns the number of records in the dataset.

[show source]
     # File lib/sequel/dataset/sql.rb, line 121
121:     def count
122:       aggregate_dataset.get{COUNT(:*){}.as(count)}.to_i
123:     end
def_mutation_method (*meths)

Add a mutation method to this dataset instance.

[show source]
     # File lib/sequel/dataset.rb, line 117
117:     def def_mutation_method(*meths)
118:       meths.each do |meth|
119:         instance_eval("def #{meth}!(*args, &block); mutation_method(:#{meth}, *args, &block) end", __FILE__, __LINE__)
120:       end
121:     end
delete ()

Deletes the records in the dataset. The returned value is generally the number of records deleted, but that is adapter dependent. See delete_sql.

[show source]
    # File lib/sequel/dataset/actions.rb, line 45
45:     def delete
46:       execute_dui(delete_sql)
47:     end
delete_sql ()

Formats a DELETE statement using the given options and dataset options.

  dataset.filter{|o| o.price >= 100}.delete_sql #=>
    "DELETE FROM items WHERE (price >= 100)"
[show source]
     # File lib/sequel/dataset/sql.rb, line 129
129:     def delete_sql
130:       return static_sql(opts[:sql]) if opts[:sql]
131:       check_modification_allowed!
132:       clause_sql(:delete)
133:     end
distinct (*args)

Returns a copy of the dataset with the SQL DISTINCT clause. The DISTINCT clause is used to remove duplicate rows from the output. If arguments are provided, uses a DISTINCT ON clause, in which case it will only be distinct on those columns, instead of all returned columns. Raises an error if arguments are given and DISTINCT ON is not supported.

 dataset.distinct # SQL: SELECT DISTINCT * FROM items
 dataset.order(:id).distinct(:id) # SQL: SELECT DISTINCT ON (id) * FROM items ORDER BY id
[show source]
    # File lib/sequel/dataset/query.rb, line 22
22:     def distinct(*args)
23:       raise(InvalidOperation, "DISTINCT ON not supported") if !args.empty? && !supports_distinct_on?
24:       clone(:distinct => args)
25:     end
each () {|row_proc.call(r)}| ...}

Iterates over the records in the dataset as they are yielded from the database adapter, and returns self.

Note that this method is not safe to use on many adapters if you are running additional queries inside the provided block. If you are running queries inside the block, you use should all instead of each.

[show source]
    # File lib/sequel/dataset/actions.rb, line 55
55:     def each(&block)
56:       if @opts[:graph]
57:         graph_each(&block)
58:       elsif row_proc = @row_proc
59:         fetch_rows(select_sql){|r| yield row_proc.call(r)}
60:       else
61:         fetch_rows(select_sql, &block)
62:       end
63:       self
64:     end
each_server () {|server(s)}| ...}

Yield a dataset for each server in the connection pool that is tied to that server. Intended for use in sharded environments where all servers need to be modified with the same data:

  DB[:configs].where(:key=>'setting').each_server{|ds| ds.update(:value=>'new_value')}
[show source]
     # File lib/sequel/dataset.rb, line 128
128:     def each_server
129:       db.servers.each{|s| yield server(s)}
130:     end
empty? ()

Returns true if no records exist in the dataset, false otherwise

[show source]
    # File lib/sequel/dataset/convenience.rb, line 31
31:     def empty?
32:       get(1).nil?
33:     end
except (dataset, opts={})

Adds an EXCEPT clause using a second dataset object. An EXCEPT compound dataset returns all rows in the current dataset that are not in the given dataset. Raises an InvalidOperation if the operation is not supported. Options:

  • :all - Set to true to use EXCEPT ALL instead of EXCEPT, so duplicate rows can occur
  • :from_self - Set to false to not wrap the returned dataset in a from_self, use with care.

    DB[:items].except(DB).sql #=> “SELECT * FROM items EXCEPT SELECT * FROM other_items“

[show source]
    # File lib/sequel/dataset/query.rb, line 37
37:     def except(dataset, opts={})
38:       opts = {:all=>opts} unless opts.is_a?(Hash)
39:       raise(InvalidOperation, "EXCEPT not supported") unless supports_intersect_except?
40:       raise(InvalidOperation, "EXCEPT ALL not supported") if opts[:all] && !supports_intersect_except_all?
41:       compound_clone(:except, dataset, opts)
42:     end
exclude (*cond, &block)

Performs the inverse of Dataset#filter.

  dataset.exclude(:category => 'software').sql #=>
    "SELECT * FROM items WHERE (category != 'software')"
[show source]
    # File lib/sequel/dataset/query.rb, line 48
48:     def exclude(*cond, &block)
49:       clause = (@opts[:having] ? :having : :where)
50:       cond = cond.first if cond.size == 1
51:       cond = filter_expr(cond, &block)
52:       cond = SQL::BooleanExpression.invert(cond)
53:       cond = SQL::BooleanExpression.new(:AND, @opts[clause], cond) if @opts[clause]
54:       clone(clause => cond)
55:     end
exists ()

Returns an EXISTS clause for the dataset as a LiteralString.

  DB.select(1).where(DB[:items].exists).sql
  #=> "SELECT 1 WHERE (EXISTS (SELECT * FROM items))"
[show source]
     # File lib/sequel/dataset/sql.rb, line 139
139:     def exists
140:       LiteralString.new("EXISTS (#{select_sql})")
141:     end
fetch_rows (sql, &block)

Executes a select query and fetches records, passing each record to the supplied block. The yielded records should be hashes with symbol keys.

[show source]
    # File lib/sequel/dataset/actions.rb, line 68
68:     def fetch_rows(sql, &block)
69:       raise NotImplementedError, NOTIMPL_MSG
70:     end
filter (*cond, &block)

Returns a copy of the dataset with the given conditions imposed upon it. If the query already has a HAVING clause, then the conditions are imposed in the HAVING clause. If not, then they are imposed in the WHERE clause.

filter accepts the following argument types:

  • Hash - list of equality/inclusion expressions
  • Array - depends:
    • If first member is a string, assumes the rest of the arguments are parameters and interpolates them into the string.
    • If all members are arrays of length two, treats the same way as a hash, except it allows for duplicate keys to be specified.
  • String - taken literally
  • Symbol - taken as a boolean column argument (e.g. WHERE active)
  • Sequel::SQL::BooleanExpression - an existing condition expression, probably created using the Sequel expression filter DSL.

filter also takes a block, which should return one of the above argument types, and is treated the same way. This block yields a virtual row object, which is easy to use to create identifiers and functions.

If both a block and regular argument are provided, they get ANDed together.

Examples:

  dataset.filter(:id => 3).sql #=>
    "SELECT * FROM items WHERE (id = 3)"
  dataset.filter('price < ?', 100).sql #=>
    "SELECT * FROM items WHERE price < 100"
  dataset.filter([[:id, (1,2,3)], [:id, 0..10]]).sql #=>
    "SELECT * FROM items WHERE ((id IN (1, 2, 3)) AND ((id >= 0) AND (id <= 10)))"
  dataset.filter('price < 100').sql #=>
    "SELECT * FROM items WHERE price < 100"
  dataset.filter(:active).sql #=>
    "SELECT * FROM items WHERE :active
  dataset.filter{|o| o.price < 100}.sql #=>
    "SELECT * FROM items WHERE (price < 100)"

Multiple filter calls can be chained for scoping:

  software = dataset.filter(:category => 'software')
  software.filter{|o| o.price < 100}.sql #=>
    "SELECT * FROM items WHERE ((category = 'software') AND (price < 100))"

See doc/dataset_filtering.rdoc for more examples and details.

[show source]
     # File lib/sequel/dataset/query.rb, line 104
104:     def filter(*cond, &block)
105:       _filter(@opts[:having] ? :having : :where, *cond, &block)
106:     end
first (*args, &block)

If a integer argument is given, it is interpreted as a limit, and then returns all matching records up to that limit. If no argument is passed, it returns the first matching record. If any other type of argument(s) is passed, it is given to filter and the first matching record is returned. If a block is given, it is used to filter the dataset before returning anything. Examples:

  ds.first => {:id=>7}
  ds.first(2) => [{:id=>6}, {:id=>4}]
  ds.order(:id).first(2) => [{:id=>1}, {:id=>2}]
  ds.first(:id=>2) => {:id=>2}
  ds.first("id = 3") => {:id=>3}
  ds.first("id = ?", 4) => {:id=>4}
  ds.first{|o| o.id > 2} => {:id=>5}
  ds.order(:id).first{|o| o.id > 2} => {:id=>3}
  ds.first{|o| o.id > 2} => {:id=>5}
  ds.first("id > ?", 4){|o| o.id < 6} => {:id=>5}
  ds.order(:id).first(2){|o| o.id < 2} => [{:id=>1}]
[show source]
    # File lib/sequel/dataset/convenience.rb, line 54
54:     def first(*args, &block)
55:       ds = block ? filter(&block) : self
56: 
57:       if args.empty?
58:         ds.single_record
59:       else
60:         args = (args.size == 1) ? args.first : args
61:         if Integer === args
62:           ds.limit(args).all
63:         else
64:           ds.filter(args).single_record
65:         end
66:       end
67:     end
first_source ()

Alias for first_source_alias

first_source_alias ()

The first source (primary table) for this dataset. If the dataset doesn’t have a table, raises an error. If the table is aliased, returns the aliased name.

[show source]
     # File lib/sequel/dataset/sql.rb, line 145
145:     def first_source_alias
146:       source = @opts[:from]
147:       if source.nil? || source.empty?
148:         raise Error, 'No source specified for query'
149:       end
150:       case s = source.first
151:       when SQL::AliasedExpression
152:         s.aliaz
153:       when Symbol
154:         sch, table, aliaz = split_symbol(s)
155:         aliaz ? aliaz.to_sym : s
156:       else
157:         s
158:       end
159:     end
from (*source)

Returns a copy of the dataset with the source changed.

  dataset.from # SQL: SELECT *
  dataset.from(:blah) # SQL: SELECT * FROM blah
  dataset.from(:blah, :foo) # SQL: SELECT * FROM blah, foo
[show source]
     # File lib/sequel/dataset/query.rb, line 113
113:     def from(*source)
114:       table_alias_num = 0
115:       sources = []
116:       source.each do |s|
117:         case s
118:         when Hash
119:           s.each{|k,v| sources << SQL::AliasedExpression.new(k,v)}
120:         when Dataset
121:           sources << SQL::AliasedExpression.new(s, dataset_alias(table_alias_num+=1))
122:         when Symbol
123:           sch, table, aliaz = split_symbol(s)
124:           if aliaz
125:             s = sch ? SQL::QualifiedIdentifier.new(sch.to_sym, table.to_sym) : SQL::Identifier.new(table.to_sym)
126:             sources << SQL::AliasedExpression.new(s, aliaz.to_sym)
127:           else
128:             sources << s
129:           end
130:         else
131:           sources << s
132:         end
133:       end
134:       o = {:from=>sources.empty? ? nil : sources}
135:       o[:num_dataset_sources] = table_alias_num if table_alias_num > 0
136:       clone(o)
137:     end
from_self (opts={})

Returns a dataset selecting from the current dataset. Supplying the :alias option controls the name of the result.

  ds = DB[:items].order(:name).select(:id, :name)
  ds.sql                         #=> "SELECT id,name FROM items ORDER BY name"
  ds.from_self.sql               #=> "SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS t1"
  ds.from_self(:alias=>:foo).sql #=> "SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS foo"
[show source]
     # File lib/sequel/dataset/query.rb, line 146
146:     def from_self(opts={})
147:       fs = {}
148:       @opts.keys.each{|k| fs[k] = nil unless NON_SQL_OPTIONS.include?(k)}
149:       clone(fs).from(opts[:alias] ? as(opts[:alias]) : self)
150:     end
function_sql (f)

SQL fragment specifying an SQL function call

[show source]
     # File lib/sequel/dataset/sql.rb, line 163
163:     def function_sql(f)
164:       args = f.args
165:       "#{f.f}#{args.empty? ? '()' : literal(args)}"
166:     end
get (column=nil, &block)

Return the column value for the first matching record in the dataset. Raises an error if both an argument and block is given.

  ds.get(:id)
  ds.get{|o| o.sum(:id)}
[show source]
    # File lib/sequel/dataset/convenience.rb, line 74
74:     def get(column=nil, &block)
75:       if column
76:         raise(Error, ARG_BLOCK_ERROR_MSG) if block
77:         select(column).single_value
78:       else
79:         select(&block).single_value
80:       end
81:     end
graph (dataset, join_conditions = nil, options = {}, &block)

Allows you to join multiple datasets/tables and have the result set split into component tables.

This differs from the usual usage of join, which returns the result set as a single hash. For example:

  # CREATE TABLE artists (id INTEGER, name TEXT);
  # CREATE TABLE albums (id INTEGER, name TEXT, artist_id INTEGER);
  DB[:artists].left_outer_join(:albums, :artist_id=>:id).first
  => {:id=>albums.id, :name=>albums.name, :artist_id=>albums.artist_id}
  DB[:artists].graph(:albums, :artist_id=>:id).first
  => {:artists=>{:id=>artists.id, :name=>artists.name}, :albums=>{:id=>albums.id, :name=>albums.name, :artist_id=>albums.artist_id}}

Using a join such as left_outer_join, the attribute names that are shared between the tables are combined in the single return hash. You can get around that by using .select with correct aliases for all of the columns, but it is simpler to use graph and have the result set split for you. In addition, graph respects any row_proc of the current dataset and the datasets you use with graph.

If you are graphing a table and all columns for that table are nil, this indicates that no matching rows existed in the table, so graph will return nil instead of a hash with all nil values:

  # If the artist doesn't have any albums
  DB[:artists].graph(:albums, :artist_id=>:id).first
  => {:artists=>{:id=>artists.id, :name=>artists.name}, :albums=>nil}

Arguments:

  • dataset - Can be a symbol (specifying a table), another dataset, or an object that responds to .dataset and return a symbol or a dataset
  • join_conditions - Any condition(s) allowed by join_table.
  • options - A hash of graph options. The following options are currently used:
    • :from_self_alias - The alias to use when the receiver is not a graphed dataset but it contains multiple FROM tables or a JOIN. In this case, the receiver is wrapped in a from_self before graphing, and this option determines the alias to use.
    • :implicit_qualifier - The qualifier of implicit conditions, see join_table.
    • :join_type - The type of join to use (passed to join_table). Defaults to :left_outer.
    • :select - An array of columns to select. When not used, selects all columns in the given dataset. When set to false, selects no columns and is like simply joining the tables, though graph keeps some metadata about join that makes it important to use graph instead of join.
    • :table_alias - The alias to use for the table. If not specified, doesn’t alias the table. You will get an error if the the alias (or table) name is used more than once.
  • block - A block that is passed to join_table.
[show source]
     # File lib/sequel/dataset/graph.rb, line 60
 60:     def graph(dataset, join_conditions = nil, options = {}, &block)
 61:       # Allow the use of a model, dataset, or symbol as the first argument
 62:       # Find the table name/dataset based on the argument
 63:       dataset = dataset.dataset if dataset.respond_to?(:dataset)
 64:       table_alias = options[:table_alias]
 65:       case dataset
 66:       when Symbol
 67:         table = dataset
 68:         dataset = @db[dataset]
 69:         table_alias ||= table
 70:       when ::Sequel::Dataset
 71:         if dataset.simple_select_all?
 72:           table = dataset.opts[:from].first
 73:           table_alias ||= table
 74:         else
 75:           table = dataset
 76:           table_alias ||= dataset_alias((@opts[:num_dataset_sources] || 0)+1)
 77:         end
 78:       else
 79:         raise Error, "The dataset argument should be a symbol, dataset, or model"
 80:       end
 81: 
 82:       # Raise Sequel::Error with explanation that the table alias has been used
 83:       raise_alias_error = lambda do
 84:         raise(Error, "this #{options[:table_alias] ? 'alias' : 'table'} has already been been used, please specify " \
 85:           "#{options[:table_alias] ? 'a different alias' : 'an alias via the :table_alias option'}") 
 86:       end
 87: 
 88:       # Only allow table aliases that haven't been used
 89:       raise_alias_error.call if @opts[:graph] && @opts[:graph][:table_aliases] && @opts[:graph][:table_aliases].include?(table_alias)
 90:       
 91:       # Use a from_self if this is already a joined table
 92:       ds = (!@opts[:graph] && (@opts[:from].length > 1 || @opts[:join])) ? from_self(:alias=>options[:from_self_alias] || first_source) : self
 93:       
 94:       # Join the table early in order to avoid cloning the dataset twice
 95:       ds = ds.join_table(options[:join_type] || :left_outer, table, join_conditions, :table_alias=>table_alias, :implicit_qualifier=>options[:implicit_qualifier], &block)
 96:       opts = ds.opts
 97: 
 98:       # Whether to include the table in the result set
 99:       add_table = options[:select] == false ? false : true
100:       # Whether to add the columns to the list of column aliases
101:       add_columns = !ds.opts.include?(:graph_aliases)
102: 
103:       # Setup the initial graph data structure if it doesn't exist
104:       unless graph = opts[:graph]
105:         master = ds.first_source_alias
106:         raise_alias_error.call if master == table_alias
107:         # Master hash storing all .graph related information
108:         graph = opts[:graph] = {}
109:         # Associates column aliases back to tables and columns
110:         column_aliases = graph[:column_aliases] = {}
111:         # Associates table alias (the master is never aliased)
112:         table_aliases = graph[:table_aliases] = {master=>self}
113:         # Keep track of the alias numbers used
114:         ca_num = graph[:column_alias_num] = Hash.new(0)
115:         # All columns in the master table are never
116:         # aliased, but are not included if set_graph_aliases
117:         # has been used.
118:         if add_columns
119:           select = opts[:select] = []
120:           columns.each do |column|
121:             column_aliases[column] = [master, column]
122:             select.push(SQL::QualifiedIdentifier.new(master, column))
123:           end
124:         end
125:       end
126: 
127:       # Add the table alias to the list of aliases
128:       # Even if it isn't been used in the result set,
129:       # we add a key for it with a nil value so we can check if it
130:       # is used more than once
131:       table_aliases = graph[:table_aliases]
132:       table_aliases[table_alias] = add_table ? dataset : nil
133: 
134:       # Add the columns to the selection unless we are ignoring them
135:       if add_table && add_columns
136:         select = opts[:select]
137:         column_aliases = graph[:column_aliases]
138:         ca_num = graph[:column_alias_num]
139:         # Which columns to add to the result set
140:         cols = options[:select] || dataset.columns
141:         # If the column hasn't been used yet, don't alias it.
142:         # If it has been used, try table_column.
143:         # If that has been used, try table_column_N 
144:         # using the next value of N that we know hasn't been
145:         # used
146:         cols.each do |column|
147:           col_alias, identifier = if column_aliases[column]
148:             column_alias = "#{table_alias}_#{column}""#{table_alias}_#{column}"
149:             if column_aliases[column_alias]
150:               column_alias_num = ca_num[column_alias]
151:               column_alias = "#{column_alias}_#{column_alias_num}""#{column_alias}_#{column_alias_num}" 
152:               ca_num[column_alias] += 1
153:             end
154:             [column_alias, SQL::QualifiedIdentifier.new(table_alias, column).as(column_alias)]
155:           else
156:             [column, SQL::QualifiedIdentifier.new(table_alias, column)]
157:           end
158:           column_aliases[col_alias] = [table_alias, column]
159:           select.push(identifier)
160:         end
161:       end
162:       ds
163:     end
grep (cols, terms)

Pattern match any of the columns to any of the terms. The terms can be strings (which use LIKE) or regular expressions (which are only supported in some databases). See Sequel::SQL::StringExpression.like. Note that the total number of pattern matches will be cols.length * terms.length, which could cause performance issues.

  dataset.grep(:a, '%test%') # SQL: SELECT * FROM items WHERE a LIKE '%test%'
  dataset.grep([:a, :b], %w'%test% foo') # SQL: SELECT * FROM items WHERE a LIKE '%test%' OR a LIKE 'foo' OR b LIKE '%test%' OR b LIKE 'foo'
[show source]
     # File lib/sequel/dataset/query.rb, line 160
160:     def grep(cols, terms)
161:       filter(SQL::BooleanExpression.new(:OR, *Array(cols).collect{|c| SQL::StringExpression.like(c, *terms)}))
162:     end
group (*columns)

Returns a copy of the dataset with the results grouped by the value of the given columns.

  dataset.group(:id) # SELECT * FROM items GROUP BY id
  dataset.group(:id, :name) # SELECT * FROM items GROUP BY id, name
[show source]
     # File lib/sequel/dataset/query.rb, line 169
169:     def group(*columns)
170:       clone(:group => (columns.compact.empty? ? nil : columns))
171:     end
group_and_count (*columns)

Returns a dataset grouped by the given column with count by group, order by the count of records (in ascending order). Column aliases may be supplied, and will be included in the select clause.

Examples:

  ds.group_and_count(:name).all => [{:name=>'a', :count=>1}, ...]
  ds.group_and_count(:first_name, :last_name).all => [{:first_name=>'a', :last_name=>'b', :count=>1}, ...]
  ds.group_and_count(:first_name___name).all => [{:name=>'a', :count=>1}, ...]
[show source]
    # File lib/sequel/dataset/convenience.rb, line 92
92:     def group_and_count(*columns)
93:       group(*columns.map{|c| unaliased_identifier(c)}).select(*(columns + [COUNT_OF_ALL_AS_COUNT])).order(:count)
94:     end
group_by (*columns)

Alias for group

having (*cond, &block)

Returns a copy of the dataset with the HAVING conditions changed. See filter for argument types.

  dataset.group(:sum).having(:sum=>10) # SQL: SELECT * FROM items GROUP BY sum HAVING sum = 10
[show source]
     # File lib/sequel/dataset/query.rb, line 177
177:     def having(*cond, &block)
178:       _filter(:having, *cond, &block)
179:     end
import (columns, values, opts={})

Inserts multiple records into the associated table. This method can be to efficiently insert a large amounts of records into a table. Inserts are automatically wrapped in a transaction.

This method is called with a columns array and an array of value arrays:

  dataset.import([:x, :y], [[1, 2], [3, 4]])

This method also accepts a dataset instead of an array of value arrays:

  dataset.import([:x, :y], other_dataset.select(:a___x, :b___y))

The method also accepts a :slice or :commit_every option that specifies the number of records to insert per transaction. This is useful especially when inserting a large number of records, e.g.:

  # this will commit every 50 records
  dataset.import([:x, :y], [[1, 2], [3, 4], ...], :slice => 50)
[show source]
     # File lib/sequel/dataset/convenience.rb, line 114
114:     def import(columns, values, opts={})
115:       return @db.transaction{insert(columns, values)} if values.is_a?(Dataset)
116: 
117:       return if values.empty?
118:       raise(Error, IMPORT_ERROR_MSG) if columns.empty?
119:       
120:       if slice_size = opts[:commit_every] || opts[:slice]
121:         offset = 0
122:         loop do
123:           @db.transaction(opts){multi_insert_sql(columns, values[offset, slice_size]).each{|st| execute_dui(st)}}
124:           offset += slice_size
125:           break if offset >= values.length
126:         end
127:       else
128:         statements = multi_insert_sql(columns, values)
129:         @db.transaction{statements.each{|st| execute_dui(st)}}
130:       end
131:     end
insert (*values)

Inserts values into the associated table. The returned value is generally the value of the primary key for the inserted row, but that is adapter dependent. See insert_sql.

[show source]
    # File lib/sequel/dataset/actions.rb, line 75
75:     def insert(*values)
76:       execute_insert(insert_sql(*values))
77:     end
insert_multiple (array, &block)

Inserts multiple values. If a block is given it is invoked for each item in the given array before inserting it. See multi_insert as a possible faster version that inserts multiple records in one SQL statement.

[show source]
     # File lib/sequel/dataset/sql.rb, line 172
172:     def insert_multiple(array, &block)
173:       if block
174:         array.each {|i| insert(block[i])}
175:       else
176:         array.each {|i| insert(i)}
177:       end
178:     end
insert_sql (*values)

Formats an INSERT statement using the given values. The API is a little complex, and best explained by example:

  # Default values
  DB[:items].insert_sql #=> 'INSERT INTO items DEFAULT VALUES'
  DB[:items].insert_sql({}) #=> 'INSERT INTO items DEFAULT VALUES'
  # Values without columns
  DB[:items].insert_sql(1,2,3) #=> 'INSERT INTO items VALUES (1, 2, 3)'
  DB[:items].insert_sql([1,2,3]) #=> 'INSERT INTO items VALUES (1, 2, 3)'
  # Values with columns
  DB[:items].insert_sql([:a, :b], [1,2]) #=> 'INSERT INTO items (a, b) VALUES (1, 2)'
  DB[:items].insert_sql(:a => 1, :b => 2) #=> 'INSERT INTO items (a, b) VALUES (1, 2)'
  # Using a subselect
  DB[:items].insert_sql(DB[:old_items]) #=> 'INSERT INTO items SELECT * FROM old_items
  # Using a subselect with columns
  DB[:items].insert_sql([:a, :b], DB[:old_items]) #=> 'INSERT INTO items (a, b) SELECT * FROM old_items
[show source]
     # File lib/sequel/dataset/sql.rb, line 196
196:     def insert_sql(*values)
197:       return static_sql(@opts[:sql]) if @opts[:sql]
198: 
199:       check_modification_allowed!
200: 
201:       columns = []
202: 
203:       case values.size
204:       when 0
205:         return insert_sql({})
206:       when 1
207:         case vals = values.at(0)
208:         when Hash
209:           vals = @opts[:defaults].merge(vals) if @opts[:defaults]
210:           vals = vals.merge(@opts[:overrides]) if @opts[:overrides]
211:           values = []
212:           vals.each do |k,v| 
213:             columns << k
214:             values << v
215:           end
216:         when Dataset, Array, LiteralString
217:           values = vals
218:         else
219:           if vals.respond_to?(:values) && (v = vals.values).is_a?(Hash)
220:             return insert_sql(v) 
221:           end
222:         end
223:       when 2
224:         if (v0 = values.at(0)).is_a?(Array) && ((v1 = values.at(1)).is_a?(Array) || v1.is_a?(Dataset) || v1.is_a?(LiteralString))
225:           columns, values = v0, v1
226:           raise(Error, "Different number of values and columns given to insert_sql") if values.is_a?(Array) and columns.length != values.length
227:         end
228:       end
229: 
230:       columns = columns.map{|k| literal(String === k ? k.to_sym : k)}
231:       clone(:columns=>columns, :values=>values)._insert_sql
232:     end
inspect ()

Returns a string representation of the dataset including the class name and the corresponding SQL select statement.

[show source]
     # File lib/sequel/dataset.rb, line 134
134:     def inspect
135:       "#<#{self.class}: #{sql.inspect}>"
136:     end
intersect (dataset, opts={})

Adds an INTERSECT clause using a second dataset object. An INTERSECT compound dataset returns all rows in both the current dataset and the given dataset. Raises an InvalidOperation if the operation is not supported. Options:

  • :all - Set to true to use INTERSECT ALL instead of INTERSECT, so duplicate rows can occur
  • :from_self - Set to false to not wrap the returned dataset in a from_self, use with care.

    DB[:items].intersect(DB).sql #=> “SELECT * FROM items INTERSECT SELECT * FROM other_items“

[show source]
     # File lib/sequel/dataset/query.rb, line 191
191:     def intersect(dataset, opts={})
192:       opts = {:all=>opts} unless opts.is_a?(Hash)
193:       raise(InvalidOperation, "INTERSECT not supported") unless supports_intersect_except?
194:       raise(InvalidOperation, "INTERSECT ALL not supported") if opts[:all] && !supports_intersect_except_all?
195:       compound_clone(:intersect, dataset, opts)
196:     end
interval (column)

Returns the interval between minimum and maximum values for the given column.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 135
135:     def interval(column)
136:       aggregate_dataset.get{max(column) - min(column)}
137:     end
invert ()

Inverts the current filter

  dataset.filter(:category => 'software').invert.sql #=>
    "SELECT * FROM items WHERE (category != 'software')"
[show source]
     # File lib/sequel/dataset/query.rb, line 202
202:     def invert
203:       having, where = @opts[:having], @opts[:where]
204:       raise(Error, "No current filter") unless having || where
205:       o = {}
206:       o[:having] = SQL::BooleanExpression.invert(having) if having
207:       o[:where] = SQL::BooleanExpression.invert(where) if where
208:       clone(o)
209:     end
join_clause_sql (jc)

SQL fragment specifying a JOIN clause without ON or USING.

[show source]
     # File lib/sequel/dataset/sql.rb, line 235
235:     def join_clause_sql(jc)
236:       table = jc.table
237:       table_alias = jc.table_alias
238:       table_alias = nil if table == table_alias
239:       tref = table_ref(table)
240:       " #{join_type_sql(jc.join_type)} #{table_alias ? as_sql(tref, table_alias) : tref}"
241:     end
join_on_clause_sql (jc)

SQL fragment specifying a JOIN clause with ON.

[show source]
     # File lib/sequel/dataset/sql.rb, line 244
244:     def join_on_clause_sql(jc)
245:       "#{join_clause_sql(jc)} ON #{literal(filter_expr(jc.on))}"
246:     end
join_table (type, table, expr=nil, options={}) {|table_name, last_alias, @opts[:join] || []| ...}

Returns a joined dataset. Uses the following arguments:

  • type - The type of join to do (e.g. :inner)
  • table - Depends on type:
    • Dataset - a subselect is performed with an alias of tN for some value of N
    • Model (or anything responding to :table_name) - table.table_name
    • String, Symbol: table
  • expr - specifies conditions, depends on type:
    • Hash, Array with all two pairs - Assumes key (1st arg) is column of joined table (unless already qualified), and value (2nd arg) is column of the last joined or primary table (or the :implicit_qualifier option). To specify multiple conditions on a single joined table column, you must use an array. Uses a JOIN with an ON clause.
    • Array - If all members of the array are symbols, considers them as columns and uses a JOIN with a USING clause. Most databases will remove duplicate columns from the result set if this is used.
    • nil - If a block is not given, doesn’t use ON or USING, so the JOIN should be a NATURAL or CROSS join. If a block is given, uses a ON clause based on the block, see below.
    • Everything else - pretty much the same as a using the argument in a call to filter, so strings are considered literal, symbols specify boolean columns, and blockless filter expressions can be used. Uses a JOIN with an ON clause.
  • options - a hash of options, with any of the following keys:
    • :table_alias - the name of the table’s alias when joining, necessary for joining to the same table more than once. No alias is used by default.
    • :implicit_qualifier - The name to use for qualifying implicit conditions. By default, the last joined or primary table is used.
  • block - The block argument should only be given if a JOIN with an ON clause is used, in which case it yields the table alias/name for the table currently being joined, the table alias/name for the last joined (or first table), and an array of previous SQL::JoinClause.
[show source]
     # File lib/sequel/dataset/sql.rb, line 283
283:     def join_table(type, table, expr=nil, options={}, &block)
284:       using_join = expr.is_a?(Array) && !expr.empty? && expr.all?{|x| x.is_a?(Symbol)}
285:       if using_join && !supports_join_using?
286:         h = {}
287:         expr.each{|s| h[s] = s}
288:         return join_table(type, table, h, options)
289:       end
290: 
291:       if [Symbol, String].any?{|c| options.is_a?(c)}
292:         table_alias = options
293:         last_alias = nil 
294:       else
295:         table_alias = options[:table_alias]
296:         last_alias = options[:implicit_qualifier]
297:       end
298:       if Dataset === table
299:         if table_alias.nil?
300:           table_alias_num = (@opts[:num_dataset_sources] || 0) + 1
301:           table_alias = dataset_alias(table_alias_num)
302:         end
303:         table_name = table_alias
304:       else
305:         table = table.table_name if table.respond_to?(:table_name)
306:         table_name = table_alias || table
307:       end
308: 
309:       join = if expr.nil? and !block_given?
310:         SQL::JoinClause.new(type, table, table_alias)
311:       elsif using_join
312:         raise(Sequel::Error, "can't use a block if providing an array of symbols as expr") if block_given?
313:         SQL::JoinUsingClause.new(expr, type, table, table_alias)
314:       else
315:         last_alias ||= @opts[:last_joined_table] || first_source_alias
316:         if Sequel.condition_specifier?(expr)
317:           expr = expr.collect do |k, v|
318:             k = qualified_column_name(k, table_name) if k.is_a?(Symbol)
319:             v = qualified_column_name(v, last_alias) if v.is_a?(Symbol)
320:             [k,v]
321:           end
322:         end
323:         if block_given?
324:           expr2 = yield(table_name, last_alias, @opts[:join] || [])
325:           expr = expr ? SQL::BooleanExpression.new(:AND, expr, expr2) : expr2
326:         end
327:         SQL::JoinOnClause.new(expr, type, table, table_alias)
328:       end
329: 
330:       opts = {:join => (@opts[:join] || []) + [join], :last_joined_table => table_name}
331:       opts[:num_dataset_sources] = table_alias_num if table_alias_num
332:       clone(opts)
333:     end
join_using_clause_sql (jc)

SQL fragment specifying a JOIN clause with USING.

[show source]
     # File lib/sequel/dataset/sql.rb, line 249
249:     def join_using_clause_sql(jc)
250:       "#{join_clause_sql(jc)} USING (#{column_list(jc.using)})"
251:     end
last (*args, &block)

Reverses the order and then runs first. Note that this will not necessarily give you the last record in the dataset, unless you have an unambiguous order. If there is not currently an order for this dataset, raises an Error.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 143
143:     def last(*args, &block)
144:       raise(Error, 'No order specified') unless @opts[:order]
145:       reverse.first(*args, &block)
146:     end
limit (l, o = nil)

If given an integer, the dataset will contain only the first l results. If given a range, it will contain only those at offsets within that range. If a second argument is given, it is used as an offset.

  dataset.limit(10) # SQL: SELECT * FROM items LIMIT 10
  dataset.limit(10, 20) # SQL: SELECT * FROM items LIMIT 10 OFFSET 20
[show source]
     # File lib/sequel/dataset/query.rb, line 217
217:     def limit(l, o = nil)
218:       return from_self.limit(l, o) if @opts[:sql]
219: 
220:       if Range === l
221:         o = l.first
222:         l = l.last - l.first + (l.exclude_end? ? 0 : 1)
223:       end
224:       l = l.to_i
225:       raise(Error, 'Limits must be greater than or equal to 1') unless l >= 1
226:       opts = {:limit => l}
227:       if o
228:         o = o.to_i
229:         raise(Error, 'Offsets must be greater than or equal to 0') unless o >= 0
230:         opts[:offset] = o
231:       end
232:       clone(opts)
233:     end
literal (v)

Returns a literal representation of a value to be used as part of an SQL expression.

  dataset.literal("abc'def\\") #=> "'abc''def\\\\'"
  dataset.literal(:items__id) #=> "items.id"
  dataset.literal([1, 2, 3]) => "(1, 2, 3)"
  dataset.literal(DB[:items]) => "(SELECT * FROM items)"
  dataset.literal(:x + 1 > :y) => "((x + 1) > y)"

If an unsupported object is given, an exception is raised.

[show source]
     # File lib/sequel/dataset/sql.rb, line 345
345:     def literal(v)
346:       case v
347:       when String
348:         return v if v.is_a?(LiteralString)
349:         v.is_a?(SQL::Blob) ? literal_blob(v) : literal_string(v)
350:       when Symbol
351:         literal_symbol(v)
352:       when Integer
353:         literal_integer(v)
354:       when Hash
355:         literal_hash(v)
356:       when SQL::Expression
357:         literal_expression(v)
358:       when Float
359:         literal_float(v)
360:       when BigDecimal
361:         literal_big_decimal(v)
362:       when NilClass
363:         NULL
364:       when TrueClass
365:         literal_true
366:       when FalseClass
367:         literal_false
368:       when Array
369:         literal_array(v)
370:       when Time
371:         literal_time(v)
372:       when DateTime
373:         literal_datetime(v)
374:       when Date
375:         literal_date(v)
376:       when Dataset
377:         literal_dataset(v)
378:       else
379:         literal_other(v)
380:       end
381:     end
map (column=nil, &block)

Maps column values for each record in the dataset (if a column name is given), or performs the stock mapping functionality of Enumerable. Raises an error if both an argument and block are given. Examples:

  ds.map(:id) => [1, 2, 3, ...]
  ds.map{|r| r[:id] * 2} => [2, 4, 6, ...]
[show source]
     # File lib/sequel/dataset/convenience.rb, line 154
154:     def map(column=nil, &block)
155:       if column
156:         raise(Error, ARG_BLOCK_ERROR_MSG) if block
157:         super(){|r| r[column]}
158:       else
159:         super(&block)
160:       end
161:     end
max (column)

Returns the maximum value for the given column.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 164
164:     def max(column)
165:       aggregate_dataset.get{max(column)}
166:     end
min (column)

Returns the minimum value for the given column.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 169
169:     def min(column)
170:       aggregate_dataset.get{min(column)}
171:     end
multi_insert (hashes, opts={})

This is a front end for import that allows you to submit an array of hashes instead of arrays of columns and values:

  dataset.multi_insert([{:x => 1}, {:x => 2}])

Be aware that all hashes should have the same keys if you use this calling method, otherwise some columns could be missed or set to null instead of to default values.

You can also use the :slice or :commit_every option that import accepts.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 183
183:     def multi_insert(hashes, opts={})
184:       return if hashes.empty?
185:       columns = hashes.first.keys
186:       import(columns, hashes.map{|h| columns.map{|c| h[c]}}, opts)
187:     end
multi_insert_sql (columns, values)

Returns an array of insert statements for inserting multiple records. This method is used by multi_insert to format insert statements and expects a keys array and and an array of value arrays.

This method should be overridden by descendants if the support inserting multiple records in a single SQL statement.

[show source]
     # File lib/sequel/dataset/sql.rb, line 389
389:     def multi_insert_sql(columns, values)
390:       values.map{|r| insert_sql(columns, r)}
391:     end
naked ()

Returns a naked dataset clone - i.e. a dataset that returns records as hashes instead of calling the row proc.

[show source]
     # File lib/sequel/dataset.rb, line 140
140:     def naked
141:       ds = clone
142:       ds.row_proc = nil
143:       ds
144:     end
negative_boolean_constant_sql (constant)

SQL fragment for NegativeBooleanConstants

[show source]
     # File lib/sequel/dataset/sql.rb, line 394
394:     def negative_boolean_constant_sql(constant)
395:       "NOT #{boolean_constant_sql(constant)}"
396:     end
or (*cond, &block)

Adds an alternate filter to an existing filter using OR. If no filter exists an error is raised.

  dataset.filter(:a).or(:b) # SQL: SELECT * FROM items WHERE a OR b
[show source]
     # File lib/sequel/dataset/query.rb, line 239
239:     def or(*cond, &block)
240:       clause = (@opts[:having] ? :having : :where)
241:       raise(InvalidOperation, "No existing filter found.") unless @opts[clause]
242:       cond = cond.first if cond.size == 1
243:       clone(clause => SQL::BooleanExpression.new(:OR, @opts[clause], filter_expr(cond, &block)))
244:     end
order (*columns, &block)

Returns a copy of the dataset with the order changed. If a nil is given the returned dataset has no order. This can accept multiple arguments of varying kinds, and even SQL functions. If a block is given, it is treated as a virtual row block, similar to filter.

  ds.order(:name).sql #=> 'SELECT * FROM items ORDER BY name'
  ds.order(:a, :b).sql #=> 'SELECT * FROM items ORDER BY a, b'
  ds.order('a + b'.lit).sql #=> 'SELECT * FROM items ORDER BY a + b'
  ds.order(:a + :b).sql #=> 'SELECT * FROM items ORDER BY (a + b)'
  ds.order(:name.desc).sql #=> 'SELECT * FROM items ORDER BY name DESC'
  ds.order(:name.asc).sql #=> 'SELECT * FROM items ORDER BY name ASC'
  ds.order{|o| o.sum(:name)}.sql #=> 'SELECT * FROM items ORDER BY sum(name)'
  ds.order(nil).sql #=> 'SELECT * FROM items'
[show source]
     # File lib/sequel/dataset/query.rb, line 259
259:     def order(*columns, &block)
260:       columns += Array(Sequel.virtual_row(&block)) if block
261:       clone(:order => (columns.compact.empty?) ? nil : columns)
262:     end
order_by (*columns, &block)

Alias for order

order_more (*columns, &block)

Returns a copy of the dataset with the order columns added to the existing order.

  ds.order(:a).order(:b).sql #=> 'SELECT * FROM items ORDER BY b'
  ds.order(:a).order_more(:b).sql #=> 'SELECT * FROM items ORDER BY a, b'
[show source]
     # File lib/sequel/dataset/query.rb, line 270
270:     def order_more(*columns, &block)
271:       columns = @opts[:order] + columns if @opts[:order]
272:       order(*columns, &block)
273:     end
ordered_expression_sql (oe)

SQL fragment for the ordered expression, used in the ORDER BY clause.

[show source]
     # File lib/sequel/dataset/sql.rb, line 400
400:     def ordered_expression_sql(oe)
401:       "#{literal(oe.expression)} #{oe.descending ? 'DESC' : 'ASC'}"
402:     end
placeholder_literal_string_sql (pls)

SQL fragment for a literal string with placeholders

[show source]
     # File lib/sequel/dataset/sql.rb, line 405
405:     def placeholder_literal_string_sql(pls)
406:       args = pls.args
407:       s = if args.is_a?(Hash)
408:         re = /:(#{args.keys.map{|k| Regexp.escape(k.to_s)}.join('|')})\b/
409:         pls.str.gsub(re){literal(args[$1.to_sym])}
410:       else
411:         i = -1
412:         pls.str.gsub(QUESTION_MARK){literal(args.at(i+=1))}
413:       end
414:       s = "(#{s})" if pls.parens
415:       s
416:     end
prepare (type, name=nil, *values)

Prepare an SQL statement for later execution. This returns a clone of the dataset extended with PreparedStatementMethods, on which you can call call with the hash of bind variables to do substitution. The prepared statement is also stored in the associated database. The following usage is identical:

  ps = prepare(:select, :select_by_name)
  ps.call(:name=>'Blah')
  db.call(:select_by_name, :name=>'Blah')
[show source]
     # File lib/sequel/dataset/prepared_statements.rb, line 195
195:     def prepare(type, name=nil, *values)
196:       ps = to_prepared_statement(type, values)
197:       db.prepared_statements[name] = ps if name
198:       ps
199:     end
qualified_identifier_sql (qcr)

SQL fragment for the qualifed identifier, specifying a table and a column (or schema and table).

[show source]
     # File lib/sequel/dataset/sql.rb, line 420
420:     def qualified_identifier_sql(qcr)
421:       [qcr.table, qcr.column].map{|x| [SQL::QualifiedIdentifier, SQL::Identifier, Symbol].any?{|c| x.is_a?(c)} ? literal(x) : quote_identifier(x)}.join('.')
422:     end
qualify (table=first_source)

Qualify to the given table, or first source if not table is given.

[show source]
     # File lib/sequel/dataset/sql.rb, line 425
425:     def qualify(table=first_source)
426:       qualify_to(table)
427:     end
qualify_to (table)

Return a copy of the dataset with unqualified identifiers in the SELECT, WHERE, GROUP, HAVING, and ORDER clauses qualified by the given table. If no columns are currently selected, select all columns of the given table.

[show source]
     # File lib/sequel/dataset/sql.rb, line 433
433:     def qualify_to(table)
434:       o = @opts
435:       return clone if o[:sql]
436:       h = {}
437:       (o.keys & QUALIFY_KEYS).each do |k|
438:         h[k] = qualified_expression(o[k], table)
439:       end
440:       h[:select] = [SQL::ColumnAll.new(table)] if !o[:select] || o[:select].empty?
441:       clone(h)
442:     end
qualify_to_first_source ()

Qualify the dataset to its current first source. This is useful if you have unqualified identifiers in the query that all refer to the first source, and you want to join to another table which has columns with the same name as columns in the current dataset. See qualify_to.

[show source]
     # File lib/sequel/dataset/sql.rb, line 449
449:     def qualify_to_first_source
450:       qualify_to(first_source)
451:     end
quote_identifier (name)

Adds quoting to identifiers (columns and tables). If identifiers are not being quoted, returns name as a string. If identifiers are being quoted quote the name with quoted_identifier.

[show source]
     # File lib/sequel/dataset/sql.rb, line 456
456:     def quote_identifier(name)
457:       return name if name.is_a?(LiteralString)
458:       name = name.value if name.is_a?(SQL::Identifier)
459:       name = input_identifier(name)
460:       name = quoted_identifier(name) if quote_identifiers?
461:       name
462:     end
quote_identifiers? ()

Whether this dataset quotes identifiers.

[show source]
   # File lib/sequel/dataset/features.rb, line 4
4:     def quote_identifiers?
5:       @quote_identifiers
6:     end
quote_schema_table (table)

Separates the schema from the table and returns a string with them quoted (if quoting identifiers)

[show source]
     # File lib/sequel/dataset/sql.rb, line 466
466:     def quote_schema_table(table)
467:       schema, table = schema_and_table(table)
468:       "#{"#{quote_identifier(schema)}." if schema}#{quote_identifier(table)}"
469:     end
quoted_identifier (name)

This method quotes the given name with the SQL standard double quote. should be overridden by subclasses to provide quoting not matching the SQL standard, such as backtick (used by MySQL and SQLite).

[show source]
     # File lib/sequel/dataset/sql.rb, line 474
474:     def quoted_identifier(name)
475:       "\"#{name.to_s.gsub('"', '""')}\""
476:     end
range (column)

Returns a Range object made from the minimum and maximum values for the given column.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 191
191:     def range(column)
192:       if r = aggregate_dataset.select{[min(column).as(v1), max(column).as(v2)]}.first
193:         (r[:v1]..r[:v2])
194:       end
195:     end
requires_sql_standard_datetimes? ()

Whether the dataset requires SQL standard datetimes (false by default, as most allow strings with ISO 8601 format.

[show source]
    # File lib/sequel/dataset/features.rb, line 10
10:     def requires_sql_standard_datetimes?
11:       false
12:     end
reverse (*order)

Alias for reverse_order

reverse_order (*order)

Returns a copy of the dataset with the order reversed. If no order is given, the existing order is inverted.

[show source]
     # File lib/sequel/dataset/query.rb, line 277
277:     def reverse_order(*order)
278:       order(*invert_order(order.empty? ? @opts[:order] : order))
279:     end
schema_and_table (table_name)

Split the schema information from the table

[show source]
     # File lib/sequel/dataset/sql.rb, line 479
479:     def schema_and_table(table_name)
480:       sch = db.default_schema if db
481:       case table_name
482:       when Symbol
483:         s, t, a = split_symbol(table_name)
484:         [s||sch, t]
485:       when SQL::QualifiedIdentifier
486:         [table_name.table, table_name.column]
487:       when SQL::Identifier
488:         [sch, table_name.value]
489:       when String
490:         [sch, table_name]
491:       else
492:         raise Error, 'table_name should be a Symbol, SQL::QualifiedIdentifier, SQL::Identifier, or String'
493:       end
494:     end
select (*columns, &block)

Returns a copy of the dataset with the columns selected changed to the given columns. This also takes a virtual row block, similar to filter.

  dataset.select(:a) # SELECT a FROM items
  dataset.select(:a, :b) # SELECT a, b FROM items
  dataset.select{|o| o.a, o.sum(:b)} # SELECT a, sum(b) FROM items
[show source]
     # File lib/sequel/dataset/query.rb, line 289
289:     def select(*columns, &block)
290:       columns += Array(Sequel.virtual_row(&block)) if block
291:       m = []
292:       columns.map do |i|
293:         i.is_a?(Hash) ? m.concat(i.map{|k, v| SQL::AliasedExpression.new(k,v)}) : m << i
294:       end
295:       clone(:select => m)
296:     end
select_all ()

Returns a copy of the dataset selecting the wildcard.

  dataset.select(:a).select_all # SELECT * FROM items
[show source]
     # File lib/sequel/dataset/query.rb, line 301
301:     def select_all
302:       clone(:select => nil)
303:     end
select_hash (key_column, value_column)

Returns a hash with key_column values as keys and value_column values as values. Similar to to_hash, but only selects the two columns.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 199
199:     def select_hash(key_column, value_column)
200:       select(key_column, value_column).to_hash(hash_key_symbol(key_column), hash_key_symbol(value_column))
201:     end
select_map (column=nil, &block)

Selects the column given (either as an argument or as a block), and returns an array of all values of that column in the dataset. If you give a block argument that returns an array with multiple entries, the contents of the resulting array are undefined.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 207
207:     def select_map(column=nil, &block)
208:       ds = naked.ungraphed
209:       ds = if column
210:         raise(Error, ARG_BLOCK_ERROR_MSG) if block
211:         ds.select(column)
212:       else
213:         ds.select(&block)
214:       end
215:       ds.map{|r| r.values.first}
216:     end
select_more (*columns, &block)

Returns a copy of the dataset with the given columns added to the existing selected columns.

  dataset.select(:a).select(:b) # SELECT b FROM items
  dataset.select(:a).select_more(:b) # SELECT a, b FROM items
[show source]
     # File lib/sequel/dataset/query.rb, line 310
310:     def select_more(*columns, &block)
311:       columns = @opts[:select] + columns if @opts[:select]
312:       select(*columns, &block)
313:     end
select_order_map (column=nil, &block)

The same as select_map, but in addition orders the array by the column.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 219
219:     def select_order_map(column=nil, &block)
220:       ds = naked.ungraphed
221:       ds = if column
222:         raise(Error, ARG_BLOCK_ERROR_MSG) if block
223:         ds.select(column).order(unaliased_identifier(column))
224:       else
225:         ds.select(&block).order(&block)
226:       end
227:       ds.map{|r| r.values.first}
228:     end
select_sql ()

Formats a SELECT statement

  dataset.select_sql # => "SELECT * FROM items"
[show source]
     # File lib/sequel/dataset/sql.rb, line 499
499:     def select_sql
500:       return static_sql(@opts[:sql]) if @opts[:sql]
501:       clause_sql(:select)
502:     end
server (servr)

Set the server for this dataset to use. Used to pick a specific database shard to run a query against, or to override the default (which is SELECT uses :read_only database and all other queries use the :default database).

[show source]
     # File lib/sequel/dataset.rb, line 149
149:     def server(servr)
150:       clone(:server=>servr)
151:     end
set (*args)

Alias for update, but not aliased directly so subclasses don’t have to override both methods.

[show source]
    # File lib/sequel/dataset/actions.rb, line 81
81:     def set(*args)
82:       update(*args)
83:     end
set_defaults (hash)

Set the default values for insert and update statements. The values hash passed to insert or update are merged into this hash.

[show source]
     # File lib/sequel/dataset.rb, line 155
155:     def set_defaults(hash)
156:       clone(:defaults=>(@opts[:defaults]||{}).merge(hash))
157:     end
set_graph_aliases (graph_aliases)

This allows you to manually specify the graph aliases to use when using graph. You can use it to only select certain columns, and have those columns mapped to specific aliases in the result set. This is the equivalent of .select for a graphed dataset, and must be used instead of .select whenever graphing is used. Example:

  DB[:artists].graph(:albums, :artist_id=>:id).set_graph_aliases(:artist_name=>[:artists, :name], :album_name=>[:albums, :name], :forty_two=>[:albums, :fourtwo, 42]).first
  => {:artists=>{:name=>artists.name}, :albums=>{:name=>albums.name, :fourtwo=>42}}

Arguments:

  • graph_aliases - Should be a hash with keys being symbols of column aliases, and values being arrays with two or three elements. The first element of the array should be the table alias symbol, and the second should be the actual column name symbol. If the array has a third element, it is used as the value returned, instead of table_alias.column_name.
[show source]
     # File lib/sequel/dataset/graph.rb, line 182
182:     def set_graph_aliases(graph_aliases)
183:       ds = select(*graph_alias_columns(graph_aliases))
184:       ds.opts[:graph_aliases] = graph_aliases
185:       ds
186:     end
set_overrides (hash)

Set values that override hash arguments given to insert and update statements. This hash is merged into the hash provided to insert or update.

[show source]
     # File lib/sequel/dataset.rb, line 161
161:     def set_overrides(hash)
162:       clone(:overrides=>hash.merge(@opts[:overrides]||{}))
163:     end
single_record ()

Returns the first record in the dataset.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 231
231:     def single_record
232:       clone(:limit=>1).each{|r| return r}
233:       nil
234:     end
single_value ()

Returns the first value of the first record in the dataset. Returns nil if dataset is empty.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 238
238:     def single_value
239:       if r = naked.ungraphed.single_record
240:         r.values.first
241:       end
242:     end
sql ()

Same as select_sql, not aliased directly to make subclassing simpler.

[show source]
     # File lib/sequel/dataset/sql.rb, line 505
505:     def sql
506:       select_sql
507:     end
subscript_sql (s)

SQL fragment for specifying subscripts (SQL arrays)

[show source]
     # File lib/sequel/dataset/sql.rb, line 510
510:     def subscript_sql(s)
511:       "#{literal(s.f)}[#{expression_list(s.sub)}]"
512:     end
sum (column)

Returns the sum for the given column.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 245
245:     def sum(column)
246:       aggregate_dataset.get{sum(column)}
247:     end
supports_cte? ()

Whether the dataset supports common table expressions (the WITH clause).

[show source]
    # File lib/sequel/dataset/features.rb, line 15
15:     def supports_cte?
16:       select_clause_methods.include?(WITH_SUPPORTED)
17:     end
supports_distinct_on? ()

Whether the dataset supports the DISTINCT ON clause, true by default.

[show source]
    # File lib/sequel/dataset/features.rb, line 20
20:     def supports_distinct_on?
21:       true
22:     end
supports_intersect_except? ()

Whether the dataset supports the INTERSECT and EXCEPT compound operations, true by default.

[show source]
    # File lib/sequel/dataset/features.rb, line 25
25:     def supports_intersect_except?
26:       true
27:     end
supports_intersect_except_all? ()

Whether the dataset supports the INTERSECT ALL and EXCEPT ALL compound operations, true by default.

[show source]
    # File lib/sequel/dataset/features.rb, line 30
30:     def supports_intersect_except_all?
31:       true
32:     end
supports_is_true? ()

Whether the dataset supports the IS TRUE syntax.

[show source]
    # File lib/sequel/dataset/features.rb, line 35
35:     def supports_is_true?
36:       true
37:     end
supports_join_using? ()

Whether the dataset supports the JOIN table USING (column1, …) syntax.

[show source]
    # File lib/sequel/dataset/features.rb, line 40
40:     def supports_join_using?
41:       true
42:     end
supports_modifying_joins? ()

Whether modifying joined datasets is supported.

[show source]
    # File lib/sequel/dataset/features.rb, line 45
45:     def supports_modifying_joins?
46:       false
47:     end
supports_multiple_column_in? ()

Whether the IN/NOT IN operators support multiple columns when an array of values is given.

[show source]
    # File lib/sequel/dataset/features.rb, line 51
51:     def supports_multiple_column_in?
52:       true
53:     end
supports_timestamp_timezones? ()

Whether the dataset supports timezones in literal timestamps

[show source]
    # File lib/sequel/dataset/features.rb, line 56
56:     def supports_timestamp_timezones?
57:       false
58:     end
supports_timestamp_usecs? ()

Whether the dataset supports fractional seconds in literal timestamps

[show source]
    # File lib/sequel/dataset/features.rb, line 61
61:     def supports_timestamp_usecs?
62:       true
63:     end
supports_window_functions? ()

Whether the dataset supports window functions.

[show source]
    # File lib/sequel/dataset/features.rb, line 66
66:     def supports_window_functions?
67:       false
68:     end
to_csv (include_column_titles = true)

Returns a string in CSV format containing the dataset records. By default the CSV representation includes the column titles in the first line. You can turn that off by passing false as the include_column_titles argument.

This does not use a CSV library or handle quoting of values in any way. If any values in any of the rows could include commas or line endings, you shouldn’t use this.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 257
257:     def to_csv(include_column_titles = true)
258:       n = naked
259:       cols = n.columns
260:       csv = ''
261:       csv << "#{cols.join(COMMA_SEPARATOR)}\r\n" if include_column_titles
262:       n.each{|r| csv << "#{cols.collect{|c| r[c]}.join(COMMA_SEPARATOR)}\r\n"}
263:       csv
264:     end
to_hash (key_column, value_column = nil)

Returns a hash with one column used as key and another used as value. If rows have duplicate values for the key column, the latter row(s) will overwrite the value of the previous row(s). If the value_column is not given or nil, uses the entire hash as the value.

[show source]
     # File lib/sequel/dataset/convenience.rb, line 270
270:     def to_hash(key_column, value_column = nil)
271:       inject({}) do |m, r|
272:         m[r[key_column]] = value_column ? r[value_column] : r
273:         m
274:       end
275:     end
truncate ()

Truncates the dataset. Returns nil.

[show source]
    # File lib/sequel/dataset/actions.rb, line 86
86:     def truncate
87:       execute_ddl(truncate_sql)
88:     end
truncate_sql ()

SQL query to truncate the table

[show source]
     # File lib/sequel/dataset/sql.rb, line 515
515:     def truncate_sql
516:       if opts[:sql]
517:         static_sql(opts[:sql])
518:       else
519:         check_modification_allowed!
520:         raise(InvalidOperation, "Can't truncate filtered datasets") if opts[:where]
521:         _truncate_sql(source_list(opts[:from]))
522:       end
523:     end
unfiltered ()

Returns a copy of the dataset with no filters (HAVING or WHERE clause) applied.

  dataset.group(:a).having(:a=>1).where(:b).unfiltered # SELECT * FROM items GROUP BY a
[show source]
     # File lib/sequel/dataset/query.rb, line 318
318:     def unfiltered
319:       clone(:where => nil, :having => nil)
320:     end
ungraphed ()

Remove the splitting of results into subhashes. Also removes metadata related to graphing, so you should not call graph any tables to this dataset after calling this method.

[show source]
     # File lib/sequel/dataset/graph.rb, line 191
191:     def ungraphed
192:       clone(:graph=>nil)
193:     end
ungrouped ()

Returns a copy of the dataset with no grouping (GROUP or HAVING clause) applied.

  dataset.group(:a).having(:a=>1).where(:b).ungrouped # SELECT * FROM items WHERE b
[show source]
     # File lib/sequel/dataset/query.rb, line 325
325:     def ungrouped
326:       clone(:group => nil, :having => nil)
327:     end
union (dataset, opts={})

Adds a UNION clause using a second dataset object. A UNION compound dataset returns all rows in either the current dataset or the given dataset. Options:

  • :all - Set to true to use UNION ALL instead of UNION, so duplicate rows can occur
  • :from_self - Set to false to not wrap the returned dataset in a from_self, use with care.

    DB[:items].union(DB).sql #=> “SELECT * FROM items UNION SELECT * FROM other_items“

[show source]
     # File lib/sequel/dataset/query.rb, line 338
338:     def union(dataset, opts={})
339:       opts = {:all=>opts} unless opts.is_a?(Hash)
340:       compound_clone(:union, dataset, opts)
341:     end
unlimited ()

Returns a copy of the dataset with no limit or offset.

  dataset.limit(10, 20).unlimited # SELECT * FROM items
[show source]
     # File lib/sequel/dataset/query.rb, line 346
346:     def unlimited
347:       clone(:limit=>nil, :offset=>nil)
348:     end
unordered ()

Returns a copy of the dataset with no order.

  dataset.order(:a).unordered # SELECT * FROM items
[show source]
     # File lib/sequel/dataset/query.rb, line 353
353:     def unordered
354:       order(nil)
355:     end
update (values={})

Updates values for the dataset. The returned value is generally the number of rows updated, but that is adapter dependent. See update_sql.

[show source]
    # File lib/sequel/dataset/actions.rb, line 92
92:     def update(values={})
93:       execute_dui(update_sql(values))
94:     end
update_sql (values = {})

Formats an UPDATE statement using the given values.

  dataset.update_sql(:price => 100, :category => 'software') #=>
    "UPDATE items SET price = 100, category = 'software'"

Raises an error if the dataset is grouped or includes more than one table.

[show source]
     # File lib/sequel/dataset/sql.rb, line 532
532:     def update_sql(values = {})
533:       return static_sql(opts[:sql]) if opts[:sql]
534:       check_modification_allowed!
535:       clone(:values=>values)._update_sql
536:     end
where (*cond, &block)

Add a condition to the WHERE clause. See filter for argument types.

  dataset.group(:a).having(:a).filter(:b) # SELECT * FROM items GROUP BY a HAVING a AND b
  dataset.group(:a).having(:a).where(:b) # SELECT * FROM items WHERE b GROUP BY a HAVING a
[show source]
     # File lib/sequel/dataset/sql.rb, line 542
542:     def where(*cond, &block)
543:       _filter(:where, *cond, &block)
544:     end
window_function_sql (function, window)

The SQL fragment for the given window function’s function and window.

[show source]
     # File lib/sequel/dataset/sql.rb, line 566
566:     def window_function_sql(function, window)
567:       "#{literal(function)} OVER #{literal(window)}"
568:     end
window_sql (opts)

The SQL fragment for the given window’s options.

[show source]
     # File lib/sequel/dataset/sql.rb, line 547
547:     def window_sql(opts)
548:       raise(Error, 'This dataset does not support window functions') unless supports_window_functions?
549:       window = literal(opts[:window]) if opts[:window]
550:       partition = "PARTITION BY #{expression_list(Array(opts[:partition]))}" if opts[:partition]
551:       order = "ORDER BY #{expression_list(Array(opts[:order]))}" if opts[:order]
552:       frame = case opts[:frame]
553:         when nil
554:           nil
555:         when :all
556:           "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
557:         when :rows
558:           "ROWS UNBOUNDED PRECEDING"
559:         else
560:           raise Error, "invalid window frame clause, should be :all, :rows, or nil"
561:       end
562:       "(#{[window, partition, order, frame].compact.join(' ')})"
563:     end
with (name, dataset, opts={})

Add a simple common table expression (CTE) with the given name and a dataset that defines the CTE. A common table expression acts as an inline view for the query. Options:

  • :args - Specify the arguments/columns for the CTE, should be an array of symbols.
  • :recursive - Specify that this is a recursive CTE
[show source]
     # File lib/sequel/dataset/sql.rb, line 575
575:     def with(name, dataset, opts={})
576:       raise(Error, 'This datatset does not support common table expressions') unless supports_cte?
577:       clone(:with=>(@opts[:with]||[]) + [opts.merge(:name=>name, :dataset=>dataset)])
578:     end
with_recursive (name, nonrecursive, recursive, opts={})

Add a recursive common table expression (CTE) with the given name, a dataset that defines the nonrecursive part of the CTE, and a dataset that defines the recursive part of the CTE. Options:

  • :args - Specify the arguments/columns for the CTE, should be an array of symbols.
  • :union_all - Set to false to use UNION instead of UNION ALL combining the nonrecursive and recursive parts.
[show source]
     # File lib/sequel/dataset/sql.rb, line 585
585:     def with_recursive(name, nonrecursive, recursive, opts={})
586:       raise(Error, 'This datatset does not support common table expressions') unless supports_cte?
587:       clone(:with=>(@opts[:with]||[]) + [opts.merge(:recursive=>true, :name=>name, :dataset=>nonrecursive.union(recursive, {:all=>opts[:union_all] != false, :from_self=>false}))])
588:     end
with_sql (sql, *args)

Returns a copy of the dataset with the static SQL used. This is useful if you want to keep the same row_proc/graph, but change the SQL used to custom SQL.

  dataset.with_sql('SELECT * FROM foo') # SELECT * FROM foo
[show source]
     # File lib/sequel/dataset/sql.rb, line 594
594:     def with_sql(sql, *args)
595:       sql = SQL::PlaceholderLiteralString.new(sql, args) unless args.empty?
596:       clone(:sql=>sql)
597:     end

Protected instance methods

_insert_sql ()

Formats in INSERT statement using the stored columns and values.

[show source]
     # File lib/sequel/dataset/sql.rb, line 610
610:     def _insert_sql
611:       clause_sql(:insert)
612:     end
_update_sql ()

Formats an UPDATE statement using the stored values.

[show source]
     # File lib/sequel/dataset/sql.rb, line 615
615:     def _update_sql
616:       clause_sql(:update)
617:     end
compound_from_self ()

Return a from_self dataset if an order or limit is specified, so it works as expected with UNION, EXCEPT, and INTERSECT clauses.

[show source]
     # File lib/sequel/dataset/sql.rb, line 621
621:     def compound_from_self
622:       (@opts[:limit] || @opts[:order]) ? from_self : self
623:     end
options_overlap (opts)

Return true if the dataset has a non-nil value for any key in opts.

[show source]
     # File lib/sequel/dataset.rb, line 171
171:     def options_overlap(opts)
172:       !(@opts.collect{|k,v| k unless v.nil?}.compact & opts).empty?
173:     end
simple_select_all? ()

Whether this dataset is a simple SELECT * FROM table.

[show source]
     # File lib/sequel/dataset.rb, line 176
176:     def simple_select_all?
177:       o = @opts.reject{|k,v| v.nil? || NON_SQL_OPTIONS.include?(k)}
178:       o.length == 1 && (f = o[:from]) && f.length == 1 && f.first.is_a?(Symbol)
179:     end
to_prepared_statement (type, values=nil)

Return a cloned copy of the current dataset extended with PreparedStatementMethods, setting the type and modify values.

[show source]
     # File lib/sequel/dataset/prepared_statements.rb, line 205
205:     def to_prepared_statement(type, values=nil)
206:       ps = bind
207:       ps.extend(PreparedStatementMethods)
208:       ps.prepared_type = type
209:       ps.prepared_modify_values = values
210:       ps
211:     end