class Sequel::Dataset

Register an extension callback for Dataset objects. ext should be the extension name symbol, and mod should be a Module that will be included in the dataset's class. This also registers a Database extension that will extend all of the database's datasets.

   # File lib/sequel/dataset/query.rb
55 def self.register_extension(ext, mod=nil, &block)
56   if mod
57     raise(Error, "cannot provide both mod and block to Dataset.register_extension") if block
58     if mod.is_a?(Module)
59       block = proc{|ds| ds.extend(mod)}
60       Sequel::Database.register_extension(ext){|db| db.extend_datasets(mod)}
61       Sequel.synchronize{EXTENSION_MODULES[ext] = mod}
62     else
63       block = mod
64     end
65   end
66 
67   unless mod.is_a?(Module)
68     Sequel::Deprecation.deprecate("Providing a block or non-module to Sequel::Dataset.register_extension is deprecated and support for it will be removed in Sequel 6.")
69   end
70 
71   Sequel.synchronize{EXTENSIONS[ext] = block}
72 end

Returns a new clone of the dataset with the given options merged. If the options changed include options in COLUMN_CHANGE_OPTS, the cached columns are deleted. This method should generally not be called directly by user code.

   # File lib/sequel/dataset/query.rb
90 def clone(opts = nil || (return self))
91   # return self used above because clone is called by almost all
92   # other query methods, and it is the fastest approach
93   c = super(:freeze=>false)
94   c.opts.merge!(opts)
95   unless opts.each_key{|o| break if COLUMN_CHANGE_OPTS.include?(o)}
96     c.clear_columns_cache
97   end
98   c.freeze
99 end

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. If a block is given, it is treated as a virtual row block, similar to where. Raises an error if arguments are given and DISTINCT ON is not supported.

DB[:items].distinct # SQL: SELECT DISTINCT * FROM items
DB[:items].order(:id).distinct(:id) # SQL: SELECT DISTINCT ON (id) * FROM items ORDER BY id
DB[:items].order(:id).distinct{func(:id)} # SQL: SELECT DISTINCT ON (func(id)) * FROM items ORDER BY id

There is support for emulating the DISTINCT ON support in MySQL, but it does not support the ORDER of the dataset, and also doesn't work in many cases if the ONLY_FULL_GROUP_BY sql_mode is used, which is the default on MySQL 5.7.5+.

    # File lib/sequel/dataset/query.rb
129 def distinct(*args, &block)
130   virtual_row_columns(args, block)
131   if args.empty?
132     return self if opts[:distinct] == EMPTY_ARRAY
133     cached_dataset(:_distinct_ds){clone(:distinct => EMPTY_ARRAY)}
134   else
135     raise(InvalidOperation, "DISTINCT ON not supported") unless supports_distinct_on?
136     clone(:distinct => args.freeze)
137   end
138 end

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:

:alias

Use the given value as the from_self alias

: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[:other_items])
# SELECT * FROM (SELECT * FROM items EXCEPT SELECT * FROM other_items) AS t1

DB[:items].except(DB[:other_items], all: true, from_self: false)
# SELECT * FROM items EXCEPT ALL SELECT * FROM other_items

DB[:items].except(DB[:other_items], alias: :i)
# SELECT * FROM (SELECT * FROM items EXCEPT SELECT * FROM other_items) AS i

    # File lib/sequel/dataset/query.rb
157 def except(dataset, opts=OPTS)
158   raise(InvalidOperation, "EXCEPT not supported") unless supports_intersect_except?
159   raise(InvalidOperation, "EXCEPT ALL not supported") if opts[:all] && !supports_intersect_except_all?
160   compound_clone(:except, dataset, opts)
161 end

Performs the inverse of Dataset#where. Note that if you have multiple filter conditions, this is not the same as a negation of all conditions.

DB[:items].exclude(category: 'software')
# SELECT * FROM items WHERE (category != 'software')

DB[:items].exclude(category: 'software', id: 3)
# SELECT * FROM items WHERE ((category != 'software') OR (id != 3))

Also note that SQL uses 3-valued boolean logic (true, false, NULL), so the inverse of a true condition is a false condition, and will still not match rows that were NULL originally. If you take the earlier example:

DB[:items].exclude(category: 'software')
# SELECT * FROM items WHERE (category != 'software')

Note that this does not match rows where category is NULL. This is because NULL is an unknown value, and you do not know whether or not the NULL category is software. You can explicitly specify how to handle NULL values if you want:

DB[:items].exclude(Sequel.~(category: nil) & {category: 'software'})
# SELECT * FROM items WHERE ((category IS NULL) OR (category != 'software'))

    # File lib/sequel/dataset/query.rb
187 def exclude(*cond, &block)
188   add_filter(:where, cond, true, &block)
189 end

Inverts the given conditions and adds them to the HAVING clause.

DB[:items].select_group(:name).exclude_having{count(name) < 2}
# SELECT name FROM items GROUP BY name HAVING (count(name) >= 2)

See documentation for exclude for how inversion is handled in regards to SQL 3-valued boolean logic.

    # File lib/sequel/dataset/query.rb
198 def exclude_having(*cond, &block)
199   add_filter(:having, cond, true, &block)
200 end

Return a clone of the dataset loaded with the given dataset extensions. If no related extension file exists or the extension does not have specific support for Dataset objects, an error will be raised.

    # File lib/sequel/dataset/query.rb
206 def extension(*exts)
207   exts.each{|ext| Sequel.extension(ext) unless Sequel.synchronize{EXTENSIONS[ext]}}
208   mods = exts.map{|ext| Sequel.synchronize{EXTENSION_MODULES[ext]}}
209   if mods.all?
210     with_extend(*mods)
211   else
212     with_extend(DeprecatedSingletonClassMethods).extension(*exts)
213   end
214 end

Alias for where.

    # File lib/sequel/dataset/query.rb
226 def filter(*cond, &block)
227   where(*cond, &block)
228 end

Returns a cloned dataset with a :update lock style.

DB[:table].for_update # SELECT * FROM table FOR UPDATE

    # File lib/sequel/dataset/query.rb
233 def for_update
234   cached_lock_style_dataset(:_for_update_ds, :update)
235 end

Returns a copy of the dataset with the source changed. If no source is given, removes all tables. If multiple sources are given, it is the same as using a CROSS JOIN (cartesian product) between all tables. If a block is given, it is treated as a virtual row block, similar to where.

DB[:items].from # SQL: SELECT *
DB[:items].from(:blah) # SQL: SELECT * FROM blah
DB[:items].from(:blah, :foo) # SQL: SELECT * FROM blah, foo
DB[:items].from{fun(arg)} # SQL: SELECT * FROM fun(arg)

    # File lib/sequel/dataset/query.rb
246 def from(*source, &block)
247   virtual_row_columns(source, block)
248   table_alias_num = 0
249   ctes = nil
250   source.map! do |s|
251     case s
252     when Dataset
253       if hoist_cte?(s)
254         ctes ||= []
255         ctes += s.opts[:with]
256         s = s.clone(:with=>nil)
257       end
258       SQL::AliasedExpression.new(s, dataset_alias(table_alias_num+=1))
259     when Symbol
260       sch, table, aliaz = split_symbol(s)
261       if aliaz
262         s = sch ? SQL::QualifiedIdentifier.new(sch, table) : SQL::Identifier.new(table)
263         SQL::AliasedExpression.new(s, aliaz.to_sym)
264       else
265         s
266       end
267     else
268       s
269     end
270   end
271   o = {:from=>source.empty? ? nil : source.freeze}
272   o[:with] = ((opts[:with] || EMPTY_ARRAY) + ctes).freeze if ctes
273   o[:num_dataset_sources] = table_alias_num if table_alias_num > 0
274   clone(o)
275 end

Returns a dataset selecting from the current dataset. Options:

:alias

Controls the alias of the table

:column_aliases

Also aliases columns, using derived column lists. Only used in conjunction with :alias.

ds = DB[:items].order(:name).select(:id, :name)
# SELECT id,name FROM items ORDER BY name

ds.from_self
# SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS t1

ds.from_self(alias: :foo)
# SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS foo

ds.from_self(alias: :foo, column_aliases: [:c1, :c2])
# SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS foo(c1, c2)

    # File lib/sequel/dataset/query.rb
294 def from_self(opts=OPTS)
295   fs = {}
296   @opts.keys.each{|k| fs[k] = nil unless non_sql_option?(k)}
297   pr = proc do
298     c = clone(fs).from(opts[:alias] ? as(opts[:alias], opts[:column_aliases]) : self)
299     if cols = _columns
300       c.send(:columns=, cols)
301     end
302     c
303   end
304 
305   opts.empty? ? cached_dataset(:_from_self_ds, &pr) : pr.call
306 end

Match any of the columns to any of the patterns. The terms can be strings (which use LIKE) or regular expressions if the database supports that. Note that the total number of pattern matches will be Array(columns).length * Array(terms).length, which could cause performance issues.

Options (all are boolean):

:all_columns

All columns must be matched to any of the given patterns.

:all_patterns

All patterns must match at least one of the columns.

:case_insensitive

Use a case insensitive pattern match (the default is case sensitive if the database supports it).

If both :all_columns and :all_patterns are true, all columns must match all patterns.

Examples:

dataset.grep(:a, '%test%')
# SELECT * FROM items WHERE (a LIKE '%test%' ESCAPE '\')

dataset.grep([:a, :b], %w'%test% foo')
# SELECT * FROM items WHERE ((a LIKE '%test%' ESCAPE '\') OR (a LIKE 'foo' ESCAPE '\')
#   OR (b LIKE '%test%' ESCAPE '\') OR (b LIKE 'foo' ESCAPE '\'))

dataset.grep([:a, :b], %w'%foo% %bar%', all_patterns: true)
# SELECT * FROM a WHERE (((a LIKE '%foo%' ESCAPE '\') OR (b LIKE '%foo%' ESCAPE '\'))
#   AND ((a LIKE '%bar%' ESCAPE '\') OR (b LIKE '%bar%' ESCAPE '\')))

dataset.grep([:a, :b], %w'%foo% %bar%', all_columns: true)
# SELECT * FROM a WHERE (((a LIKE '%foo%' ESCAPE '\') OR (a LIKE '%bar%' ESCAPE '\'))
#   AND ((b LIKE '%foo%' ESCAPE '\') OR (b LIKE '%bar%' ESCAPE '\')))

