Class Sequel::Dataset

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

Returns the first record matching the conditions. Examples:

  ds[:id=>1] => {:id=1}

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

  ds[:id=>1] = {:id=>2} # SQL: UPDATE ... SET id = 2 WHERE id = 1

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

SQL fragment for the aliased expression

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.

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

  ds.filter(:a).and(:b) # SQL: WHERE a AND b

SQL fragment for the SQL array.

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

Returns the average value for the given column.

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

SQL fragment for specifying given CaseExpression.

SQL fragment for the SQL CAST expression.

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

SQL fragment for specifying all columns in a given table.

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

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

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

SQL fragment for complex expressions

Returns the number of records in the dataset.

Add a mutation method to this dataset instance.

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

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

  dataset.filter{|o| o.price >= 100}.delete_sql #=>
    "DELETE FROM items WHERE (price >= 100)"

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

 dataset.distinct # SQL: SELECT DISTINCT * FROM items
 dataset.order(:id).distinct(:id) # SQL: SELECT DISTINCT ON (id) * FROM items ORDER BY id

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

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

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

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

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

Performs the inverse of Dataset#filter.

  dataset.exclude(:category => 'software').sql #=>
    "SELECT * FROM items WHERE (category != 'software')"

Returns an EXISTS clause for the dataset as a LiteralString.

  DB.select(1).where(DB[:items].exists).sql
  #=> "SELECT 1 WHERE EXISTS (SELECT * FROM items)"

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

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

filter accepts the following argument types:

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

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

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

Examples:

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

Multiple filter calls can be chained for scoping:

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

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

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

  ds.first => {:id=>7}
  ds.first(2) => [{:id=>6}, {:id=>4}]
  ds.order(:id).first(2) => [{:id=>1}, {:id=>2}]
  ds.first(:id=>2) => {:id=>2}
  ds.first("id = 3") => {:id=>3}
  ds.first("id = ?", 4) => {:id=>4}
  ds.first{|o| o.id > 2} => {:id=>5}
  ds.order(:id).first{|o| o.id > 2} => {:id=>3}
  ds.first{|o| o.id > 2} => {:id=>5}
  ds.first("id > ?", 4){|o| o.id < 6} => {:id=>5}
  ds.order(:id).first(2){|o| o.id < 2} => [{:id=>1}]

Alias for first_source_alias

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

Returns a copy of the dataset with the source changed.

  dataset.from # SQL: SELECT *
  dataset.from(:blah) # SQL: SELECT * FROM blah
  dataset.from(:blah, :foo) # SQL: SELECT * FROM blah, foo

Returns a dataset selecting from the current dataset.

  ds = DB[:items].order(:name)
  ds.sql #=> "SELECT * FROM items ORDER BY name"
  ds.from_self.sql #=> "SELECT * FROM (SELECT * FROM items ORDER BY name)"

SQL fragment specifying an SQL function call

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

  ds.get(:id)
  ds.get{|o| o.sum(:id)}

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

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

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

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

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

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

Arguments:

• dataset - Can be a symbol (specifying a table), another dataset, or an object that responds to .dataset and return a symbol or a dataset
• join_conditions - Any condition(s) allowed by join_table.
• options - A hash of graph options. The following options are currently used:
  • :implicit_qualifier - The qualifier of implicit conditions, see join_table.
  • :join_type - The type of join to use (passed to join_table). Defaults to :left_outer.
  • :select - An array of columns to select. When not used, selects all columns in the given dataset. When set to false, selects no columns and is like simply joining the tables, though graph keeps some metadata about join that makes it important to use graph instead of join.
  • :table_alias - The alias to use for the table. If not specified, doesn’t alias the table. You will get an error if the the alias (or table) name is used more than once.
• block - A block that is passed to join_table.

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

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

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

  dataset.group(:id) # SELECT * FROM items GROUP BY id
  dataset.group(:id, :name) # SELECT * FROM items GROUP BY id, name

Returns a dataset grouped by the given column with count by group, order by the count of records. Examples:

  ds.group_and_count(:name) => [{:name=>'a', :count=>1}, ...]
  ds.group_and_count(:first_name, :last_name) => [{:first_name=>'a', :last_name=>'b', :count=>1}, ...]

Alias for group

Returns a copy of the dataset with the HAVING conditions changed. Raises an error if the dataset has not been grouped. See filter for argument types.

  dataset.group(:sum).having(:sum=>10) # SQL: SELECT * FROM items GROUP BY sum HAVING sum = 10

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

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

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

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

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

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

  # this will commit every 50 records
  dataset.import([:x, :y], [[1, 2], [3, 4], ...], :slice => 50)

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

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

Formats an INSERT statement using the given values. If a hash is given, the resulting statement includes column names. If no values are given, the resulting statement includes a DEFAULT VALUES clause.

  dataset.insert_sql #=> 'INSERT INTO items DEFAULT VALUES'
  dataset.insert_sql(1,2,3) #=> 'INSERT INTO items VALUES (1, 2, 3)'
  dataset.insert_sql(:a => 1, :b => 2) #=>
    'INSERT INTO items (a, b) VALUES (1, 2)'

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

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

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

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

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

Inverts the current filter

  dataset.filter(:category => 'software').invert.sql #=>
    "SELECT * FROM items WHERE (category != 'software')"

SQL fragment specifying a JOIN clause without ON or USING.

SQL fragment specifying a JOIN clause with ON.

Returns a joined dataset. Uses the following arguments:

• type - The type of join to do (e.g. :inner)
• table - Depends on type:
  • Dataset - a subselect is performed with an alias of tN for some value of N
  • Model (or anything responding to :table_name) - table.table_name
  • String, Symbol: table
• expr - specifies conditions, depends on type:
  • Hash, Array with all two pairs - Assumes key (1st arg) is column of joined table (unless already qualified), and value (2nd arg) is column of the last joined or primary table (or the :implicit_qualifier option). To specify multiple conditions on a single joined table column, you must use an array. Uses a JOIN with an ON clause.
  • Array - If all members of the array are symbols, considers them as columns and uses a JOIN with a USING clause. Most databases will remove duplicate columns from the result set if this is used.
  • nil - If a block is not given, doesn’t use ON or USING, so the JOIN should be a NATURAL or CROSS join. If a block is given, uses a ON clause based on the block, see below.
  • Everything else - pretty much the same as a using the argument in a call to filter, so strings are considered literal, symbols specify boolean columns, and blockless filter expressions can be used. Uses a JOIN with an ON clause.
• options - a hash of options, with any of the following keys:
  • :table_alias - the name of the table’s alias when joining, necessary for joining to the same table more than once. No alias is used by default.
  • :implicit_qualifer - The name to use for qualifying implicit conditions. By default, the last joined or primary table is used.
• block - The block argument should only be given if a JOIN with an ON clause is used, in which case it yields the table alias/name for the table currently being joined, the table alias/name for the last joined (or first table), and an array of previous SQL::JoinClause.

SQL fragment specifying a JOIN clause with USING.

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

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

  dataset.limit(10) # SQL: SELECT * FROM items LIMIT 10
  dataset.limit(10, 20) # SQL: SELECT * FROM items LIMIT 10 OFFSET 20

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

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

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

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

  ds.map(:id) => [1, 2, 3, ...]
  ds.map{|r| r[:id] * 2} => [2, 4, 6, ...]

Returns the maximum value for the given column.

Returns the minimum value for the given column.

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

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

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

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

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

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

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

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

  dataset.filter(:a).or(:b) # SQL: SELECT * FROM items WHERE a OR b

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

  ds.order(:name).sql #=> 'SELECT * FROM items ORDER BY name'
  ds.order(:a, :b).sql #=> 'SELECT * FROM items ORDER BY a, b'
  ds.order('a + b'.lit).sql #=> 'SELECT * FROM items ORDER BY a + b'
  ds.order(:a + :b).sql #=> 'SELECT * FROM items ORDER BY (a + b)'
  ds.order(:name.desc).sql #=> 'SELECT * FROM items ORDER BY name DESC'
  ds.order(:name.asc).sql #=> 'SELECT * FROM items ORDER BY name ASC'
  ds.order{|o| o.sum(:name)}.sql #=> 'SELECT * FROM items ORDER BY sum(name)'
  ds.order(nil).sql #=> 'SELECT * FROM items'

Alias for order

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

  ds.order(:a).order(:b).sql #=> 'SELECT * FROM items ORDER BY b'
  ds.order(:a).order_more(:b).sql #=> 'SELECT * FROM items ORDER BY a, b'

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

SQL fragment for a literal string with placeholders

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

  ps = prepare(:select, :select_by_name)
  ps.call(:name=>'Blah')
  db.call(:select_by_name, :name=>'Blah')

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

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

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

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

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

Whether this dataset quotes identifiers.

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

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

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

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

Alias for reverse_order

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

Split the schema information from the table

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

  dataset.select(:a) # SELECT a FROM items
  dataset.select(:a, :b) # SELECT a, b FROM items
  dataset.select{|o| o.a, o.sum(:b)} # SELECT a, sum(b) FROM items

Returns a copy of the dataset selecting the wildcard.

  dataset.select(:a).select_all # SELECT * FROM items

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

  dataset.select(:a).select(:b) # SELECT b FROM items
  dataset.select(:a).select_more(:b) # SELECT a, b FROM items

Formats a SELECT statement

  dataset.select_sql # => "SELECT * 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 (which is SELECT uses :read_only database and all other queries use the :default database).

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

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

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

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

Arguments:

• graph_aliases - Should be a hash with keys being symbols of column aliases, and values being arrays with two or three elements. The first element of the array should be the table alias symbol, and the second should be the actual column name symbol. If the array has a third element, it is used as the value returned, instead of table_alias.column_name.

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

Returns the first record in the dataset.

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

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

SQL fragment for specifying subscripts (SQL arrays)

Returns the sum for the given column.

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

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

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

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

Whether the dataset supports the IS TRUE syntax.

Whether the dataset supports window functions.

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

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

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.

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

  dataset.group(:a).having(:a=>1).where(:b).unfiltered # SELECT * FROM items

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

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

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

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

Returns a copy of the dataset with no order.

  dataset.order(:a).unordered # SELECT * FROM items

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

Formats an UPDATE statement using the given values.

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

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

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

  dataset.group(:a).having(:a).filter(:b) # SELECT * FROM items GROUP BY a HAVING a AND b
  dataset.group(:a).having(:a).where(:b) # SELECT * FROM items WHERE b GROUP BY a HAVING a

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

The SQL fragment for the given window’s options.

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

• :args - Specify the arguments/columns for the CTE, should be an array of symbols.
• :recursive - Specify that this is a recursive CTE

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.

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

  dataset.with_sql('SELECT * FROM foo') # SELECT * FROM foo