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.

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.

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+.

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

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'))

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.

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.

Alias for where.

Returns a cloned dataset with a :update lock style.

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

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)

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)

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 '\'))

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)

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}, ...]

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

Alias of group

Adds the appropriate CUBE syntax to GROUP BY.

Adds the appropriate ROLLUP syntax to GROUP BY.

Adds the appropriate GROUPING SETS syntax to GROUP BY.

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)

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

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.

Alias of inner_join

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)))

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))

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

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

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

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))

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

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)

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}]

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

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

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

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

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

Alias of order

Alias of order_append.

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

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)

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

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

Alias of reverse

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

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

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

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)

Alias for select_append.

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

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

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.

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

Skip locked rows when returning results from this dataset.

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

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

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

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

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

Returns a copy of the dataset with no order.

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

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.

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)

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

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.

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

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}]

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)

Add the dataset to the list of compounds

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

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

SELECT * FROM table
SELECT table.* FROM table

A frozen array for the currently selected columns.

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

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

If invert is true, invert the condition.

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

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

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.

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

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

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

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.

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

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)]

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

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

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)

Returns the first record matching the conditions. Examples:

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

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}

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.

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'], ...]

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

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]

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

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

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

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))

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.

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

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

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}]

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

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

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

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.

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

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}]

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'], ...]

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