dataset.grep([:a, :b], %w'%foo% %bar%', all_patterns: true, all_columns: true)
# SELECT * FROM a WHERE ((a LIKE '%foo%' ESCAPE '\') AND (b LIKE '%foo%' ESCAPE '\')
#   AND (a LIKE '%bar%' ESCAPE '\') AND (b LIKE '%bar%' ESCAPE '\'))

    # File lib/sequel/dataset/query.rb
343 def grep(columns, patterns, opts=OPTS)
344   column_op = opts[:all_columns] ? :AND : :OR
345   if opts[:all_patterns]
346     conds = Array(patterns).map do |pat|
347       SQL::BooleanExpression.new(column_op, *Array(columns).map{|c| SQL::StringExpression.like(c, pat, opts)})
348     end
349     where(SQL::BooleanExpression.new(:AND, *conds))
350   else
351     conds = Array(columns).map do |c|
352       SQL::BooleanExpression.new(:OR, *Array(patterns).map{|pat| SQL::StringExpression.like(c, pat, opts)})
353     end
354     where(SQL::BooleanExpression.new(column_op, *conds))
355   end
356 end

Returns a copy of the dataset with the results grouped by the value of the given columns. If a block is given, it is treated as a virtual row block, similar to where.

DB[:items].group(:id) # SELECT * FROM items GROUP BY id
DB[:items].group(:id, :name) # SELECT * FROM items GROUP BY id, name
DB[:items].group{[a, sum(b)]} # SELECT * FROM items GROUP BY a, sum(b)

    # File lib/sequel/dataset/query.rb
365 def group(*columns, &block)
366   virtual_row_columns(columns, block)
367   clone(:group => (columns.compact.empty? ? nil : columns.freeze))
368 end

Returns a dataset grouped by the given column with count by group. Column aliases may be supplied, and will be included in the select clause. If a block is given, it is treated as a virtual row block, similar to where.

Examples:

DB[:items].group_and_count(:name).all
# SELECT name, count(*) AS count FROM items GROUP BY name
# => [{:name=>'a', :count=>1}, ...]

DB[:items].group_and_count(:first_name, :last_name).all
# SELECT first_name, last_name, count(*) AS count FROM items GROUP BY first_name, last_name
# => [{:first_name=>'a', :last_name=>'b', :count=>1}, ...]

DB[:items].group_and_count(Sequel[:first_name].as(:name)).all
# SELECT first_name AS name, count(*) AS count FROM items GROUP BY first_name
# => [{:name=>'a', :count=>1}, ...]

DB[:items].group_and_count{substr(:first_name, 1, 1).as(:initial)}.all
# SELECT substr(first_name, 1, 1) AS initial, count(*) AS count FROM items GROUP BY substr(first_name, 1, 1)
# => [{:initial=>'a', :count=>1}, ...]

    # File lib/sequel/dataset/query.rb
396 def group_and_count(*columns, &block)
397   select_group(*columns, &block).select_append(COUNT_OF_ALL_AS_COUNT)
398 end

Returns a copy of the dataset with the given columns added to the list of existing columns to group on. If no existing columns are present this method simply sets the columns as the initial ones to group on.

DB[:items].group_append(:b) # SELECT * FROM items GROUP BY b
DB[:items].group(:a).group_append(:b) # SELECT * FROM items GROUP BY a, b

    # File lib/sequel/dataset/query.rb
406 def group_append(*columns, &block)
407   columns = @opts[:group] + columns if @opts[:group]
408   group(*columns, &block)
409 end

Alias of group

    # File lib/sequel/dataset/query.rb
371 def group_by(*columns, &block)
372   group(*columns, &block)
373 end

Adds the appropriate CUBE syntax to GROUP BY.

    # File lib/sequel/dataset/query.rb
412 def group_cube
413   raise Error, "GROUP BY CUBE not supported on #{db.database_type}" unless supports_group_cube?
414   clone(:group_options=>:cube)
415 end

Adds the appropriate ROLLUP syntax to GROUP BY.

    # File lib/sequel/dataset/query.rb
418 def group_rollup
419   raise Error, "GROUP BY ROLLUP not supported on #{db.database_type}" unless supports_group_rollup?
420   clone(:group_options=>:rollup)
421 end

Adds the appropriate GROUPING SETS syntax to GROUP BY.

    # File lib/sequel/dataset/query.rb
424 def grouping_sets
425   raise Error, "GROUP BY GROUPING SETS not supported on #{db.database_type}" unless supports_grouping_sets?
426   clone(:group_options=>:"grouping sets")
427 end

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

DB[:items].group(:sum).having(sum: 10)
# SELECT * FROM items GROUP BY sum HAVING (sum = 10)

    # File lib/sequel/dataset/query.rb
433 def having(*cond, &block)
434   add_filter(:having, cond, &block)
435 end

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:

:alias

Use the given value as the from_self alias

: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[:other_items])
# SELECT * FROM (SELECT * FROM items INTERSECT SELECT * FROM other_items) AS t1

DB[:items].intersect(DB[:other_items], all: true, from_self: false)
# SELECT * FROM items INTERSECT ALL SELECT * FROM other_items

DB[:items].intersect(DB[:other_items], alias: :i)
# SELECT * FROM (SELECT * FROM items INTERSECT SELECT * FROM other_items) AS i

    # File lib/sequel/dataset/query.rb
454 def intersect(dataset, opts=OPTS)
455   raise(InvalidOperation, "INTERSECT not supported") unless supports_intersect_except?
456   raise(InvalidOperation, "INTERSECT ALL not supported") if opts[:all] && !supports_intersect_except_all?
457   compound_clone(:intersect, dataset, opts)
458 end

Inverts the current WHERE and HAVING clauses. If there is neither a WHERE or HAVING clause, adds a WHERE clause that is always false.

DB[:items].where(category: 'software').invert
# SELECT * FROM items WHERE (category != 'software')

DB[:items].where(category: 'software', id: 3).invert
# SELECT * FROM items WHERE ((category != 'software') OR (id != 3))

See documentation for exclude for how inversion is handled in regards to SQL 3-valued boolean logic.

    # File lib/sequel/dataset/query.rb
471 def invert
472   cached_dataset(:_invert_ds) do
473     having, where = @opts.values_at(:having, :where)
474     if having.nil? && where.nil?
475       where(false)
476     else
477       o = {}
478       o[:having] = SQL::BooleanExpression.invert(having) if having
479       o[:where] = SQL::BooleanExpression.invert(where) if where
480       clone(o)
481     end
482   end
483 end

Alias of inner_join

    # File lib/sequel/dataset/query.rb
486 def join(*args, &block)
487   inner_join(*args, &block)
488 end

Returns a joined dataset. Not usually called directly, users should use the appropriate join method (e.g. join, left_join, natural_join, cross_join) which fills in the type argument.

Takes the following arguments:

type

The type of join to do (e.g. :inner)

table

table to join into the current dataset. Generally one of the following types:

String, Symbol

identifier used as table or view name

Dataset

a subselect is performed with an alias of tN for some value of N

SQL::Function

set returning function

SQL::AliasedExpression

already aliased expression. Uses given alias unless overridden by the :table_alias option.

expr

conditions used when joining, depends on type:

Hash, Array of 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 an ON clause based on the block, see below.

otherwise

Treats the argument as a filter expression, so strings are considered literal, symbols specify boolean columns, and Sequel expressions can be used. Uses a JOIN with an ON clause.

options

a hash of options, with the following keys supported:

:table_alias

Override the table alias used when joining. In general you shouldn't use this option, you should provide the appropriate SQL::AliasedExpression as the table argument.

:implicit_qualifier

The name to use for qualifying implicit conditions. By default, the last joined or primary table is used.

:join_using

Force the using of JOIN USING, even if expr is not an array of symbols.

:reset_implicit_qualifier

Can set to false to ignore this join when future joins determine qualifier for implicit conditions.

:qualify

Can be set to false to not do any implicit qualification. Can be set to :deep to use the Qualifier AST Transformer, which will attempt to qualify subexpressions of the expression tree. Can be set to :symbol to only qualify symbols. Defaults to the value of default_join_table_qualification.

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. Unlike where, this block is not treated as a virtual row block.

Examples:

DB[:a].join_table(:cross, :b)
# SELECT * FROM a CROSS JOIN b

DB[:a].join_table(:inner, DB[:b], c: d)
# SELECT * FROM a INNER JOIN (SELECT * FROM b) AS t1 ON (t1.c = a.d)

DB[:a].join_table(:left, Sequel[:b].as(:c), [:d])
# SELECT * FROM a LEFT JOIN b AS c USING (d)

DB[:a].natural_join(:b).join_table(:inner, :c) do |ta, jta, js|
  (Sequel.qualify(ta, :d) > Sequel.qualify(jta, :e)) & {Sequel.qualify(ta, :f)=>DB.from(js.first.table).select(:g)}
end
# SELECT * FROM a NATURAL JOIN b INNER JOIN c
#   ON ((c.d > b.e) AND (c.f IN (SELECT g FROM b)))

    # File lib/sequel/dataset/query.rb
