Sequel Models many_to_one / one_to_many

In our last post, we discussed simple Sequel models. In this post, we'll talk about and show some more complex models using a many_to_one / one_to_many relationship. Here, we're going to revisit the countries we used earlier and add cities to the countries. In this case a country can have many cities, but a city will belong to only a single country. Other examples of this many_to_one / one_to_many relationship are customers and orders (a customer might have many orders, but an order will belong to only one customer) or car models and car makers (a car maker will make many models, but each model will belong to a single car maker). Here's some code illustrating the one_to_many / many_to_one relationship using countries and cities and sequel models.

require 'rubygems'
require 'sequel'

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

# Create a new Country table with columns of
# id, name, and population.
DB.create_table :countries do 
    primary_key :id
    column :name, :text, :unique=>true
    column :population, :integer
end

# Create a cities table that links to the 
# country with a foreign_key
DB.create_table :cities do
    primary_key :id
    column :name, :text
    foreign_key :country_id
end

# Create a Country model stating that we'll have many cities.
class Country < Sequel::Model; 
    one_to_many :cities
end

# Create a City model stating that a city will belong to one country.
class City < Sequel::Model
    many_to_one :country
end

# Create the USA country.
usa = Country.create(:name => 'U.S.A.', :population => 250000000)

# Create two cities, NY and LA.
ny = City.create(:name => 'New York')
la = City.create(:name => 'Los Angeles')

# Add the cities to the USA.
usa.add_city(ny)
usa.add_city(la)

# Get all of the cities in the US as a dataset. If we do this then
# we have options for filtering.
usaCities = usa.cities_dataset

# Put out each city from the dataset.
usaCities.each{|city| puts "City #{city[:name]}" }

# Or it can be done as an array.
usa.cities.each{|city| puts "City #{city[:name]}" }

In this example, unlike our last one, we use an in-memory database rather than one that's permanent. Feel free to make this change if you like. Next we create our two tables, countries (the same as in our previous post) and a new one cities. The cities table has the primary_key :id, the name of the city, and a foreign_key, :country_id. This foreign key will point back to the :id of the countries table (all of this will be managed behind the scenes by Sequel). Next we'll construct models to match the tables. These should be named singular to the model's plural versions (countries/Country and cities/City). In the model, we need to add the one_to_many :cities in the Country model to let Sequel know that this relationship exists (one country / many cities). Also in the City model, we'll add many_to_one :country to express the opposite relationship. After this, we can go ahead and start using the models. Here, we've created a country, the USA, with two cities, New York and LA. Then we add the cities to the country. Finally, we can get the data back out again either as a dataset (which we looked at a bit in our last post) or as an array.

Next post we'll take a look at the many_to_many relationships. For now though, if you have questions, leave them in the comments.

Finally, I'd like to thank the folks on sequel-talk for helping me understand some of this and also the many_to_many relationships also. If you're at all interested in Sequel, I'd strongly recommend signing up, listening in, and learning from everyone there.