Sequel Models many_to_many / many_to_many

In our last post yesterday, we discussed Sequel one_to_many/many_to_one models. In this post we'll take a look at many_to_many / many_to_many models. You would use this in cases (as we will here) where an author can have many books and a book can have many authors. Another example is in a school example where a teacher would have many students and a student would have many teachers (at least past elementary school here in the U.S. YMMV). Here's some code for a book/author example:

require 'rubygems'
require 'sequel'

DB = Sequel.sqlite # Create an in-memory database

# Create the books table with a title only.
DB.create_table :books do 
    primary_key :id
    column :title, :text, :unique=>true
end

# Create the authors table with the author's name.
DB.create_table :authors do
    primary_key :id
    column :name, :name
end

# Create the table where we'll cross reference authors and books. This
# has foreign keys going both directions and these reference both the 
# ID and the table name.
DB.create_table :authors_books do
    primary_key :id
    foreign_key :book_id, :books
    foreign_key :author_id, :authors
end

# Create the Book model. Note the plural in the 
# many_to_many "authors".
class Book < Sequel::Model
    many_to_many :authors
end

# Create the Author model. Note the plural in the 
# many_to_many "books".
class Author < Sequel::Model 
    many_to_many :books
end

# Is this needed? No it's not, but it won't hurt anything either.
# class Author_Book < Sequel::Model; end

# Create a couple of books (odd that we'd use C/Unix books and not Ruby, but at
# least it's not Rails ;-)) ...
cProgrammingLanguage = Book.create(:title => 'The C Programming Language')
unixProgrammingEnvironment = Book.create(:title => 'Unix Programming Environment')

# and some authors
kernighan = Author.create(:name => 'Brian Kernighan')
ritchie = Author.create(:name => 'Dennis Ritchie')
pike = Author.create(:name => 'Rob Pike')

# We only need to do one of these (add authors to books) ...
cProgrammingLanguage.add_author(kernighan)
cProgrammingLanguage.add_author(ritchie)
unixProgrammingEnvironment.add_author(kernighan)
unixProgrammingEnvironment.add_author(pike)

# or these (add books to authors). If you do both, they will appear twice below.
#kernighan.add_book(cProgrammingLanguage)
#ritchie.add_book(cProgrammingLanguage)
#kernighan.add_book(unixProgrammingEnvironment)
#pike.add_book(unixProgrammingEnvironment)

# We can get either a dataset ...
cProgrammingLanguageAuthors = cProgrammingLanguage.authors_dataset
cProgrammingLanguageAuthors.each {|author| puts "C Programming Language Author: #{author[:name]}"}
brianKernighanBooks = kernighan.books_dataset
brianKernighanBooks.each {|book| puts "Brian Kernighan Books: #{book[:title]}"}

# or arrays.
authors = cProgrammingLanguage.authors
authors.each {|author| puts "C Programming Language Author: #{author[:name]}"}

books = kernighan.books
books.each {|book| puts "Brian Kernighan Books: #{book[:title]}"}

We start out creating our database, once again just using an in-memory database rather than persisting it. We then create book and author tables with title and name respectively. Finally, we create a cross reference table that links books to authors and vice versa. Here the name of the table must be the name of the two tables alphabetically listed with an underscore between. It will have foreign keys for both books and authors using the id and the name of the table (as Jeremy Evans from Sequel-Talk noted, the table name isn't strictly necessary for sqlite, but will be for other databases, so go ahead and put them in. I didn't in yesterday's example). After the tables are created, we create the models for the books and authors (note the singular in the class name compared to the plural in the table name). In these, we put the many_to_many tag to link the two. We can also create a model for hte Author_Book, but it's not necessary and here I've shown it but left it commented out.

Now we can go ahead and start using the models. We'll create a couple of books (classics I might add), the C Programming Language and the Unix Programming Environment). Then we create the authors for these books, Brian Kernighan, Dennis Ritchie, and Rob Pike. Finally, we add the authors to the books. Note we could have just as easily added the books to the authors to the same end. Now, we can get the books for each author or the authors for each book either as a dataset (which means we can filter using the techniques from our first post on Sequel or just as an array.

Once again thanks to the Sequel-Talk group. Let me know if you have questions or comments on anything here.