550 def join_table(type, table, expr=nil, options=OPTS, &block)
551   if hoist_cte?(table)
552     s, ds = hoist_cte(table)
553     return s.join_table(type, ds, expr, options, &block)
554   end
555 
556   using_join = options[:join_using] || (expr.is_a?(Array) && !expr.empty? && expr.all?{|x| x.is_a?(Symbol)})
557   if using_join && !supports_join_using?
558     h = {}
559     expr.each{|e| h[e] = e}
560     return join_table(type, table, h, options)
561   end
562 
563   table_alias = options[:table_alias]
564 
565   if table.is_a?(SQL::AliasedExpression)
566     table_expr = if table_alias
567       SQL::AliasedExpression.new(table.expression, table_alias, table.columns)
568     else
569       table
570     end
571     table = table_expr.expression
572     table_name = table_alias = table_expr.alias
573   elsif table.is_a?(Dataset)
574     if table_alias.nil?
575       table_alias_num = (@opts[:num_dataset_sources] || 0) + 1
576       table_alias = dataset_alias(table_alias_num)
577     end
578     table_name = table_alias
579     table_expr = SQL::AliasedExpression.new(table, table_alias)
580   else
581     table, implicit_table_alias = split_alias(table)
582     table_alias ||= implicit_table_alias
583     table_name = table_alias || table
584     table_expr = table_alias ? SQL::AliasedExpression.new(table, table_alias) : table
585   end
586 
587   join = if expr.nil? and !block
588     SQL::JoinClause.new(type, table_expr)
589   elsif using_join
590     raise(Sequel::Error, "can't use a block if providing an array of symbols as expr") if block
591     SQL::JoinUsingClause.new(expr, type, table_expr)
592   else
593     last_alias = options[:implicit_qualifier] || @opts[:last_joined_table] || first_source_alias
594     qualify_type = options[:qualify]
595     if Sequel.condition_specifier?(expr)
596       expr = expr.map do |k, v|
597         qualify_type = default_join_table_qualification if qualify_type.nil?
598         case qualify_type
599         when false
600           nil # Do no qualification
601         when :deep
602           k = Sequel::Qualifier.new(table_name).transform(k)
603           v = Sequel::Qualifier.new(last_alias).transform(v)
604         else
605           k = qualified_column_name(k, table_name) if k.is_a?(Symbol)
606           v = qualified_column_name(v, last_alias) if v.is_a?(Symbol)
607         end
608         [k,v]
609       end
610       expr = SQL::BooleanExpression.from_value_pairs(expr)
611     end
612     if block
613       expr2 = yield(table_name, last_alias, @opts[:join] || EMPTY_ARRAY)
614       expr = expr ? SQL::BooleanExpression.new(:AND, expr, expr2) : expr2
615     end
616     SQL::JoinOnClause.new(expr, type, table_expr)
617   end
618 
619   opts = {:join => ((@opts[:join] || EMPTY_ARRAY) + [join]).freeze}
620   opts[:last_joined_table] = table_name unless options[:reset_implicit_qualifier] == false
621   opts[:num_dataset_sources] = table_alias_num if table_alias_num
622   clone(opts)
623 end

Marks this dataset as a lateral dataset. If used in another dataset's FROM or JOIN clauses, it will surround the subquery with LATERAL to enable it to deal with previous tables in the query:

DB.from(:a, DB[:b].where(Sequel[:a][:c]=>Sequel[:b][:d]).lateral)
# SELECT * FROM a, LATERAL (SELECT * FROM b WHERE (a.c = b.d))

    # File lib/sequel/dataset/query.rb
644 def lateral
645   return self if opts[:lateral]
646   cached_dataset(:_lateral_ds){clone(:lateral=>true)}
647 end

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. To use an offset without a limit, pass nil as the first argument.

DB[:items].limit(10) # SELECT * FROM items LIMIT 10
DB[:items].limit(10, 20) # SELECT * FROM items LIMIT 10 OFFSET 20
DB[:items].limit(10...20) # SELECT * FROM items LIMIT 10 OFFSET 10
DB[:items].limit(10..20) # SELECT * FROM items LIMIT 11 OFFSET 10
DB[:items].limit(nil, 20) # SELECT * FROM items OFFSET 20

    # File lib/sequel/dataset/query.rb
659 def limit(l, o = (no_offset = true; nil))
660   return from_self.limit(l, o) if @opts[:sql]
661 
662   if l.is_a?(Range)
663     no_offset = false
664     o = l.first
665     l = l.last - l.first + (l.exclude_end? ? 0 : 1)
666   end
667   l = l.to_i if l.is_a?(String) && !l.is_a?(LiteralString)
668   if l.is_a?(Integer)
669     raise(Error, 'Limits must be greater than or equal to 1') unless l >= 1
670   end
671 
672   ds = clone(:limit=>l)
673   ds = ds.offset(o) unless no_offset
674   ds
675 end

Returns a cloned dataset with the given lock style. If style is a string, it will be used directly. You should never pass a string to this method that is derived from user input, as that can lead to SQL injection.

A symbol may be used for database independent locking behavior, but all supported symbols have separate methods (e.g. for_update).

DB[:items].lock_style('FOR SHARE NOWAIT')
# SELECT * FROM items FOR SHARE NOWAIT
DB[:items].lock_style('FOR UPDATE OF table1 SKIP LOCKED')
# SELECT * FROM items FOR UPDATE OF table1 SKIP LOCKED

    # File lib/sequel/dataset/query.rb
689 def lock_style(style)
690   clone(:lock => style)
691 end

Return a dataset with a WHEN MATCHED THEN DELETE clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_delete
# WHEN MATCHED THEN DELETE

merge_delete{a > 30}
# WHEN MATCHED AND (a > 30) THEN DELETE

    # File lib/sequel/dataset/query.rb
702 def merge_delete(&block)
703   _merge_when(:type=>:delete, &block)
704 end

Return a dataset with a WHEN NOT MATCHED THEN INSERT clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

The arguments provided can be any arguments that would be accepted by insert.

merge_insert(i1: :i2, a: Sequel[:b]+11)
# WHEN NOT MATCHED THEN INSERT (i1, a) VALUES (i2, (b + 11))

merge_insert(:i2, Sequel[:b]+11){a > 30}
# WHEN NOT MATCHED AND (a > 30) THEN INSERT VALUES (i2, (b + 11))

    # File lib/sequel/dataset/query.rb
718 def merge_insert(*values, &block)
719   _merge_when(:type=>:insert, :values=>values, &block)
720 end

Return a dataset with a WHEN MATCHED THEN UPDATE clause added to the MERGE statement. If a block is passed, treat it as a virtual row and use it as additional conditions for the match.

merge_update(i1: Sequel[:i1]+:i2+10, a: Sequel[:a]+:b+20)
# WHEN MATCHED THEN UPDATE SET i1 = (i1 + i2 + 10), a = (a + b + 20)

merge_update(i1: :i2){a > 30}
# WHEN MATCHED AND (a > 30) THEN UPDATE SET i1 = i2

    # File lib/sequel/dataset/query.rb
731 def merge_update(values, &block)
732   _merge_when(:type=>:update, :values=>values, &block)
733 end

Return a dataset with the source and join condition to use for the MERGE statement.

merge_using(:m2, i1: :i2)
# USING m2 ON (i1 = i2)

    # File lib/sequel/dataset/query.rb
739 def merge_using(source, join_condition)
740   clone(:merge_using => [source, join_condition].freeze)
741 end

Returns a cloned dataset without a row_proc.

ds = DB[:items].with_row_proc(:invert.to_proc)
ds.all # => [{2=>:id}]
ds.naked.all # => [{:id=>2}]

    # File lib/sequel/dataset/query.rb
748 def naked
749   return self unless opts[:row_proc]
750   cached_dataset(:_naked_ds){with_row_proc(nil)}
751 end

Returns a copy of the dataset that will raise a DatabaseLockTimeout instead of waiting for rows that are locked by another transaction

DB[:items].for_update.nowait
# SELECT * FROM items FOR UPDATE NOWAIT

    # File lib/sequel/dataset/query.rb
758 def nowait
759   return self if opts[:nowait]
760   cached_dataset(:_nowait_ds) do
761     raise(Error, 'This dataset does not support raises errors instead of waiting for locked rows') unless supports_nowait?
762     clone(:nowait=>true)
763   end
764 end

Returns a copy of the dataset with a specified order. Can be safely combined with limit. If you call limit with an offset, it will override the offset if you've called offset first.

DB[:items].offset(10) # SELECT * FROM items OFFSET 10

    # File lib/sequel/dataset/query.rb
771 def offset(o)
772   o = o.to_i if o.is_a?(String) && !o.is_a?(LiteralString)
773   if o.is_a?(Integer)
774     raise(Error, 'Offsets must be greater than or equal to 0') unless o >= 0
775   end
776   clone(:offset => o)
777 end

Adds an alternate filter to an existing WHERE clause using OR. If there is no WHERE clause, then the default is WHERE true, and OR would be redundant, so return the dataset in that case.

DB[:items].where(:a).or(:b) # SELECT * FROM items WHERE a OR b
DB[:items].or(:b) # SELECT * FROM items

    # File lib/sequel/dataset/query.rb
785 def or(*cond, &block)
786   if @opts[:where].nil?
787     self
788   else
789     add_filter(:where, cond, false, :OR, &block)
790   end
791 end

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

DB[:items].order(:name) # SELECT * FROM items ORDER BY name
DB[:items].order(:a, :b) # SELECT * FROM items ORDER BY a, b
DB[:items].order(Sequel.lit('a + b')) # SELECT * FROM items ORDER BY a + b
DB[:items].order(Sequel[:a] + :b) # SELECT * FROM items ORDER BY (a + b)
DB[:items].order(Sequel.desc(:name)) # SELECT * FROM items ORDER BY name DESC
DB[:items].order(Sequel.asc(:name, nulls: :last)) # SELECT * FROM items ORDER BY name ASC NULLS LAST
DB[:items].order{sum(name).desc} # SELECT * FROM items ORDER BY sum(name) DESC
DB[:items].order(nil) # SELECT * FROM items

    # File lib/sequel/dataset/query.rb
807 def order(*columns, &block)
808   virtual_row_columns(columns, block)
809   clone(:order => (columns.compact.empty?) ? nil : columns.freeze)
810 end

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

DB[:items].order(:a).order(:b) # SELECT * FROM items ORDER BY b
DB[:items].order(:a).order_append(:b) # SELECT * FROM items ORDER BY a, b

    # File lib/sequel/dataset/query.rb
817 def order_append(*columns, &block)
818   columns = @opts[:order] + columns if @opts[:order]
819   order(*columns, &block)
820 end

Alias of order

    # File lib/sequel/dataset/query.rb
823 def order_by(*columns, &block)
824   order(*columns, &block)
825 end

Alias of order_append.

    # File lib/sequel/dataset/query.rb
828 def order_more(*columns, &block)
829   order_append(*columns, &block)
830 end

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

DB[:items].order(:a).order(:b) # SELECT * FROM items ORDER BY b
DB[:items].order(:a).order_prepend(:b) # SELECT * FROM items ORDER BY b, a

    # File lib/sequel/dataset/query.rb
837 def order_prepend(*columns, &block)
838   ds = order(*columns, &block)
839   @opts[:order] ? ds.order_append(*@opts[:order]) : ds
840 end

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

DB[:items].where(id: 1).qualify
# SELECT items.* FROM items WHERE (items.id = 1)

DB[:items].where(id: 1).qualify(:i)
# SELECT i.* FROM items WHERE (i.id = 1)

    # File lib/sequel/dataset/query.rb
