Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Fetching contributors…

Cannot retrieve contributors at this time

937 lines (642 sloc) 30.683 kb

Querying in Sequel

This guide is based on guides.rubyonrails.org/active_record_querying.html

Purpose of this Guide

Sequel is a simple to use, very flexible, and powerful database library that supports a wide variety of different querying methods. This guide aims to be a gentle introduction to Sequel's querying support.

While you can easily use raw SQL with Sequel, a large part of the advantage you get from using Sequel is Sequel's ability to abstract SQL from you and give you a much nicer interface.

Retrieving Objects

Sequel provides a few separate methods for retrieving objects from the database. The underlying method is Sequel::Dataset#each, which yields each row as the Sequel::Database provides it. However, while Dataset#each can and often is used directly, in many cases there is a more convenient retrieval method you can use.

Sequel::Dataset

If you are new to Sequel and aren't familiar with Sequel, you should probably read the “Dataset Basics” guide, then come back here.

Retrieving a Single Object

Sequel offers quite a few ways to to retrieve a single object.

Using a Primary Key [Sequel::Model]

The Sequel::Model.[] is the easiest method to use to find a model instance by its primary key value:

# Find artist with primary key (id) 1
artist = Artist[1]
# SELECT * FROM artists WHERE id = 1
=> #<Artist @values={:name=>"YJM", :id=>1}>

If there is no record with the given primary key, nil will be returned.

Using first

If you just want the first record in the dataset, Sequel::Dataset#first is probably the most obvious method to use:

artist = Artist.first
# SELECT * FROM artists LIMIT 1
=> #<Artist @values={:name=>"YJM", :id=>1}>

Any options you pass to first will be used as a filter:

artist = Artist.first(:name => 'YJM')
# SELECT * FROM artists WHERE (name = 'YJM') LIMIT 1
=> #<Artist @values={:name=>"YJM", :id=>1}>

artist = Artist.first(:name.like('Y%'))
# SELECT * FROM artists WHERE (name LIKE 'Y%') LIMIT 1
=> #<Artist @values={:name=>"YJM", :id=>1}>

Sequel::Dataset#[] is basically an alias for first, except it requires an argument:

DB[:artists][:name => 'YJM']
# SELECT * FROM artists WHERE (name = 'YJM') LIMIT 1
=> {:name=>"YJM", :id=>1}

Note that while Model.[] allows you to pass a primary key directly, Dataset#[] does not.

Using last

If you want the last record in the dataset, Sequel::Dataset#last is an obvious method to use. Note first that last requires that the dataset be ordered. Without an order, any object can be considered the first as well as the last.

artist = Artist.order(:name).last
# SELECT * FROM artists ORDER BY name DESC LIMIT 1
=> #<Artist @values={:name=>"YJM", :id=>1}>

Note that all last does is reverse the order of the dataset and then call first. This is why last raises a Sequel::Error if there is no order on the dataset, because otherwise it would provide the same record as first, and most users would find that confusing.

Note that last is not necessarily going to give you the last record in the dataset unless you give the dataset an unambiguous order.

Retrieving a Single Column Value

Sometimes, instead of wanting an entire row, you only want the value of a specific column. For this Sequel::Dataset#get is the method you want:

artist_name = Artist.get(:name)
# SELECT name FROM artists LIMIT 1
=> "YJM"

Retrieving Multiple Objects

As an Array of Hashes or Model Objects

In many cases, you want an array of all of the rows associated with the dataset, in which case Sequel::Dataset#all is the method you want to use:

artists = Artist.all
# SELECT * FROM artists
=> [#<Artist @values={:name=>"YJM", :id=>1}>,
    #<Artist @values={:name=>"AS", :id=>2}>]

Using an Enumerable Interface

Sequel::Dataset uses an Enumerable Interface, so it provides a method named each that yields hashes or model objects as they are retrieved from the database:

Artist.each{|x| p x.name}
# SELECT * FROM artists
"YJM"
"AS"

This means that all of the methods in the Enumerable module are available, such as map:

artist_names = Artist.map{|x| x.name}
# SELECT * FROM artists
=> ["YJM", "AS"]

As an Array of Column Values

Sequel also has an extended map method that takes an argument. If you provide an argument to map, it will return an array of values for the given column. So the previous example can be handled more easily with:

artist_names = Artist.map(:name)
# SELECT * FROM artists
=> ["YJM", "AS"]

One difference between these two ways of returning an array of values is that providing map with an argument is really doing:

artist_names = Artist.map{|x| x[:name]} # not x.name

Note that regardless of whether you provide map with an argument, it does not modify the columns selected. If you only want to select a single column and return an array of the columns values, you can use select_map:

artist_names = Artist.select_map(:name)
# SELECT name FROM artists
=> ["YJM", "AS"]

It's also common to want to order such a map, so Sequel provides a select_order_map method as well:

artist_names = Artist.select_order_map(:name)
# SELECT name FROM artists ORDER BY name
=> ["AS", "YJM"]

As a Hash

Sequel makes it easy to take an SQL query and return it as a ruby hash, using the to_hash method:

artist_names = Artist.to_hash(:name, :id)
# SELECT * FROM artists
=> {"YJM"=>1, "AS"=>2}

As you can see, the to_hash method uses the first symbol as the key and the second symbol as the value. So if you swap the two arguments the hash will have its keys and values transposed:

artist_names = Artist.to_hash(:id, :name)
# SELECT * FROM artists
=> {1=>"YJM", 2=>"AS"}

If you only provide one argument to to_hash, it uses the entire hash or model object as the value:

artist_names = DB[:artists].to_hash(:name)
# SELECT * FROM artists
=> {"YJM"=>{:id=>1, :name=>"YJM"}, "AS"=>{:id=>2, :name=>"AS"}}

Model datasets have a to_hash method that can be called without any arguments, in which case it will use the primary key as the key and the model object as the value. This can be used to easily create an identity map:

artist_names = Artist.to_hash
# SELECT * FROM artists
=> {1=>#<Artist @values={:id=>1, :name=>"YGM"}>,
    2=>#<Artist @values={:id=>2, :name=>"AS"}>}

Note that to_hash never modifies the columns selected. However, just like Sequel has a select_map method to modify the columns selected and return an array, Sequel also has a select_hash method to modify the columns selected and return a hash:

artist_names = Artist.select_hash(:name, :id)
# SELECT name, id FROM artists
=> {"YJM"=>1, "AS"=>2}

Modifying datasets

Note that the retrieval methods discussed above just return the row(s) included in the existing dataset. In most cases, you aren't interested in every row in a table, but in a subset of the rows, based on some criteria. In Sequel, filtering the dataset is generally done separately than retrieving the records.

There are really two types of dataset methods that you will be using:

  1. Methods that return row(s), discussed above

  2. Methods that return modified datasets, discussed below

Sequel uses a method chaining, functional style API to modify datasets. Let's start with a simple example.

This is a basic dataset that includes all records in the table artists:

ds1 = DB[:artists]
# SELECT * FROM artists

Let's say we are only interested in the artists whose names start with “A”:

ds2 = ds1.where(:name.like('A%'))
# SELECT * FROM artists WHERE name LIKE 'A%'

Here we see that where returns a dataset that adds a WHERE clause to the query. It's important to note that where does not modify the receiver:

ds1
# SELECT * FROM artists
ds2
# SELECT * FROM artists WHERE name LIKE 'A%'

In Sequel, most dataset methods that you will be using will not modify the dataset itself, so you can freely use the dataset in multiple places without worrying that its usage in one place will affect its usage in another place. This is what is meant by a functional style API.

Let's say we only want to select the id and name columns, and that we want to order by name:

ds3 = ds.order(:name).select(:id, :name)
# SELECT id, name FROM artists WHERE name LIKE 'A%' ORDER BY name

Note how you don't need to assign the returned value of order to a variable, and then call select on that. Because order just returns a dataset, you can call select directly on the returned dataset. This is what is meant by a method chaining API.

Also note how you can call methods that modify different clauses in any order. In this case, the WHERE clause was added first, then the ORDER clause, then the SELECT clause was modified. This makes for a flexible API, where you can modify any part of the query at any time.

Filters

Filtering is probably the most common dataset modifying action done in Sequel. Both the where and filter methods filter the dataset by modifying the dataset's WHERE clause. While not quite aliases of each other, they operate the same in most cases. Both accept a wide variety of input formats, discussed below.

Hashes

The most common format for providing filters is via a hash. In general, Sequel treats conditions specified with a hash as equality or inclusion. What type of condition is used depends on the values in the hash.

Unless Sequel has special support for the value's class, it uses a simple equality statement:

Artist.filter(:id=>1)
# SELECT * FROM artists WHERE id = 1

Artist.filter(:name=>'YJM')
# SELECT * FROM artists WHERE name = 'YJM'

For arrays, Sequel uses the IN operator.

Artist.filter(:id=>[1, 2])
# SELECT * FROM artists WHERE id IN (1, 2)

For datasets, Sequel uses the IN operator with a subselect:

Artist.filter(:id=>Album.select(:artist_id))
# SELECT * FROM artists WHERE id IN (
#   SELECT artist_id FROM albums)

For boolean values such as nil, true, and false, Sequel uses the IS operator:

Artist.filter(:id=>nil)
# SELECT * FROM artists WHERE id IS NULL

For ranges, Sequel uses a pair of inequality statements:

Artist.filter(:id=>1..5)
# SELECT * FROM artists WHERE id >= 1 AND id <= 5

Finally, for regexps, Sequel uses an SQL regular expression. Note that this is probably only supported on PostgreSQL and MySQL.

Artist.filter(:name=>/JM$/)
# SELECT * FROM artists WHERE name ~ 'JM$'

If there are multiple arguments in the hash, the filters are ANDed together:

Artist.filter(:id=>1, :name=>/JM$/)
# SELECT * FROM artists WHERE id = 1 AND name ~ 'JM$'

This works the same as if you used two separate filter calls:

Artist.filter(:id=>1).filter(:name=>/JM$/)
# SELECT * FROM artists WHERE id = 1 AND name ~ 'JM$'

Array of Two Element Arrays

If you use an array of two element arrays, it is treated as a hash. The only advantage to using an array of two element arrays is that it allows you to duplicate keys, so you can do:

Artist.filter([[:name, /JM$/], [:name, /^YJ/]])
# SELECT * FROM artists WHERE name ~ 'JM$' AND name ~ '^YJ'

Virtual Row Blocks

If a block is passed to filter, it is treated as a virtual row block:

Artist.filter{id > 5}
# SELECT * FROM artists WHERE id > 5

You can learn more about virtual row blocks in the “Virtual Rows” guide.

You can provide both regular arguments and a block, in which case the results will be ANDed together:

Artist.filter(:name=>'A'...'M'){id > 5}
# SELECT * FROM artists WHERE name >= 'A' AND name < 'M' AND id > 5

Symbols

If you have a boolean column in the database, and you want only true values, you can just provide the column symbol to filter:

Artist.where(:retired)
# SELECT * FROM artists WHERE retired

SQL::Expression

Sequel has a DSL that allows easily creating SQL expressions. These SQL expressions are instances of subclasses of Sequel::SQL::Expression. You've already seen an example earlier:

Artist.filter(:name.like('Y%'))
# SELECT * FROM artists WHERE name LIKE 'Y%'

In this case Symbol#like returns a Sequel::SQL::BooleanExpression object, which is used directly in the filter.

You can use the DSL to create arbitrarily complex expressions. SQL::Expression objects support the & operator for AND, the | operator for OR, and the ~ operator for inversion:

Artist.filter(:name.like('Y%') & ({:b=>1} | ~{:c=>3}))
# SELECT * FROM artists WHERE name LIKE 'Y%' AND (b = 1 OR c != 3)

You can combine these expression operators with the virtual row support:

Artist.filter{(a > 1) & ~((b(c) < 1) | d)}
# SELECT * FROM artists WHERE a > 1 AND b(c) >= 1 AND NOT d

Note the use of parentheses when using the & and | operators, as they have lower precedence than other operators. The following will not work:

Artist.filter{a > 1 & ~(b(c) < 1 | d)}
# Raises a TypeError, as it calls Integer#| with a Sequel::SQL::Identifier

Strings with Placeholders

Assuming you want to get your hands dirty and write some SQL, Sequel allows you to use strings using placeholders for the values:

Artist.filter("name LIKE ?", 'Y%')
# SELECT * FROM artists WHERE name LIKE 'Y%'

This is the most common type of placeholder, where each question mark is substituted with the next argument:

Artist.filter("name LIKE ? AND id = ?", 'Y%', 5)
# SELECT * FROM artists WHERE name LIKE 'Y%' AND id = 5

You can also use named placeholders with a hash, where the named placeholders use colons before the placeholder names:

Artist.filter("name LIKE :name AND id = :id", :name=>'Y%', :id=>5)
# SELECT * FROM artists WHERE name LIKE 'Y%' AND id = 5

You don't have to provide any placeholders if you don't want to:

Artist.filter("id = 2")
# SELECT * FROM artists WHERE id = 2

However, if you are using any untrusted input, you should definitely be using placeholders. In general, unless you are hardcoding values in the strings, you should use placeholders. You should never pass a string that has been built using interpolation, unless you are sure of what you are doing.

Artist.filter("id = #{params[:id]}") # Don't do this!
Artist.filter("id = ?", params[:id]) # Do this instead
Artist.filter(:id=>params[:id]) # Even better

Inverting

You may be wondering how to specify a not equals condition in Sequel, or the NOT IN operator. Sequel has generic support for inverting conditions, so to write a not equals condition, you write an equals condition, and invert it:

Artist.filter(:id=>5).invert
# SELECT * FROM artists WHERE id != 5

Note that invert inverts the entire filter:

Artist.filter(:id=>5).filter{name > 'A'}.invert
# SELECT * FROM artists WHERE id != 5 OR name <= 'A'

In general, invert is used rarely, since exclude allows you to invert only specific filters:

Artist.exclude(:id=>5)
# SELECT * FROM artists WHERE id != 5

Artist.filter(:id=>5).exclude{name > 'A'}
# SELECT * FROM artists WHERE id = 5 OR name <= 'A'

So to do a NOT IN with an array:

Artist.exclude(:id=>[1, 2])
# SELECT * FROM artists WHERE id NOT IN (1, 2)

Or to use the NOT LIKE operator:

Artist.exclude(:name.like('%J%'))
# SELECT * FROM artists WHERE name NOT LIKE '%J%'

Removing

To remove all existing filters, use unfiltered:

Artist.filter(:id=>1).unfiltered
# SELECT * FROM artists

Ordering

Sequel offers quite a few methods to manipulate the SQL ORDER BY clause. The most basic of these is order:

Artist.order(:id)
# SELECT * FROM artists ORDER BY id

You can specify multiple arguments to order by more than one column:

Album.order(:artist_id, :id)
# SELECT * FROM album ORDER BY artist_id, id

Note that unlike filter, order replaces an existing order, it does not append to an existing order:

Artist.order(:id).order(:name)
# SELECT * FROM artists ORDER BY name

If you want to add a column to the end of the existing order:

Artist.order(:id).order_append(:name)
# SELECT * FROM artists ORDER BY id, name

If you want to add a column to the beginning of the existing order:

Artist.order(:id).order_prepend(:name)
# SELECT * FROM artists ORDER BY name, id

Reversing

Just like you can invert an existing filter, you can reverse an existing order, using reverse:

Artist.order(:id).reverse
# SELECT FROM artists ORDER BY id DESC

As you might expect, reverse is not used all that much. In general, Symbol#desc is used more commonly to specify a descending order for columns:

Artist.order(:id.desc)
# SELECT FROM artists ORDER BY id DESC

This allows you to easily use both ascending and descending orders:

Artist.order(:name, :id.desc)
# SELECT FROM artists ORDER BY name, id DESC

Removing

Just like you can remove filters with unfiltered, you can remove orders with unordered:

Artist.order(:name).unordered
# SELECT * FROM artists

Selected Columns

Sequel offers a few methods to manipulate the columns selected. As you may be able to guess, the main method used is select:

Artist.select(:id, :name)
# SELECT id, name FROM artists

You just specify all of the columns that you are selecting as arguments to the method.

If you are dealing with model objects, you'll want to include the primary key if you want to update or destroy the object. You'll also want to include any keys (primary or foreign) related to associations you plan to use.

If a column is not selected, and you attempt to access it, you will get nil:

artist = Artist.select(:name).first
# SELECT name FROM artists LIMIT 1

artist[:id]
# => nil

Like order, select replaces the existing selected columns:

Artist.select(:id).select(:name)
# SELECT name FROM artists

To add to the existing selected columns, use select_append:

Artist.select(:id).select_append(:name)
# SELECT id, name FROM artists

To remove specifically selected columns, and default back to all columns, use select_all:

Artist.select(:id).select_all
# SELECT * FROM artists

Distinct

To treat duplicate rows as a single row when retrieving the records, use distinct:

Artist.distinct.select(:name)
# SELECT DISTINCT name FROM artists

Note that DISTINCT is a separate SQL clause, it's not a function that you pass to select.

Limit and Offset

You can limit the dataset to a given number of rows using limit:

Artist.limit(5)
# SELECT * FROM artists LIMIT 5

You can provide a second argument to limit to specify an offset:

Artist.limit(5, 10)
# SELECT * FROM artists LIMIT 5 OFFSET 10

This would return the 11th through 15th records in the original dataset.

To remove a limit from a dataset, use unlimited:

Artist.limit(5, 10).unlimited
# SELECT * FROM artists

Grouping

The SQL GROUP BY clause is used to combine multiple rows based on the values of a given group of columns.

To modify the GROUP BY clause of the SQL statement, you use group:

Album.group(:artist_id)
# SELECT * FROM albums GROUP BY artist_id

You can remove an existing grouping using ungrouped:

Album.group(:artist_id).ungrouped
# SELECT * FROM albums

A common use of grouping is to count based on the number of grouped rows, and Sequel provides a group_and_count method to make this easier:

Album.group_and_count(:artist_id)
# SELECT artist_id, COUNT(*) AS count FROM albums GROUP BY artist_id

This will return the number of albums for each artist_id.

Having

The SQL HAVING clause is similar to the WHERE clause, except that filters the results after the grouping has been applied, instead of before. One possible use is if you only wanted to return artists who had at least 10 albums:

Album.group_and_count(:artist_id).having{count >= 10}
# SELECT artist_id, COUNT(*) AS count FROM albums
# GROUP BY artist_id HAVING count >= 10

If you have an existing HAVING clause on your dataset, then filter will add to the HAVING clause instead of the WHERE clause:

Album.group_and_count(:artist_id).
 having{count >= 10}.filter{count < 15}
# SELECT artist_id, COUNT(*) AS count FROM albums
# GROUP BY artist_id HAVING count >= 10 AND count < 15

Unlike filter, where always affects the WHERE clause:

Album.group_and_count(:artist_id).
 having{count >= 10}.where(:name.like('A%'))
# SELECT artist_id, COUNT(*) AS count FROM albums
# WHERE name LIKE 'A%' GROUP BY artist_id HAVING count >= 10

Both the WHERE clause and the HAVING clause are removed by unfiltered:

Album.group_and_count(:artist_id).having{count >= 10}.
 where(:name.like('A%')).unfiltered
# SELECT artist_id, COUNT(*) AS count FROM albums GROUP BY artist_id

Joins

Sequel makes it very easy to join a dataset to another table or dataset. The underlying method used is join_table:

Album.join_table(:inner, :artists, :id=>:artist_id)
# SELECT * FROM albums
# INNER JOIN artists ON artists.id = albums.artist_id

In most cases, you won't call join_table directly, as Sequel provides shortcuts for all common (and most uncommon) join types. For example join does an inner join:

Album.join(:artists, :id=>:artist_id)
# SELECT * FROM albums
# INNER JOIN artists ON artists.id = albums.artist_id

And left_join does a LEFT JOIN:

Album.left_join(:artists, :id=>:artist_id)
# SELECT * FROM albums
# LEFT JOIN artists ON artists.id = albums.artist_id

Table/Dataset to Join

For all of these specialized join methods, the first argument is generally the name of the table to which you are joining. However, you can also provide a model class:

Album.join(Artist, :id=>:artist_id)

Or a dataset, in which case a subselect is used:

Album.join(Artist.filter{name < 'A'}, :id=>:artist_id)
# SELECT * FROM albums
# INNER JOIN (SELECT * FROM artists WHERE (name < 'A')) AS t1
#  ON (t1.id = albums.artist_id)

Join Conditions

The second argument to the specialized join methods is the conditions to use when joining, which is similar to a filter expression, with a few minor exceptions.

Implicit Qualification

A hash used as the join conditions operates similarly to a filter, except that unqualified symbol keys are automatically qualified with the table from the first argument, and unqualified symbol values are automatically qualified with the first table or the last table joined. This implicit qualification is one of the reasons that joins in Sequel are easy to specify:

Album.join(:artists, :id=>:artist_id)
# SELECT * FROM albums
# INNER JOIN artists ON artists.id = albums.artist_id

Note how the :id symbol is automatically qualified with artists, while the artist_id symbol is automatically qualified with albums.

Because Sequel uses the last joined table for implicit qualifications of values, you can do things like:

Album.join(:artists, :id=>:artist_id).
 join(:members, :artist_id=>:id)
# SELECT * FROM albums
# INNER JOIN artists ON artists.id = albums.artist_id
# INNER JOIN members ON members.artist_id = artists.id

Note that when joining to the members table, artist_id is qualified with members and id is qualified with artists.

While a good default, implicit qualification is not always correct:

Album.join(:artists, :id=>:artist_id).
 join(:tracks, :album_id=>:id)
# SELECT * FROM albums
# INNER JOIN artists ON artists.id = albums.artist_id
# INNER JOIN tracks ON tracks.album_id = artists.id

Note here how id is qualified with artists instead of albums. This is wrong as the foreign key tracks.album_id refers to albums.id, not artists.id. To fix this, you need to explicitly qualify when joining:

Album.join(:artists, :id=>:artist_id).
 join(:tracks, :album_id=>:albums__id)
# SELECT * FROM albums
# INNER JOIN artists ON artists.id = albums.artist_id
# INNER JOIN tracks ON tracks.album_id = albums.id

Just like in filters, an array of two element arrays is treated the same as a hash, but allows for duplicate keys:

Album.join(:artists, [[:id, :artist_id], [:id, 1..5]])
# SELECT * FROM albums INNER JOIN artists
#  ON artists.id = albums.artist_id
#   AND artists.id >= 1 AND artists.id <= 5

And just like in the hash case, unqualified symbol elements in the array are implicitly qualified.

USING Joins

The most common type of join conditions is a JOIN ON, as displayed above. However, the SQL standard allows for join conditions to be specified with JOIN USING, which Sequel makes easy to use.

JOIN USING is useful when the columns you are using have the same names in both tables. For example, if instead of having a primary column named id in all of your tables, you use artist_id in your artists table and album_id in your albums table, you could do:

Album.join(:artists, [:artist_id])
# SELECT * FROM albums INNER JOIN artists USING (artist_id)

See here how you specify the USING columns as an array of symbols.

NATURAL Joins

NATURAL Joins take it one step further than USING joins, by assuming that all columns with the same names in both tables should be used for joining:

Album.natural_join(:artists)
# SELECT * FROM albums NATURAL JOIN artists

In this case, you don't even need to specify any conditions.

Join Blocks

You can provide a block to any of the join methods that accept conditions. This block should accept 3 arguments, the table alias for the table currently being joined, the table alias for the last table joined (or first table), and an array of previous Sequel::SQL::JoinClauses.

This allows you to qualify columns similar to how the implicit qualification works, without worrying about the specific aliases being used. For example, lets say you wanted to join the albums and artists tables, but only want albums where the artist's name comes before the album's name.

Album.join(:artists, :id=>:artist_id) do |j, lj, js|
  :name.qualify(j) < :name.qualify(lj)
end
# SELECT * FROM albums INNER JOIN artists
# ON artists.id = albums.artist_id
# AND artists.name < albums.name

Because greater than can't be expressed with a hash in Sequel, you need to use a block and qualify the tables manually.

From

In general, the FROM table is the first clause populated when creating a dataset. For a standard Sequel::Model, the dataset already has the FROM clause populated, and the most common way to create datasets is with the Database#[] method, which populates the FROM clause.

However, you can modify the tables you are selecting FROM using from:

Album.from(:albums, :old_albums)
# SELECT * FROM albums, old_albums

Be careful with this, as multiple tables in the FROM clause use a cross join by default, so the number of rows will be number of albums times the number of old albums.

Using multiple FROM tables and setting conditions in the WHERE clause is an old-school way of joining tables:

DB.from(:albums, :artists).where(:artists__id=>:albums__artist_id)
# SELECT * FROM albums, artists WHERE artists.id = albums.artist_id

Using the current dataset in a subselect

In some cases, you may want to wrap the current dataset in a subselect. Here's an example using from_self:

Album.order(:artist_id).limit(100).from_self.group(:artist_id)
# SELECT * FROM (SELECT * FROM albums ORDER BY artist_id LIMIT 100)
#  AS t1 GROUP BY artist_id

This is slightly different than without from_self:

Album.order(:artist_id).limit(100).group(:artist_id)
# SELECT * FROM albums GROUP BY artist_id ORDER BY name LIMIT 100

Without from_self, you are doing the grouping, and limiting the number of grouped records returned to 100. So assuming you have albums by more than 100 artists, you'll end up with 100 results.

With from_self, you are limiting the number of records before grouping. So if the artist with the lowest id had 100 albums, you'd get 1 result, not 100.

Locking for Update

Sequel allows you to easily add a FOR UPDATE clause to your queries so that the records returned can't be modified by another query until the current transaction commits. You just use the for_update dataset method when returning the rows:

DB.transaction do
  album = Album.for_update.first(:id=>1)
  # SELECT * FROM albums WHERE id = 1 FOR UPDATE
  album.num_tracks += 1
  album.save
end

This will ensure that no other connection modifies the row between when you select it and when the transaction ends.

Optimistic Locking

One of Sequel's built-in model plugins is an optimistic locking plugin, which provides a database independent way to detect and raise an error if two different connections modify the same row. It's useful for things like web forms where you cannot keep a transaction open while the user is looking at the form, because of the web's stateless nature.

Custom SQL

Sequel makes it easy to use custom SQL by providing it to the Database#[] method as a string:

DB["SELECT * FROM artists"]
# SELECT * FROM artists

You can also use the with_sql dataset method to return a dataset that uses that exact SQL:

DB[:albums].with_sql("SELECT * FROM artists")
# SELECT * FROM artists

With either of these methods, you can use placeholders:

DB["SELECT * FROM artists WHERE id = ?", 5]
# SELECT * FROM artists WHERE id = 5

DB[:albums].with_sql("SELECT * FROM artists WHERE id = :id", :id=>5)
# SELECT * FROM artists WHERE id = 5

Checking for Records

If you just want to know whether the current dataset would return any rows, use empty?:

Album.empty?
# SELECT 1 FROM albums LIMIT 1
=> false

Album.filter(:id=>0).empty?
# SELECT 1 FROM albums WHERE id = 0 LIMIT 1
=> true

Album.filter(:name.like('R%')).empty?
# SELECT 1 FROM albums WHERE name LIKE 'R%' LIMIT 1
=> false

Aggregate Calculations

The SQL standard defines a few helpful methods to get aggreate information about datasets, such as count, sum, avg, min, and max. There are dataset methods for each of these aggregate functions.

count just returns the number of records in the dataset.

Album.count
# SELECT COUNT(*) AS count FROM albums LIMIT 1
=> 2

The other methods take a column argument and call the aggregate function with the argument:

Album.sum(:id)
# SELECT sum(id) FROM albums LIMIT 1
=> 3

Album.avg(:id)
# SELECT avg(id) FROM albums LIMIT 1
=> 1.5

Album.min(:id)
# SELECT min(id) FROM albums LIMIT 1
=> 1

Album.max(:id)
# SELECT max(id) FROM albums LIMIT 1
=> 2
Jump to Line
Something went wrong with that request. Please try again.