Sequel::Model has the most powerful and flexible associations of any ruby ORM.
"Extraordinary claims require extraordinary proof" - Carl Sagan
There are a bunch of advanced association options that are available to handle the other-than-bog-standard cases. First we’ll go over some of the simpler ones:
All associations take a block that can be used to further filter/modify the default dataset. There’s also an :eager_block option if you want to use a different block when eager loading via Dataset#eager. Association blocks are useful for things like:
Artist.one_to_many :gold_albums, :class=>:Album do |ds| ds.filter{|o| o.copies_sold > 500000} end
There are a whole bunch of options for changing how the association is eagerly loaded via Dataset#eager_graph: :graph_block, :graph_conditions, :graph_only_conditions, :graph_join_type (and :graph_join_table_* ones for JOINing to the join table in a many_to_many association).
-
:graph_join_type - The type of join to do
-
:graph_conditions - Additional conditions to put on join (needs to be a hash or array of all two pairs). Automatically assumes unqualified symbols as first element of the pair to be columns of the associated model, and unqualified symbols of the second element of the pair to be columns of the current model.
-
:graph_block - A block passed to join_table, allowing you to specify conditions other than equality, or to use OR, or set up any arbitrary condition. The block is passed the associated table alias, current model alias, and array of previous joins.
-
:graph_only_conditions - Use these conditions instead of the standard association conditions. This is necessary when you don’t want to have an equal condition between the foreign key and primary key of the tables. You can also use this to have a JOIN USING (array of symbols), or a NATURAL or CROSS JOIN (nil, with the appropriate :graph_join_type).
These can be used like this:
# Makes Artist.eager_graph(:required_albums).all not return artists that # don't have any albums Artist.one_to_many :required_albums, :class=>:Album, :graph_join_type=>:inner # Makes sure all returned albums have the active flag set Artist.one_to_many :active_albums, :class=>:Album, \ :graph_conditions=>{:active=>true} # Only returns albums that have sold more than 500,000 copies Artist.one_to_many :gold_albums, :class=>:Album, \ :graph_block=>proc{|j,lj,js| :copies_sold.qualify(j) > 500000} # Handles the case where the to tables are associated by a case insensitive name string Artist.one_to_many :albums, :key=>:artist_name, \ :graph_only_conditions=>nil, \ :graph_block=>proc{|j,lj,js| {:lower.sql_function(artist_name.qualify(j))=>:lower.sql_function(name.qualify(lj))}} # Handles the case where both key columns have the name artist_name, and you want to use # a JOIN USING Artist.one_to_many :albums, :key=>:artist_name, :graph_only_conditions=>[:artist_name]
Remember, using #eager_graph is generally only necessary when you need to filter/order based on columns in an associated table, it is recommended to use #eager for eager loading if possible.
For lazy loading (e.g. Model.association), the :dataset option can be used to specify an arbitrary dataset (one that uses different keys, multiple keys, joins to other tables, etc.).
For eager loading via #eager, the :eager_loader option can be used to specify how to eagerly load a complex association. This is an extremely powerful option. Though it can often be verbose (compared to other things in Sequel), it allows you complete control over how to eagerly load associations for a group of objects.
:eager_loader should be a proc that takes 3 arguments, a key_hash, an array of records, and a hash of dependent associations. Since you are given all of the records, you can do things like filter on associations that are specified by multiple keys, or do multiple queries depending on the content of the records (which would be necessary for polymorphic associations). Inside the :eager_loader proc, you should get the related objects and populate the associations cache for all objects in the array of records. The hash of dependent associations is available for you to cascade the eager loading down multiple levels, but it is up to you to use it. The key_hash is a performance enhancement that is used by the default code and is also available to you. It is a hash with keys being foreign/primary key symbols in the current table, and the values being hashes where the key is foreign/primary key values and values being arrays of current model objects having the foreign/primary key value associated with the key. This is hard to visualize, so I’ll give an example:
album1 = Album.load(:id=>1, :artist_id=>2) album2 = Album.load(:id=>3, :artist_id=>2) Album.many_to_one :artist Album.one_to_many :tracks Album.eager(:band, :tracks).all # The key_hash provided to the :eager_loader proc would be: {:id=>{1=>[album1], 3=>[album2]}, :artist_id=>{2=>[album1, album2]}}
Using these options, you can build associations Sequel doesn’t natively support, and still be able to use the same eager loading features that you are used to.
Sequel supports all of associations that ActiveRecord supports, one way or another. Sometimes this requires more code, as Sequel is a toolkit and not a swiss army chainsaw.
Sequel supports the same callbacks that ActiveRecord does: :before_add, :before_remove, :after_add, and :after_remove. It also supports a callback that ActiveRecord does not, :after_load, which is called after the association has been loaded.
Each of these options can be a Symbol specifying an instance method that takes one argument (the associated object), or a Proc that takes two arguments (the current object and the associated object), or an array of Symbols and Procs. For :after_load with a *_to_many association, the associated object argument is an array of associated objects.
If any of the before callbacks return false, the adding/removing does not happen and it either raises an error (the default), or returns nil (if raise_on_save_failure is false).
All callbacks are also run on many_to_one associations. If there was already an existing object for the association, it calls the remove callbacks on the existing object and the add callbacks on the new object. The remove callback calls are placed around the add callback calls.
All associations come with an association_dataset method that can be further filtered or otherwise modified:
class Author < Sequel::Model one_to_many :authorships end Author.first.authorships_dataset.filter{|o| o.number < 10}.first
You can extend a dataset with a module easily with :extend. You can reference the model object that created the association dataset via the dataset’s model_object method, and the related association reflection via the dataset’s association_reflection method:
module FindOrCreate def find_or_create(vals) first(vals) || model.create(vals.merge(association_reflection[:key]=>model_object.id)) end end class Author < Sequel::Model one_to_many :authorships, :extend=>FindOrCreate end Author.first.authorships_dataset.find_or_create(:name=>'Blah', :number=>10)
many_to_many handles the usual case of a has_many :through with a belongs_to in the associated model. It doesn’t break on the case where the join table is a model table, unlike ActiveRecord’s has_and_belongs_to_many.
ActiveRecord:
class Author < ActiveRecord::Base has_many :authorships has_many :books, :through => :authorships end class Authorship < ActiveRecord::Base belongs_to :author belongs_to :book end @author = Author.find :first @author.books
Sequel::Model:
class Author < Sequel::Model one_to_many :authorships many_to_many :books, :join_table=>:authorships end class Authorship < Sequel::Model many_to_one :author many_to_one :book end @author = Author.first @author.books
If you use an association other than belongs_to in the associated model, things are a bit more involved (has_many :through a has_many association):
ActiveRecord:
class Firm < ActiveRecord::Base has_many :clients has_many :invoices, :through => :clients end class Client < ActiveRecord::Base belongs_to :firm has_many :invoices end class Invoice < ActiveRecord::Base belongs_to :client has_one :firm, :through => :client end Firm.find(:first).invoices
Sequel::Model:
class Firm < Sequel::Model one_to_many :clients one_to_many :invoices, :read_only=>true, \ :dataset=>proc{Invoice.eager_graph(:client).filter(:client__firm_id=>pk)}, \ :after_load=>(proc do |firm, invs| invs.each do |inv| inv.client.associations[:firm] = inv.associations[:firm] = firm end end), \ :eager_loader=>(proc do |key_hash, firms, associations| id_map = key_hash[Firm.primary_key] firms.each{|firm| firm.associations[:invoices] = []} Invoice.eager_graph(:client).filter(:client__firm_id=>id_map.keys).all do |inv| id_map[inv.client.firm_id].each do |firm| inv.client.associations[:firm] = inv.associations[:firm] = firm firm.associations[:invoices] << inv end end end) end class Client < Sequel::Model many_to_one :firm one_to_many :invoices end class Invoice < Sequel::Model many_to_one :client many_to_one :firm, :key=>nil, :read_only=>true, \ :dataset=>proc{Firm.eager_graph(:clients).filter(:clients__id=>client_id)}, \ :after_load=>(proc do |inv, firm| # Delete the cached associations from firm, because it only has the # client with this invoice, instead of all clients of the firm inv.associations[:client] = firm.associations.delete(:clients).first end), \ :eager_loader=>(proc do |key_hash, invoices, associations| id_map = {} invoices.each do |inv| inv.associations[:firm] = nil inv.associations[:client] = nil (id_map[inv.client_id] ||= []) << inv end Firm.eager_graph(:clients).filter(:clients__id=>id_map.keys).all do |firm| # Delete the cached associations from firm, because it only has the # clients related the invoices being eagerly loaded, instead of all # clients of the firm. firm.associations.delete(:clients).each do |client| id_map[client.pk].each do |inv| inv.associations[:firm] = firm inv.associations[:client] = client end end end end) end Firm.find(:first).invoices
It is significantly more code in Sequel Model, but quite a bit of it is setting the intermediate associated record (the client) and the reciprocal association in the associations cache for each object, which ActiveRecord won’t do for you. The reason you would want to do this is that then firm.invoices.first.firm or firm.invoices.first.client doesn’t do another query to get the firm/client.
Polymorphic associations are really a design flaw. The only advantage polymorphic associations offer is that they require fewer join tables.
Proof by Reductio ad absurdum: If fewer join tables are preferable, then surely fewer tables and columns are preferrable, so you might as well store all of your data in a single column in a single table if you think polymorphic associations are a good idea.
Compelling Argument: Polymorphic associations are more complex than normal associations, and they break referential integrity, so the only reason you should use them is if you are already stuck with an existing design that uses them. You should never use them in new code.
ActiveRecord:
class Asset < ActiveRecord::Base belongs_to :attachable, :polymorphic => true end class Post < ActiveRecord::Base has_many :assets, :as => :attachable end class Note < ActiveRecord::Base has_many :assets, :as => :attachable end @asset.attachable = @post @asset.attachable = @note
Sequel::Model:
class Asset < Sequel::Model many_to_one :attachable, :reciprocal=>:assets, \ :dataset=>(proc do klass = attachable_type.constantize klass.filter(klass.primary_key=>attachable_id) end), \ :eager_loader=>(proc do |key_hash, assets, associations| id_map = {} assets.each do |asset| asset.associations[:attachable] = nil ((id_map[asset.attachable_type] ||= {})[asset.attachable_id] ||= []) << asset end id_map.each do |klass_name, id_map| klass = klass_name.constantize klass.filter(klass.primary_key=>id_map.keys).all do |attach| id_map[attach.pk].each do |asset| asset.associations[:attachable] = attach end end end end) private def _attachable=(attachable) self[:attachable_id] = (attachable.pk if attachable) self[:attachable_type] = (attachable.class.name if attachable) end end class Post < Sequel::Model one_to_many :assets, :key=>:attachable_id do |ds| ds.filter(:attachable_type=>'Post') end private def _add_asset(asset) asset.attachable_id = pk asset.attachable_type = 'Post' asset.save end def _remove_asset(asset) asset.attachable_id = nil asset.attachable_type = nil asset.save end def _remove_all_assets Asset.filter(:attachable_id=>pk, :attachable_type=>'Post')\ .update(:attachable_id=>nil, :attachable_type=>nil) end end class Note < Sequel::Model one_to_many :assets, :key=>:attachable_id do |ds| ds.filter(:attachable_type=>'Note') end private def _add_asset(asset) asset.attachable_id = pk asset.attachable_type = 'Note' asset.save end def _remove_asset(asset) asset.attachable_id = nil asset.attachable_type = nil asset.save end def _remove_all_assets Asset.filter(:attachable_id=>pk, :attachable_type=>'Note')\ .update(:attachable_id=>nil, :attachable_type=>nil) end end @asset.attachable = @post @asset.attachable = @note
So far, we’ve only shown that Sequel::Model has associations as powerful as ActiveRecord’s. Now we will show how Sequel::Model’s associations are more powerful.
This can now be handled easily in Sequel using the :primary_key association option. However, this example shows how the association was possible before the introduction of that option.
Let’s say you have two tables, invoices and clients, where each client is associated with many invoices. However, instead of using the client’s primary key, the invoice is associated to the client by name (this is bad database design, but sometimes you have to play with the cards you are dealt).
class Client < Sequel::Model one_to_many :invoices, :reciprocal=>:client, \ :dataset=>proc{Invoice.filter(:client_name=>name)}, \ :eager_loader=>(proc do |key_hash, clients, associations| id_map = {} clients.each do |client| id_map[client.name] = client client.associations[:invoices] = [] end Invoice.filter(:client_name=>id_map.keys.sort).all do |inv| inv.associations[:client] = client = id_map[inv.client_name] client.associations[:invoices] << inv end end) private def _add_invoice(invoice) invoice.client_name = name invoice.save end def _remove_invoice(invoice) invoice.client_name = nil invoice.save end def _remove_all_invoices Invoice.filter(:client_name=>name).update(:client_name=>nil) end end class Invoice < Sequel::Model many_to_one :client, :key=>:client_name, \ :dataset=>proc{Client.filter(:name=>client_name)}, \ :eager_loader=>(proc do |key_hash, invoices, associations| id_map = key_hash[:client_name] invoices.each{|inv| inv.associations[:client] = nil} Client.filter(:name=>id_map.keys).all do |client| id_map[client.name].each{|inv| inv.associations[:client] = client} end end) private def _client=(client) self.client_name = (client.name if client) end end
Let’s say you have two tables that are associated with each other with multiple keys. For example:
# Both of these models have an album_id, number, and disc_number fields. # All FavoriteTracks have an associated track, but not all tracks have an # associated favorite track class Track < Sequel::Model many_to_one :favorite_track, \ :dataset=>(proc do FavoriteTrack.filter(:disc_number=>disc_number, :number=>number, :album_id=>album_id) end), \ :eager_loader=>(proc do |key_hash, tracks, associations| id_map = {} tracks.each do |t| t.associations[:favorite_track] = nil id_map[[t[:album_id], t[:disc_number], t[:number]]] = t end FavoriteTrack.filter([:album_id, :disc_number, :number]=>id_map.keys).all do |ft| if t = id_map[[ft[:album_id], ft[:disc_number], ft[:number]]] t.associations[:favorite_track] = ft end end end) end class FavoriteTrack < Sequel::Model many_to_one :track, \ :dataset=>(proc do Track.filter(:disc_number=>disc_number, :number=>number, :album_id=>album_id) end), \ :eager_loader=>(proc do |key_hash, ftracks, associations| id_map = {} ftracks.each{|ft| id_map[[ft[:album_id], ft[:disc_number], ft[:number]]] = ft} Track.filter([:album_id, :disc_number, :number]=>id_map.keys).all do |t| id_map[[t[:album_id], t[:disc_number], t[:number]]].associations[:track] = t end end) end
Let’s say you want to store a tree relationship in your database, it’s pretty simple:
class Node < Sequel::Model many_to_one :parent one_to_many :children, :key=>:parent_id end
You can easily get a node’s parent with node.parent, and a node’s children with node.children. You can even eager load the relationship up to a certain depth:
# Eager load three generations of generations of children for a given node Node.filter(:id=>1).eager(:children=>{:children=>:children}).all.first # Load parents and grandparents for a group of nodes Node.filter{|o| o.id < 10}.eager(:parent=>:parent).all
What if you want to get all ancestors up to the root node, or all descendents, without knowing the depth of the tree?
class Node < Sequel::Model many_to_one :ancestors, :eager_loader=>(proc do |key_hash, nodes, associations| # Handle cases where the root node has the same parent_id as primary_key # and also when it is NULL non_root_nodes = nodes.reject do |n| if [nil, n.pk].include?(n.parent_id) # Make sure root nodes have their parent association set to nil n.associations[:parent] = nil true else false end end unless non_root_nodes.empty? id_map = {} # Create an map of parent_ids to nodes that have that parent id non_root_nodes.each{|n| (id_map[n.parent_id] ||= []) << n} # Doesn't cause an infinte loop, because when only the root node # is left, this is not called. Node.filter(Node.primary_key=>id_map.keys).eager(:ancestors).all do |node| # Populate the parent association for each node id_map[node.pk].each{|n| n.associations[:parent] = node} end end end) many_to_one :descendants, :eager_loader=>(proc do |key_hash, nodes, associations| id_map = {} nodes.each do |n| # Initialize an empty array of child associations for each parent node n.associations[:children] = [] # Populate identity map of nodes id_map[n.pk] = n end # Doesn't cause an infinite loop, because the :eager_loader is not called # if no records are returned. Exclude id = parent_id to avoid infinite loop # if the root note is one of the returned records and it has parent_id = id # instead of parent_id = NULL. Node.filter(:parent_id=>id_map.keys).exclude(:id=>:parent_id).eager(:descendants).all do |node| # Get the parent from the identity map parent = id_map[node.parent_id] # Set the child's parent association to the parent node.associations[:parent] = parent # Add the child association to the array of children in the parent parent.associations[:children] << node end end) end
Let’s say you have a database, of songs, lyrics, and artists. Each song may or may not have a lyric (most songs are instrumental). The lyric can be associated to an artist in each of four ways: composer, arranger, vocalist, or lyricist. These may all be the same, or they could all be different, and none of them are required. The songs table has a lyric_id field to associate it to the lyric, and the lyric table has four fields to associate it to the artist (composer_id, arranger_id, vocalist_id, and lyricist_id).
What you want to do is get all songs for a given artist, ordered by the song’s name, with no duplicates?
class Artist < Sequel::Model one_to_many :songs, :order=>:songs__name, \ :dataset=>proc{Song.select(:songs.*).join(Lyric, :id=>:lyric_id, id=>[:composer_id, :arranger_id, :vocalist_id, :lyricist_id])}, \ :eager_loader=>(proc do |key_hash, records, associations| h = key_hash[:id] ids = h.keys records.each{|r| r.associations[:songs] = []} Song.select(:songs.*, :lyrics__composer_id, :lyrics__arranger_id, :lyrics__vocalist_id, :lyrics__lyricist_id)\ .join(Lyric, :id=>:lyric_id){{:composer_id=>ids, :arranger_id=>ids, :vocalist_id=>ids, :lyricist_id=>ids}.sql_or}\ .order(:songs__name).all do |song| [:composer_id, :arranger_id, :vocalist_id, :lyricist_id].each do |x| recs = h[song.values.delete(x)] recs.each{|r| r.associations[:songs] << song} if recs end end records.each{|r| r.associations[:songs].uniq!} end) end
In addition to getting associated records, you can use Sequel’s association support to get aggregate information for columns in associated tables (sums, averages, etc.).
Let’s say you have a database with projects and tickets. A project can have many tickets, and each ticket has a number of hours associated with it. You can use the association support to create a Project association that gives the sum of hours for all associated tickets.
class Project < Sequel::Model one_to_many :tickets many_to_one :ticket_hours, :read_only=>true, :key=>:id, :dataset=>proc{Ticket.filter(:project_id=>id).select{sum(hours).as(hours)}}, :eager_loader=>(proc do |kh, projects, a| projects.each{|p| p.associations[:ticket_hours] = nil} Ticket.filter(:project_id=>kh[:id].keys). group(:project_id). select{[project_id, sum(hours).as(hours)]}. all do |t| p = kh[:id][t.values.delete(:project_id)].first p.associations[:ticket_hours] = t end end) # The association method returns a Ticket object with a single aggregate # sum-of-hours value, but you want it to return an Integer/Float of just the # sum of hours, so you call super and return just the sum-of-hours value. # This works for both lazy loading and eager loading. def ticket_hours if s = super s[:hours] end end end class Ticket < Sequel::Model many_to_one :project end
Note that it is often better to use a sum cache instead of this approach. You can implement a sum cache using before or after save and delete hooks, or using a database trigger (the preferred method if you only have to support one database and that database supports triggers).