849 def qualify(table=(cache=true; first_source))
850   o = @opts
851   return self if o[:sql]
852 
853   pr = proc do
854     h = {}
855     (o.keys & QUALIFY_KEYS).each do |k|
856       h[k] = qualified_expression(o[k], table)
857     end
858     h[:select] = [SQL::ColumnAll.new(table)].freeze if !o[:select] || o[:select].empty?
859     clone(h)
860   end
861 
862   cache ? cached_dataset(:_qualify_ds, &pr) : pr.call
863 end

Modify the RETURNING clause, only supported on a few databases. If returning is used, instead of insert returning the autogenerated primary key or update/delete returning the number of modified rows, results are returned using fetch_rows.

DB[:items].returning # RETURNING *
DB[:items].returning(nil) # RETURNING NULL
DB[:items].returning(:id, :name) # RETURNING id, name

DB[:items].returning.insert(a: 1) do |hash|
  # hash for each row inserted, with values for all columns
end
DB[:items].returning.update(a: 1) do |hash|
  # hash for each row updated, with values for all columns
end
DB[:items].returning.delete(a: 1) do |hash|
  # hash for each row deleted, with values for all columns
end

    # File lib/sequel/dataset/query.rb
883 def returning(*values)
884   if values.empty?
885     return self if opts[:returning] == EMPTY_ARRAY
886     cached_dataset(:_returning_ds) do
887       raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert)
888       clone(:returning=>EMPTY_ARRAY)
889     end
890   else
891     raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert)
892     clone(:returning=>values.freeze)
893   end
894 end

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

DB[:items].reverse(:id) # SELECT * FROM items ORDER BY id DESC
DB[:items].reverse{foo(bar)} # SELECT * FROM items ORDER BY foo(bar) DESC
DB[:items].order(:id).reverse # SELECT * FROM items ORDER BY id DESC
DB[:items].order(:id).reverse(Sequel.desc(:name)) # SELECT * FROM items ORDER BY name ASC

    # File lib/sequel/dataset/query.rb
903 def reverse(*order, &block)
904   if order.empty? && !block
905     cached_dataset(:_reverse_ds){order(*invert_order(@opts[:order]))}
906   else
907     virtual_row_columns(order, block)
908     order(*invert_order(order.empty? ? @opts[:order] : order.freeze))
909   end
910 end

Alias of reverse

    # File lib/sequel/dataset/query.rb
913 def reverse_order(*order, &block)
914   reverse(*order, &block)
915 end

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

DB[:items].select(:a) # SELECT a FROM items
DB[:items].select(:a, :b) # SELECT a, b FROM items
DB[:items].select{[a, sum(b)]} # SELECT a, sum(b) FROM items

    # File lib/sequel/dataset/query.rb
924 def select(*columns, &block)
925   virtual_row_columns(columns, block)
926   clone(:select => columns.freeze)
927 end

Returns a copy of the dataset selecting the wildcard if no arguments are given. If arguments are given, treat them as tables and select all columns (using the wildcard) from each table.

DB[:items].select(:a).select_all # SELECT * FROM items
DB[:items].select_all(:items) # SELECT items.* FROM items
DB[:items].select_all(:items, :foo) # SELECT items.*, foo.* FROM items

    # File lib/sequel/dataset/query.rb
936 def select_all(*tables)
937   if tables.empty?
938     return self unless opts[:select]
939     cached_dataset(:_select_all_ds){clone(:select => nil)}
940   else
941     select(*tables.map{|t| i, a = split_alias(t); a || i}.map!{|t| SQL::ColumnAll.new(t)}.freeze)
942   end
943 end

Returns a copy of the dataset with the given columns added to the existing selected columns. If no columns are currently selected, it will select the columns given in addition to *.

DB[:items].select(:a).select(:b) # SELECT b FROM items
DB[:items].select(:a).select_append(:b) # SELECT a, b FROM items
DB[:items].select_append(:b) # SELECT *, b FROM items

    # File lib/sequel/dataset/query.rb
952 def select_append(*columns, &block)
953   virtual_row_columns(columns, block)
954   select(*(_current_select(true) + columns))
955 end

Set both the select and group clauses with the given columns. Column aliases may be supplied, and will be included in the select clause. This also takes a virtual row block similar to where.

DB[:items].select_group(:a, :b)
# SELECT a, b FROM items GROUP BY a, b

DB[:items].select_group(Sequel[:c].as(:a)){f(c2)}
# SELECT c AS a, f(c2) FROM items GROUP BY c, f(c2)

    # File lib/sequel/dataset/query.rb
966 def select_group(*columns, &block)
967   virtual_row_columns(columns, block)
968   select(*columns).group(*columns.map{|c| unaliased_identifier(c)})
969 end

Alias for select_append.

    # File lib/sequel/dataset/query.rb
972 def select_more(*columns, &block)
973   select_append(*columns, &block)
974 end

Returns a copy of the dataset with the given columns added to the existing selected columns. If no columns are currently selected, it will select the columns given in addition to *.

DB[:items].select(:a).select(:b) # SELECT b FROM items
DB[:items].select(:a).select_prepend(:b) # SELECT b, a FROM items
DB[:items].select_prepend(:b) # SELECT b, * FROM items

    # File lib/sequel/dataset/query.rb
983 def select_prepend(*columns, &block)
984   virtual_row_columns(columns, block)
985   select(*(columns + _current_select(false)))
986 end

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 (where SELECT uses :read_only database and all other queries use the :default database). This method is always available but is only useful when database sharding is being used.

DB[:items].all # Uses the :read_only or :default server
DB[:items].delete # Uses the :default server
DB[:items].server(:blah).delete # Uses the :blah server

    # File lib/sequel/dataset/query.rb
997 def server(servr)
998   clone(:server=>servr)
999 end

If the database uses sharding and the current dataset has not had a server set, return a cloned dataset that uses the given server. Otherwise, return the receiver directly instead of returning a clone.

     # File lib/sequel/dataset/query.rb
1004 def server?(server)
1005   if db.sharded? && !opts[:server]
1006     server(server)
1007   else
1008     self
1009   end
1010 end

Specify that the check for limits/offsets when updating/deleting be skipped for the dataset.

     # File lib/sequel/dataset/query.rb
1013 def skip_limit_check
1014   return self if opts[:skip_limit_check]
1015   cached_dataset(:_skip_limit_check_ds) do
1016     clone(:skip_limit_check=>true)
1017   end
1018 end

Skip locked rows when returning results from this dataset.

     # File lib/sequel/dataset/query.rb
1021 def skip_locked
1022   return self if opts[:skip_locked]
1023   cached_dataset(:_skip_locked_ds) do
1024     raise(Error, 'This dataset does not support skipping locked rows') unless supports_skip_locked?
1025     clone(:skip_locked=>true)
1026   end
1027 end

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

DB[:items].group(:a).having(a: 1).where(:b).unfiltered
# SELECT * FROM items GROUP BY a

     # File lib/sequel/dataset/query.rb
1033 def unfiltered
1034   return self unless opts[:where] || opts[:having]
1035   cached_dataset(:_unfiltered_ds){clone(:where => nil, :having => nil)}
1036 end

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

DB[:items].group(:a).having(a: 1).where(:b).ungrouped
# SELECT * FROM items WHERE b

     # File lib/sequel/dataset/query.rb
1042 def ungrouped
1043   return self unless opts[:group] || opts[:having]
1044   cached_dataset(:_ungrouped_ds){clone(:group => nil, :having => nil)}
1045 end

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:

:alias

Use the given value as the from_self alias

: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[:other_items])
# SELECT * FROM (SELECT * FROM items UNION SELECT * FROM other_items) AS t1

DB[:items].union(DB[:other_items], all: true, from_self: false)
# SELECT * FROM items UNION ALL SELECT * FROM other_items

DB[:items].union(DB[:other_items], alias: :i)
# SELECT * FROM (SELECT * FROM items UNION SELECT * FROM other_items) AS i

     # File lib/sequel/dataset/query.rb
1063 def union(dataset, opts=OPTS)
1064   compound_clone(:union, dataset, opts)
1065 end

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

DB[:items].limit(10, 20).unlimited # SELECT * FROM items

     # File lib/sequel/dataset/query.rb
1070 def unlimited
1071   return self unless opts[:limit] || opts[:offset]
1072   cached_dataset(:_unlimited_ds){clone(:limit=>nil, :offset=>nil)}
1073 end

Returns a copy of the dataset with no order.

DB[:items].order(:a).unordered # SELECT * FROM items

     # File lib/sequel/dataset/query.rb
1078 def unordered
1079   return self unless opts[:order]
1080   cached_dataset(:_unordered_ds){clone(:order=>nil)}
1081 end

Returns a copy of the dataset with the given WHERE conditions imposed upon it.

Accepts the following argument types:

Hash, Array of pairs

list of equality/inclusion expressions

Symbol

taken as a boolean column argument (e.g. WHERE active)

Sequel::SQL::BooleanExpression, Sequel::LiteralString

an existing condition expression, probably created using the Sequel expression filter DSL.

where also accepts 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. For more details on the virtual row support, see the “Virtual Rows” guide

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

Examples:

DB[:items].where(id: 3)
# SELECT * FROM items WHERE (id = 3)

DB[:items].where(Sequel.lit('price < ?', 100))
# SELECT * FROM items WHERE price < 100

DB[:items].where([[:id, [1,2,3]], [:id, 0..10]])
# SELECT * FROM items WHERE ((id IN (1, 2, 3)) AND ((id >= 0) AND (id <= 10)))

DB[:items].where(Sequel.lit('price < 100'))
# SELECT * FROM items WHERE price < 100

DB[:items].where(:active)
# SELECT * FROM items WHERE :active

DB[:items].where{price < 100}
# SELECT * FROM items WHERE (price < 100)

Multiple where calls can be chained for scoping:

software = dataset.where(category: 'software').where{price < 100}
# SELECT * FROM items WHERE ((category = 'software') AND (price < 100))

See the “Dataset Filtering” guide for more examples and details.

     # File lib/sequel/dataset/query.rb
1125 def where(*cond, &block)
1126   add_filter(:where, cond, &block)
1127 end

Return a clone of the dataset with an addition named window that can be referenced in window functions. See Sequel::SQL::Window for a list of options that can be passed in. Example:

DB[:items].window(:w, partition: :c1, order: :c2)
# SELECT * FROM items WINDOW w AS (PARTITION BY c1 ORDER BY c2)

     # File lib/sequel/dataset/query.rb
1135 def window(name, opts)
1136   clone(:window=>((@opts[:window]||EMPTY_ARRAY) + [[name, SQL::Window.new(opts)].freeze]).freeze)
1137 end

Add a 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

:materialized

Set to false to force inlining of the CTE, or true to force not inlining the CTE (PostgreSQL 12+/SQLite 3.35+).

DB[:items].with(:items, DB[:syx].where(Sequel[:name].like('A%')))
# WITH items AS (SELECT * FROM syx WHERE (name LIKE 'A%' ESCAPE '\')) SELECT * FROM items

     # File lib/sequel/dataset/query.rb
1150 def with(name, dataset, opts=OPTS)
1151   raise(Error, 'This dataset does not support common table expressions') unless supports_cte?
1152   if hoist_cte?(dataset)
1153     s, ds = hoist_cte(dataset)
1154     s.with(name, ds, opts)
1155   else
1156     clone(:with=>((@opts[:with]||EMPTY_ARRAY) + [Hash[opts].merge!(:name=>name, :dataset=>dataset)]).freeze)
1157   end
1158 end

Create a subclass of the receiver's class, and include the given modules into it. If a block is provided, a DatasetModule is created using the block and is included into the subclass. Create an instance of the subclass using the same db and opts, so that the returned dataset operates similarly to a clone extended with the given modules. This approach is used to avoid singleton classes, which significantly improves performance.

Note that like Object#extend, when multiple modules are provided as arguments the subclass includes the modules in reverse order.

     # File lib/sequel/dataset/query.rb
1239 def with_extend(*mods, &block)
1240   c = Sequel.set_temp_name(Class.new(self.class)){"Sequel::Dataset::_Subclass"}
1241   c.include(*mods) unless mods.empty?
1242   c.include(Sequel.set_temp_name(DatasetModule.new(&block)){"Sequel::Dataset::_DatasetModule(#{block.source_location[0,2].join(':')})"}) if block
1243   o = c.freeze.allocate
1244   o.instance_variable_set(:@db, @db)
1245   o.instance_variable_set(:@opts, @opts)
1246   o.instance_variable_set(:@cache, {})
1247   if cols = cache_get(:_columns)
1248     o.send(:columns=, cols)
1249   end
1250   o.freeze
1251 end

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.

PostgreSQL 14+ Options:

:cycle

Stop recursive searching when a cycle is detected. Includes two columns in the result of the CTE, a cycle column indicating whether a cycle was detected for the current row, and a path column for the path traversed to get to the current row. If given, must be a hash with the following keys:

:columns

(required) The column or array of columns to use to detect a cycle. If the value of these columns match columns already traversed, then a cycle is detected, and recursive searching will not traverse beyond the cycle (the CTE will include the row where the cycle was detected).

:cycle_column

The name of the cycle column in the output, defaults to :is_cycle.

:cycle_value

The value of the cycle column in the output if the current row was detected as a cycle, defaults to true.

:noncycle_value

The value of the cycle column in the output if the current row was not detected as a cycle, defaults to false. Only respected if :cycle_value is given.

:path_column

The name of the path column in the output, defaults to :path.

:search

Include an order column in the result of the CTE that allows for breadth or depth first searching. If given, must be a hash with the following keys:

:by

(required) The column or array of columns to search by.

:order_column

The name of the order column in the output, defaults to :ordercol.

:type

Set to :breadth to use breadth-first searching (depth-first searching is the default).

DB[:t].with_recursive(:t,
  DB[:i1].select(:id, :parent_id).where(parent_id: nil),
  DB[:i1].join(:t, id: :parent_id).select(Sequel[:i1][:id], Sequel[:i1][:parent_id]),
  args: [:id, :parent_id])

# WITH RECURSIVE t(id, parent_id) AS (
#   SELECT id, parent_id FROM i1 WHERE (parent_id IS NULL)
#   UNION ALL
#   SELECT i1.id, i1.parent_id FROM i1 INNER JOIN t ON (t.id = i1.parent_id)
# ) SELECT * FROM t

DB[:t].with_recursive(:t,
  DB[:i1].where(parent_id: nil),
  DB[:i1].join(:t, id: :parent_id).select_all(:i1),
  search: {by: :id, type: :breadth},
  cycle: {columns: :id, cycle_value: 1, noncycle_value: 2})

# WITH RECURSIVE t AS (
#     SELECT * FROM i1 WHERE (parent_id IS NULL)
#     UNION ALL
#     (SELECT i1.* FROM i1 INNER JOIN t ON (t.id = i1.parent_id))
#   )
#   SEARCH BREADTH FIRST BY id SET ordercol
#   CYCLE id SET is_cycle TO 1 DEFAULT 2 USING path
# SELECT * FROM t

     # File lib/sequel/dataset/query.rb
1216 def with_recursive(name, nonrecursive, recursive, opts=OPTS)
1217   raise(Error, 'This dataset does not support common table expressions') unless supports_cte?
1218   if hoist_cte?(nonrecursive)
1219     s, ds = hoist_cte(nonrecursive)
1220     s.with_recursive(name, ds, recursive, opts)
1221   elsif hoist_cte?(recursive)
1222     s, ds = hoist_cte(recursive)
1223     s.with_recursive(name, nonrecursive, ds, opts)
1224   else
1225     clone(:with=>((@opts[:with]||EMPTY_ARRAY) + [Hash[opts].merge!(:recursive=>true, :name=>name, :dataset=>nonrecursive.union(recursive, {:all=>opts[:union_all] != false, :from_self=>false}))]).freeze)
1226   end
1227 end

Returns a cloned dataset with the given row_proc.

ds = DB[:items]
ds.all # => [{:id=>2}]
ds.with_row_proc(:invert.to_proc).all # => [{2=>:id}]

     # File lib/sequel/dataset/query.rb
1268 def with_row_proc(callable)
1269   clone(:row_proc=>callable)
1270 end

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.

DB[:items].with_sql('SELECT * FROM foo') # SELECT * FROM foo

You can use placeholders in your SQL and provide arguments for those placeholders:

DB[:items].with_sql('SELECT ? FROM foo', 1) # SELECT 1 FROM foo

You can also provide a method name and arguments to call to get the SQL:

DB[:items].with_sql(:insert_sql, b: 1) # INSERT INTO items (b) VALUES (1)

Note that datasets that specify custom SQL using this method will generally ignore future dataset methods that modify the SQL used, as specifying custom SQL overrides Sequel's SQL generator. You should probably limit yourself to the following dataset methods when using this method, or use the implicit_subquery extension:

• each

• all

• single_record (if only one record could be returned)

• single_value (if only one record could be returned, and a single column is selected)

• map

• as_hash

• to_hash

• to_hash_groups

• delete (if a DELETE statement)

• update (if an UPDATE statement, with no arguments)

• insert (if an INSERT statement, with no arguments)

• truncate (if a TRUNCATE statement, with no arguments)

     # File lib/sequel/dataset/query.rb
1302 def with_sql(sql, *args)
1303   if sql.is_a?(Symbol)
1304     sql = public_send(sql, *args).freeze
1305   else
1306     sql = SQL::PlaceholderLiteralString.new(sql, args) unless args.empty?
1307   end
1308   clone(:sql=>sql)
1309 end

Add the dataset to the list of compounds

     # File lib/sequel/dataset/query.rb
1314 def compound_clone(type, dataset, opts)
1315   if dataset.is_a?(Dataset) && dataset.opts[:with] && !supports_cte_in_compounds?
1316     s, ds = hoist_cte(dataset)
1317     return s.compound_clone(type, ds, opts)
1318   end
1319   ds = compound_from_self.clone(:compounds=>(Array(@opts[:compounds]).map(&:dup) + [[type, dataset.compound_from_self, opts[:all]].freeze]).freeze)
1320   opts[:from_self] == false ? ds : ds.from_self(opts)
1321 end

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

     # File lib/sequel/dataset/query.rb
1324 def options_overlap(opts)
1325   !(@opts.map{|k,v| k unless v.nil?}.compact & opts).empty?
1326 end

Whether this dataset is a simple select from an underlying table, such as:

SELECT * FROM table
SELECT table.* FROM table

     # File lib/sequel/dataset/query.rb
1335 def simple_select_all?
1336   return false unless (f = @opts[:from]) && f.length == 1
1337   o = @opts.reject{|k,v| v.nil? || non_sql_option?(k)}
1338   from = f.first
1339   from = from.expression if from.is_a?(SQL::AliasedExpression)
1340 
1341   if SIMPLE_SELECT_ALL_ALLOWED_FROM.any?{|x| from.is_a?(x)}
1342     case o.length
1343     when 1
1344       true
1345     when 2
1346       (s = o[:select]) && s.length == 1 && s.first.is_a?(SQL::ColumnAll)
1347     else
1348       false
1349     end
1350   else
1351     false
1352   end
1353 end

A frozen array for the currently selected columns.

     # File lib/sequel/dataset/query.rb
1379 def _current_select(allow_plain_wildcard)
1380   cur_sel = @opts[:select]
1381 
1382   if !cur_sel || cur_sel.empty?
1383     cur_sel = if allow_plain_wildcard && supports_select_all_and_column?
1384       [WILDCARD].freeze
1385     else
1386       _current_select_column_all
1387     end
1388   elsif !allow_plain_wildcard && cur_sel.include?(WILDCARD)
1389     cur_sel = cur_sel.dup
1390     index = cur_sel.index(WILDCARD)
1391     cur_sel.delete(WILDCARD)
1392     _current_select_column_all.each_with_index do |ca, i|
1393       cur_sel.insert(index+i, ca)
1394     end
1395     cur_sel.freeze
1396   end
1397 
1398   cur_sel
1399 end

An array of SQL::ColumnAll objects for all FROM and JOIN tables. Used for select_append and select_prepend.

     # File lib/sequel/dataset/query.rb
1403 def _current_select_column_all
1404   tables = Array(@opts[:from]) + Array(@opts[:join])
1405   tables.map{|t| i, a = split_alias(t); a || i}.map!{|t| SQL::ColumnAll.new(t)}.freeze
1406 end

Load the extensions into the receiver, without checking if the receiver is frozen.

     # File lib/sequel/dataset/query.rb
1360 def _extension!(exts)
1361   exts.each do |ext|
1362     unless pr = Sequel.synchronize{EXTENSIONS[ext]}
1363       Sequel.extension(ext)
1364       pr = Sequel.synchronize{EXTENSIONS[ext]}
1365     end
1366 
1367     if pr
1368       pr.call(self)
1369     else
1370       raise(Error, "Extension #{ext} does not have specific support handling individual datasets (try: Sequel.extension #{ext.inspect})")
1371     end
1372   end
1373   self
1374 end

If invert is true, invert the condition.

     # File lib/sequel/dataset/query.rb
1409 def _invert_filter(cond, invert)
1410   if invert
1411     SQL::BooleanExpression.invert(cond)
1412   else
1413     cond
1414   end
1415 end

Append to the current MERGE WHEN clauses. Mutates the hash to add the conditions, if a virtual row block is passed.

     # File lib/sequel/dataset/query.rb
1419 def _merge_when(hash, &block)
1420   hash[:conditions] = Sequel.virtual_row(&block) if block
1421 
1422   if merge_when = @opts[:merge_when]
1423     clone(:merge_when => (merge_when.dup << hash.freeze).freeze)
1424   else
1425     clone(:merge_when => [hash.freeze].freeze)
1426   end
1427 end

Add the given filter condition. Arguments:

clause

Symbol or which SQL clause to effect, should be :where or :having

cond

The filter condition to add

invert

Whether the condition should be inverted (true or false)

combine

How to combine the condition with an existing condition, should be :AND or :OR

     # File lib/sequel/dataset/query.rb
1434 def add_filter(clause, cond, invert=false, combine=:AND, &block)
1435   if cond == EMPTY_ARRAY && !block
1436     raise Error, "must provide an argument to a filtering method if not passing a block"
1437   end
1438   
1439   cond = cond.first if cond.size == 1
1440 
1441   empty = cond == OPTS || cond == EMPTY_ARRAY
1442 
1443   if empty && !block
1444     self 
1445   else
1446     if cond == nil
1447       cond = Sequel::NULL
1448     end
1449     if empty && block
1450       cond = nil
1451     end
1452 
1453     cond = _invert_filter(filter_expr(cond, &block), invert)
1454     cond = SQL::BooleanExpression.new(combine, @opts[clause], cond) if @opts[clause]
1455 
1456     if cond.nil?
1457       cond = Sequel::NULL
1458     end
1459 
1460     clone(clause => cond)
1461   end
1462 end

Internals of for_update and adapter-specific lock methods. Returns receiver if it already uses this lock style, and a cached dataset using the given key otherwise. The key could be derived from the style, but doing so would require allocation, so pass it in as an argument.

     # File lib/sequel/dataset/query.rb
1469 def cached_lock_style_dataset(key, style)
1470   opts[:lock] == style ? self : cached_dataset(key){lock_style(style)}
1471 end

The default :qualify option to use for join tables if one is not specified.

     # File lib/sequel/dataset/query.rb
1474 def default_join_table_qualification
1475   :symbol
1476 end

Return self if the dataset already has a server, or a cloned dataset with the default server otherwise.

     # File lib/sequel/dataset/query.rb
1552 def default_server
1553   server?(:default)
1554 end

SQL expression object based on the expr type. See where.

     # File lib/sequel/dataset/query.rb
1482 def filter_expr(expr = nil, &block)
1483   expr = nil if expr == EMPTY_ARRAY
1484 
1485   if block
1486     cond = filter_expr(Sequel.virtual_row(&block))
1487     cond = SQL::BooleanExpression.new(:AND, filter_expr(expr), cond) if expr
1488     return cond
1489   end
1490 
1491   case expr
1492   when Hash
1493     SQL::BooleanExpression.from_value_pairs(expr)
1494   when Array
1495     if Sequel.condition_specifier?(expr)
1496       SQL::BooleanExpression.from_value_pairs(expr)
1497     else
1498       raise Error, "Invalid filter expression: #{expr.inspect}"
1499     end
1500   when LiteralString
1501     SQL::PlaceholderLiteralString.new(PAREN_WRAPPER, [expr])
1502   when Numeric, SQL::NumericExpression, SQL::StringExpression, Proc, String, Set
1503     raise Error, "Invalid filter expression: #{expr.inspect}"
1504   when TrueClass, FalseClass
1505     if supports_where_true?
1506       SQL::BooleanExpression.new(:NOOP, expr)
1507     elsif expr
1508       SQL::Constants::SQLTRUE
1509     else
1510       SQL::Constants::SQLFALSE
1511     end
1512   when PlaceholderLiteralizer::Argument
1513     expr.transform{|v| filter_expr(v)}
1514   when SQL::PlaceholderLiteralString
1515     expr.with_parens
1516   else
1517     expr
1518   end
1519 end

Return two datasets, the first a clone of the receiver with the WITH clause from the given dataset added to it, and the second a clone of the given dataset with the WITH clause removed.

     # File lib/sequel/dataset/query.rb
1524 def hoist_cte(ds)
1525   [clone(:with => ((opts[:with] || EMPTY_ARRAY) + ds.opts[:with]).freeze), ds.clone(:with => nil)]
1526 end

Whether CTEs need to be hoisted from the given ds into the current ds.

     # File lib/sequel/dataset/query.rb
1529 def hoist_cte?(ds)
1530   ds.is_a?(Dataset) && ds.opts[:with] && !supports_cte_in_subqueries?
1531 end

Inverts the given order by breaking it into a list of column references and inverting them.

DB[:items].invert_order([Sequel.desc(:id)]]) #=> [Sequel.asc(:id)]
DB[:items].invert_order([:category, Sequel.desc(:price)]) #=> [Sequel.desc(:category), Sequel.asc(:price)]

     # File lib/sequel/dataset/query.rb
1538 def invert_order(order)
1539   return unless order
1540   order.map do |f|
1541     case f
1542     when SQL::OrderedExpression
1543       f.invert
1544     else
1545       SQL::OrderedExpression.new(f)
1546     end
1547   end
1548 end

Whether the given option key does not affect the generated SQL.

     # File lib/sequel/dataset/query.rb
1557 def non_sql_option?(key)
1558   NON_SQL_OPTIONS.include?(key)
1559 end

Treat the block as a virtual_row block if not nil and add the resulting columns to the columns array (modifies columns).

     # File lib/sequel/dataset/query.rb
1563 def virtual_row_columns(columns, block)
1564   if block
1565     v = Sequel.virtual_row(&block)
1566     if v.is_a?(Array)
1567       columns.concat(v)
1568     else
1569       columns << v
1570     end
1571   end
1572 end

Inserts the given argument into the database. Returns self so it can be used safely when chaining:

DB[:items] << {id: 0, name: 'Zero'} << DB[:old_items].select(:id, name)

   # File lib/sequel/dataset/actions.rb
29 def <<(arg)
30   insert(arg)
31   self
32 end

Returns the first record matching the conditions. Examples:

DB[:table][id: 1] # SELECT * FROM table WHERE (id = 1) LIMIT 1
# => {:id=>1}

   # File lib/sequel/dataset/actions.rb
38 def [](*conditions)
39   raise(Error, 'You cannot call Dataset#[] with an integer or with no arguments') if (conditions.length == 1 and conditions.first.is_a?(Integer)) or conditions.length == 0
40   first(*conditions)
41 end

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.

DB[:table].all # SELECT * FROM table
# => [{:id=>1, ...}, {:id=>2, ...}, ...]

# Iterate over all rows in the table
DB[:table].all{|row| p row}

   # File lib/sequel/dataset/actions.rb
51 def all(&block)
52   _all(block){|a| each{|r| a << r}}
53 end

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.

DB[:table].as_hash(:id, :name) # SELECT * FROM table
# {1=>'Jim', 2=>'Bob', ...}

DB[:table].as_hash(:id) # SELECT * FROM table
# {1=>{:id=>1, :name=>'Jim'}, 2=>{:id=>2, :name=>'Bob'}, ...}

You can also provide an array of column names for either the key_column, the value column, or both:

DB[:table].as_hash([:id, :foo], [:name, :bar]) # SELECT * FROM table
# {[1, 3]=>['Jim', 'bo'], [2, 4]=>['Bob', 'be'], ...}

DB[:table].as_hash([:id, :name]) # SELECT * FROM table
# {[1, 'Jim']=>{:id=>1, :name=>'Jim'}, [2, 'Bob']=>{:id=>2, :name=>'Bob'}, ...}

Options:

:all

Use all instead of each to retrieve the objects

:hash

The object into which the values will be placed. If this is not given, an empty hash is used. This can be used to use a hash with a default value or default proc.

    # File lib/sequel/dataset/actions.rb
910 def as_hash(key_column, value_column = nil, opts = OPTS)
911   h = opts[:hash] || {}
912   meth = opts[:all] ? :all : :each
913   if value_column
914     return naked.as_hash(key_column, value_column, opts) if row_proc
915     if value_column.is_a?(Array)
916       if key_column.is_a?(Array)
917         public_send(meth){|r| h[r.values_at(*key_column)] = r.values_at(*value_column)}
918       else
919         public_send(meth){|r| h[r[key_column]] = r.values_at(*value_column)}
920       end
921     else
922       if key_column.is_a?(Array)
923         public_send(meth){|r| h[r.values_at(*key_column)] = r[value_column]}
924       else
925         public_send(meth){|r| h[r[key_column]] = r[value_column]}
926       end
927     end
928   elsif key_column.is_a?(Array)
929     public_send(meth){|r| h[key_column.map{|k| r[k]}] = r}
930   else
931     public_send(meth){|r| h[r[key_column]] = r}
932   end
933   h
934 end

Returns sets for column values for each record in the dataset.

DB[:table].as_set(:id) # SELECT * FROM table
# => Set[1, 2, 3, ...]

You can also provide an array of column names, in which case the elements of the returned set are arrays (not sets):

DB[:table].as_set([:id, :name]) # SELECT * FROM table
# => Set[[1, 'A'], [2, 'B'], [3, 'C'], ...]

   # File lib/sequel/dataset/actions.rb
65 def as_set(column)
66   return naked.as_set(column) if row_proc
67 
68   if column.is_a?(Array)
69     to_set{|r| r.values_at(*column)}
70   else
71     to_set{|r| r[column]}
72   end
73 end

Returns the average value for the given column/expression. Uses a virtual row block if no argument is given.

DB[:table].avg(:number) # SELECT avg(number) FROM table LIMIT 1
# => 3
DB[:table].avg{function(column)} # SELECT avg(function(column)) FROM table LIMIT 1
# => 1

   # File lib/sequel/dataset/actions.rb
82 def avg(arg=(no_arg = true), &block)
83   arg = Sequel.virtual_row(&block) if no_arg
84   _aggregate(:avg, arg)
85 end

Returns the columns in the result set in order as an array of symbols. If the columns are currently cached, returns the cached value. Otherwise, a SELECT query is performed to retrieve a single row in order to get the columns.

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

DB[:table].columns
# => [:id, :name]

   # File lib/sequel/dataset/actions.rb
96 def columns
97   _columns || columns!
98 end

Ignore any cached column information and perform a query to retrieve a row in order to get the columns.

DB[:table].columns!
# => [:id, :name]

    # File lib/sequel/dataset/actions.rb
105 def columns!
106   ds = clone(COLUMNS_CLONE_OPTIONS)
107   ds.each{break}
108 
109   if cols = ds.cache[:_columns]
110     self.columns = cols
111   else
112     []
113   end
114 end

Returns the number of records in the dataset. If an argument is provided, it is used as the argument to count. If a block is provided, it is treated as a virtual row, and the result is used as the argument to count.

DB[:table].count # SELECT count(*) AS count FROM table LIMIT 1
# => 3
DB[:table].count(:column) # SELECT count(column) AS count FROM table LIMIT 1
# => 2
DB[:table].count{foo(column)} # SELECT count(foo(column)) AS count FROM table LIMIT 1
# => 1

    # File lib/sequel/dataset/actions.rb
129 def count(arg=(no_arg=true), &block)
130   if no_arg && !block
131     cached_dataset(:_count_ds) do
132       aggregate_dataset.select(COUNT_SELECT).single_value_ds
133     end.single_value!.to_i
134   else
135     if block
136       if no_arg
137         arg = Sequel.virtual_row(&block)
138       else
139         raise Error, 'cannot provide both argument and block to Dataset#count'
140       end
141     end
142 
143     _aggregate(:count, arg)
144   end
145 end

Deletes the records in the dataset, returning the number of records deleted.

DB[:table].delete # DELETE * FROM table
# => 3

Some databases support using multiple tables in a DELETE query. This requires multiple FROM tables (JOINs can also be used). As multiple FROM tables use an implicit CROSS JOIN, you should make sure your WHERE condition uses the appropriate filters for the FROM tables:

DB.from(:a, :b).join(:c, :d=>Sequel[:b][:e]).where{{a[:f]=>b[:g], a[:id]=>c[:h]}}.
  delete
# DELETE FROM a
# USING b
# INNER JOIN c ON (c.d = b.e)
# WHERE ((a.f = b.g) AND (a.id = c.h))

    # File lib/sequel/dataset/actions.rb
163 def delete(&block)
164   sql = delete_sql
165   if uses_returning?(:delete)
166     returning_fetch_rows(sql, &block)
167   else
168     execute_dui(sql)
169   end
170 end

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

DB[:table].each{|row| p row} # SELECT * FROM table

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 should use all instead of each for the outer queries, or use a separate thread or shard inside each.

    # File lib/sequel/dataset/actions.rb
181 def each
182   if rp = row_proc
183     fetch_rows(select_sql){|r| yield rp.call(r)}
184   else
185     fetch_rows(select_sql){|r| yield r}
186   end
187   self
188 end

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

DB[:table].empty? # SELECT 1 AS one FROM table LIMIT 1
# => false

    # File lib/sequel/dataset/actions.rb
196 def empty?
197   cached_dataset(:_empty_ds) do
198     (@opts[:sql] ? from_self : self).single_value_ds.unordered.select(EMPTY_SELECT)
199   end.single_value!.nil?
200 end

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

If there are no records in the dataset, returns nil (or an empty array if an integer argument is given).

Examples:

DB[:table].first # SELECT * FROM table LIMIT 1
# => {:id=>7}

DB[:table].first(2) # SELECT * FROM table LIMIT 2
# => [{:id=>6}, {:id=>4}]

DB[:table].first(id: 2) # SELECT * FROM table WHERE (id = 2) LIMIT 1
# => {:id=>2}

DB[:table].first(Sequel.lit("id = 3")) # SELECT * FROM table WHERE (id = 3) LIMIT 1
# => {:id=>3}

DB[:table].first(Sequel.lit("id = ?", 4)) # SELECT * FROM table WHERE (id = 4) LIMIT 1
# => {:id=>4}

DB[:table].first{id > 2} # SELECT * FROM table WHERE (id > 2) LIMIT 1
# => {:id=>5}

DB[:table].first(Sequel.lit("id > ?", 4)){id < 6} # SELECT * FROM table WHERE ((id > 4) AND (id < 6)) LIMIT 1
# => {:id=>5}

DB[:table].first(2){id < 2} # SELECT * FROM table WHERE (id < 2) LIMIT 2
# => [{:id=>1}]

    # File lib/sequel/dataset/actions.rb
237 def first(*args, &block)
238   case args.length
239   when 0
240     unless block
241       return(@opts[:sql] ? single_record! : single_record)
242     end
243   when 1
244     arg = args[0]
245     if arg.is_a?(Integer)
246       res = if block
247         if loader = cached_placeholder_literalizer(:_first_integer_cond_loader) do |pl|
248             where(pl.arg).limit(pl.arg)
249           end
250 
251           loader.all(filter_expr(&block), arg)
252         else
253           where(&block).limit(arg).all
254         end
255       else
256         if loader = cached_placeholder_literalizer(:_first_integer_loader) do |pl|
257            limit(pl.arg)
258           end
259 
260           loader.all(arg)
261         else
262           limit(arg).all
263         end
264       end
265 
266       return res
267     end
268     where_args = args
269     args = arg
270   end
271 
272   if loader = cached_where_placeholder_literalizer(where_args||args, block, :_first_cond_loader) do |pl|
273       _single_record_ds.where(pl.arg)
274     end
275 
276     loader.first(filter_expr(args, &block))
277   else
278     _single_record_ds.where(args, &block).single_record!
279   end
280 end

Calls first. If first returns nil (signaling that no row matches), raise a Sequel::NoMatchingRow exception.

    # File lib/sequel/dataset/actions.rb
284 def first!(*args, &block)
285   first(*args, &block) || raise(Sequel::NoMatchingRow.new(self))
286 end

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

DB[:table].get(:id) # SELECT id FROM table LIMIT 1
# => 3

ds.get{sum(id)} # SELECT sum(id) AS v FROM table LIMIT 1
# => 6

You can pass an array of arguments to return multiple arguments, but you must make sure each element in the array has an alias that Sequel can determine:

DB[:table].get([:id, :name]) # SELECT id, name FROM table LIMIT 1
# => [3, 'foo']

DB[:table].get{[sum(id).as(sum), name]} # SELECT sum(id) AS sum, name FROM table LIMIT 1
# => [6, 'foo']

If called on a dataset with raw SQL, returns the first value in the dataset without changing the selection or setting a limit:

DB["SELECT id FROM table"].get # SELECT id FROM table
# =>  3

    # File lib/sequel/dataset/actions.rb
312 def get(column=(no_arg=true; nil), &block)
313   ds = naked
314   if block
315     raise(Error, 'Must call Dataset#get with an argument or a block, not both') unless no_arg
316     ds = ds.select(&block)
317     column = ds.opts[:select]
318     column = nil if column.is_a?(Array) && column.length < 2
319   elsif no_arg && opts[:sql]
320     return ds.single_value!
321   else
322     case column
323     when Array
324       ds = ds.select(*column)
325     when LiteralString, Symbol, SQL::Identifier, SQL::QualifiedIdentifier, SQL::AliasedExpression
326       if loader = cached_placeholder_literalizer(:_get_loader) do |pl|
327           ds.single_value_ds.select(pl.arg)
328         end
329 
330         return loader.get(column)
331       end
332 
333       ds = ds.select(column)
334     else
335       if loader = cached_placeholder_literalizer(:_get_alias_loader) do |pl|
336           ds.single_value_ds.select(Sequel.as(pl.arg, :v))
337         end
338 
339         return loader.get(column)
340       end
341 
342       ds = ds.select(Sequel.as(column, :v))
343     end
344   end
345 
346   if column.is_a?(Array)
347    if r = ds.single_record
348      r.values_at(*hash_key_symbols(column))
349    end
350   else
351     ds.single_value
352   end
353 end

Inserts multiple records into the associated table. This method can be used to efficiently insert a large number of records into a table in a single query if the database supports it. Inserts are automatically wrapped in a transaction if necessary.

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

DB[:table].import([:x, :y], [[1, 2], [3, 4]])
# INSERT INTO table (x, y) VALUES (1, 2)
# INSERT INTO table (x, y) VALUES (3, 4)

or, if the database supports it:

# INSERT INTO table (x, y) VALUES (1, 2), (3, 4)

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

DB[:table].import([:x, :y], DB[:table2].select(:a, :b))
# INSERT INTO table (x, y) SELECT a, b FROM table2

The return value of this method is undefined and should not be used, except in two cases:

• When the return: :primary_key option is used.

• On PostgreSQL, when the dataset uses RETURNING. In this case, if a single value is returned per row, the return value is an array of those values. If multiple values are returned per row, the return value is an array of hashes.

Options:

:commit_every

Open a new transaction for every given number of records. For example, if you provide a value of 50, will commit after every 50 records. When a transaction is not required, this option controls the maximum number of values to insert with a single statement; it does not force the use of a transaction.

:return

When this is set to :primary_key, returns an array of autoincremented primary key values for the rows inserted. This does not have an effect if values is a Dataset.

:server

Set the server/shard to use for the transaction and insert queries.

:skip_transaction

Do not use a transaction even when using multiple INSERT queries.

:slice

Same as :commit_every, :commit_every takes precedence.

    # File lib/sequel/dataset/actions.rb
400 def import(columns, values, opts=OPTS)
401   return insert(columns, values) if values.is_a?(Dataset)
402 
403   return if values.empty?
404   raise(Error, 'Using Sequel::Dataset#import with an empty column array is not allowed') if columns.empty?
405   ds = opts[:server] ? server(opts[:server]) : self
406   
407   if slice_size = opts.fetch(:commit_every, opts.fetch(:slice, default_import_slice))
408     offset = 0
409     rows = []
410     while offset < values.length
411       rows << ds._import(columns, values[offset, slice_size], opts)
412       offset += slice_size
413     end
414     rows.flatten
415   else
416     ds._import(columns, values, opts)
417   end
418 end

Inserts values into the associated table. The returned value is generally the value of the autoincremented primary key for the inserted row, assuming that a single row is inserted and the table has an autoincrementing primary key.

insert handles a number of different argument formats:

no arguments or single empty hash

Uses DEFAULT VALUES

single hash

Most common format, treats keys as columns and values as values

single array

Treats entries as values, with no columns

two arrays

Treats first array as columns, second array as values

single Dataset

Treats as an insert based on a selection from the dataset given, with no columns

array and dataset

Treats as an insert based on a selection from the dataset given, with the columns given by the array.

Examples:

DB[:items].insert
# INSERT INTO items DEFAULT VALUES

DB[:items].insert({})
# INSERT INTO items DEFAULT VALUES

DB[:items].insert([1,2,3])
# INSERT INTO items VALUES (1, 2, 3)

DB[:items].insert([:a, :b], [1,2])
# INSERT INTO items (a, b) VALUES (1, 2)

DB[:items].insert(a: 1, b: 2)
# INSERT INTO items (a, b) VALUES (1, 2)

DB[:items].insert(DB[:old_items])
# INSERT INTO items SELECT * FROM old_items

DB[:items].insert([:a, :b], DB[:old_items])
# INSERT INTO items (a, b) SELECT * FROM old_items

    # File lib/sequel/dataset/actions.rb
456 def insert(*values, &block)
457   sql = insert_sql(*values)
458   if uses_returning?(:insert)
459     returning_fetch_rows(sql, &block)
460   else
461     execute_insert(sql)
462   end
463 end

Reverses the order and then runs first with the given arguments and block. 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.

DB[:table].order(:id).last # SELECT * FROM table ORDER BY id DESC LIMIT 1
# => {:id=>10}

DB[:table].order(Sequel.desc(:id)).last(2) # SELECT * FROM table ORDER BY id ASC LIMIT 2
# => [{:id=>1}, {:id=>2}]

    # File lib/sequel/dataset/actions.rb
475 def last(*args, &block)
476   raise(Error, 'No order specified') unless @opts[:order]
477   reverse.first(*args, &block)
478 end

Maps column values for each record in the dataset (if an argument is given) or performs the stock mapping functionality of Enumerable otherwise. Raises an Error if both an argument and block are given.

DB[:table].map(:id) # SELECT * FROM table
# => [1, 2, 3, ...]

DB[:table].map{|r| r[:id] * 2} # SELECT * FROM table
# => [2, 4, 6, ...]

You can also provide an array of column names:

DB[:table].map([:id, :name]) # SELECT * FROM table
# => [[1, 'A'], [2, 'B'], [3, 'C'], ...]

    # File lib/sequel/dataset/actions.rb
494 def map(column=nil, &block)
495   if column
496     raise(Error, 'Must call Dataset#map with either an argument or a block, not both') if block
497     return naked.map(column) if row_proc
498     if column.is_a?(Array)
499       super(){|r| r.values_at(*column)}
500     else
501       super(){|r| r[column]}
502     end
503   else
504     super(&block)
505   end
506 end

Returns the maximum value for the given column/expression. Uses a virtual row block if no argument is given.

DB[:table].max(:id) # SELECT max(id) FROM table LIMIT 1
# => 10
DB[:table].max{function(column)} # SELECT max(function(column)) FROM table LIMIT 1
# => 7

    # File lib/sequel/dataset/actions.rb
515 def max(arg=(no_arg = true), &block)
516   arg = Sequel.virtual_row(&block) if no_arg
517   _aggregate(:max, arg)
518 end

Execute a MERGE statement, which allows for INSERT, UPDATE, and DELETE behavior in a single query, based on whether rows from a source table match rows in the current table, based on the join conditions.

Unless the dataset uses static SQL, to use merge, you must first have called merge_using to specify the merge source and join conditions. You will then likely to call one or more of the following methods to specify MERGE behavior by adding WHEN [NOT] MATCHED clauses:

• merge_insert

• merge_update

• merge_delete

The WHEN [NOT] MATCHED clauses are added to the SQL in the order these methods were called on the dataset. If none of these methods are called, an error is raised.

Example:

DB[:m1]
  merge_using(:m2, i1: :i2).
  merge_insert(i1: :i2, a: Sequel[:b]+11).
  merge_delete{a > 30}.
  merge_update(i1: Sequel[:i1]+:i2+10, a: Sequel[:a]+:b+20).
  merge

SQL:

MERGE INTO m1 USING m2 ON (i1 = i2)
WHEN NOT MATCHED THEN INSERT (i1, a) VALUES (i2, (b + 11))
WHEN MATCHED AND (a > 30) THEN DELETE
WHEN MATCHED THEN UPDATE SET i1 = (i1 + i2 + 10), a = (a + b + 20)

On PostgreSQL, two additional merge methods are supported, for the PostgreSQL-specific DO NOTHING syntax.

• merge_do_nothing_when_matched

• merge_do_nothing_when_not_matched

This method is supported on Oracle, but Oracle's MERGE support is non-standard, and has the following issues:

• DELETE clause requires UPDATE clause

• DELETE clause requires a condition

• DELETE clause only affects rows updated by UPDATE clause

    # File lib/sequel/dataset/actions.rb
565 def merge
566   execute_ddl(merge_sql)
567 end

Returns the minimum value for the given column/expression. Uses a virtual row block if no argument is given.

DB[:table].min(:id) # SELECT min(id) FROM table LIMIT 1
# => 1
DB[:table].min{function(column)} # SELECT min(function(column)) FROM table LIMIT 1
# => 0

    # File lib/sequel/dataset/actions.rb
576 def min(arg=(no_arg = true), &block)
577   arg = Sequel.virtual_row(&block) if no_arg
578   _aggregate(:min, arg)
579 end

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

DB[:table].multi_insert([{x: 1}, {x: 2}])
# INSERT INTO table (x) VALUES (1)
# INSERT INTO table (x) VALUES (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.

This respects the same options as import.

    # File lib/sequel/dataset/actions.rb
593 def multi_insert(hashes, opts=OPTS)
594   return if hashes.empty?
595   columns = hashes.first.keys
596   import(columns, hashes.map{|h| columns.map{|c| h[c]}}, opts)
597 end

Yields each row in the dataset, but internally uses multiple queries as needed to process the entire result set without keeping all rows in the dataset in memory, even if the underlying driver buffers all query results in memory.

Because this uses multiple queries internally, in order to remain consistent, it also uses a transaction internally. Additionally, to work correctly, the dataset must have unambiguous order. Using an ambiguous order can result in an infinite loop, as well as subtler bugs such as yielding duplicate rows or rows being skipped.

Sequel checks that the datasets using this method have an order, but it cannot ensure that the order is unambiguous.

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, use a separate thread or shard inside paged_each.

Options:

:rows_per_fetch

The number of rows to fetch per query. Defaults to 1000.

:strategy

The strategy to use for paging of results. By default this is :offset, for using an approach with a limit and offset for every page. This can be set to :filter, which uses a limit and a filter that excludes rows from previous pages. In order for this strategy to work, you must be selecting the columns you are ordering by, and none of the columns can contain NULLs. Note that some Sequel adapters have optimized implementations that will use cursors or streaming regardless of the :strategy option used.

:filter_values

If the strategy: :filter option is used, this option should be a proc that accepts the last retrieved row for the previous page and an array of ORDER BY expressions, and returns an array of values relating to those expressions for the last retrieved row. You will need to use this option if your ORDER BY expressions are not simple columns, if they contain qualified identifiers that would be ambiguous unqualified, if they contain any identifiers that are aliased in SELECT, and potentially other cases.

:skip_transaction

Do not use a transaction. This can be useful if you want to prevent a lock on the database table, at the expense of consistency.

Examples:

DB[:table].order(:id).paged_each{|row| }
# SELECT * FROM table ORDER BY id LIMIT 1000
# SELECT * FROM table ORDER BY id LIMIT 1000 OFFSET 1000
# ...

DB[:table].order(:id).paged_each(rows_per_fetch: 100){|row| }
# SELECT * FROM table ORDER BY id LIMIT 100
# SELECT * FROM table ORDER BY id LIMIT 100 OFFSET 100
# ...

DB[:table].order(:id).paged_each(strategy: :filter){|row| }
# SELECT * FROM table ORDER BY id LIMIT 1000
# SELECT * FROM table WHERE id > 1001 ORDER BY id LIMIT 1000
# ...

DB[:table].order(:id).paged_each(strategy: :filter,
  filter_values: lambda{|row, exprs| [row[:id]]}){|row| }
# SELECT * FROM table ORDER BY id LIMIT 1000
# SELECT * FROM table WHERE id > 1001 ORDER BY id LIMIT 1000
# ...

    # File lib/sequel/dataset/actions.rb
656 def paged_each(opts=OPTS)
657   unless @opts[:order]
658     raise Sequel::Error, "Dataset#paged_each requires the dataset be ordered"
659   end
660   unless defined?(yield)
661     return enum_for(:paged_each, opts)
662   end
663 
664   total_limit = @opts[:limit]
665   offset = @opts[:offset]
666   if server = @opts[:server]
667     opts = Hash[opts]
668     opts[:server] = server
669   end
670 
671   rows_per_fetch = opts[:rows_per_fetch] || 1000
672   strategy = if offset || total_limit
673     :offset
674   else
675     opts[:strategy] || :offset
676   end
677 
678   db.transaction(opts) do
679     case strategy
680     when :filter
681       filter_values = opts[:filter_values] || proc{|row, exprs| exprs.map{|e| row[hash_key_symbol(e)]}}
682       base_ds = ds = limit(rows_per_fetch)
683       while ds
684         last_row = nil
685         ds.each do |row|
686           last_row = row
687           yield row
688         end
689         ds = (base_ds.where(ignore_values_preceding(last_row, &filter_values)) if last_row)
690       end
691     else
692       offset ||= 0
693       num_rows_yielded = rows_per_fetch
694       total_rows = 0
695 
696       while num_rows_yielded == rows_per_fetch && (total_limit.nil? || total_rows < total_limit)
697         if total_limit && total_rows + rows_per_fetch > total_limit
698           rows_per_fetch = total_limit - total_rows
699         end
700 
701         num_rows_yielded = 0
702         limit(rows_per_fetch, offset).each do |row|
703           num_rows_yielded += 1
704           total_rows += 1 if total_limit
705           yield row
706         end
707 
708         offset += rows_per_fetch
709       end
710     end
711   end
712 
713   self
714 end