Tuesday, March 10, 2009

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

# 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

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

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

# 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.

# 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.


  1. I'm trying to do something similar; the same associations but with Person and Town objects (many persons belong to one town and one town belongs to many persons), but I get an error when I try to create a person, saynig that the fname= method doesn't exist or is restricted. Perhaps you could shed some insight?

    The code is here: http://gist.github.com/142252

  2. Max, Try changing "persons" to "people" through your code and that should work or at least it did for me. Let me know if that doesn't help. I gave it a try and it looked OK for me (at least I didn't get the fname error, which I was getting before the change).

  3. That looks like it did the trick. Thanks Scott. Seems like a weird fix for a weird bug, though. Maybe I'm missing something... after all, persons is a plural of person (as is people), but I don't know if that was the reason behind the code not interpreting. Or was it?

  4. Max, I'm guessing it was the issue. Sequel probably only "knows" one plural per word. If it were a word that it didn't know, fluggurp say, then it would be quite happy adding an "s" to the end to get fluggurps. You should check on the Sequel mail list though. Jeremy would know best and he answers quickly.

  5. Sequel-Talk? I'll take a look, I've been visiting that site for a little while now. That's where I got the link to your blog from. It's been pretty confusing trying to learn Sequel without knowing much SQL, since most of the examples seem to assume you know what you're doing. That on top of different classes and methods getting deprecated rather quickly doesn't help either. Oh well. I'm not discouraged =]. Thanks for the help Scott

  6. Max, Yes, Sequel-Talk. I don't know much (OK, really no) SQL either, but they have been reallyh helpful with questions about both Sequel and SQL. I even accidently sent a Ramaze question to them and got some answers. As far as classes/methods getting deprecated, I generally have only been using the most basic things in Sequel, so it hasn't been a problem. I guess I'll worry about it if I ever get to the point where I'm a "power" user. Not much chance of that though ;-).

  7. I believe migrations are deprecated in Sequel, so some of your examples from a few months ago don't work the way they should (I haven't tested them out but the documentation says they are deprecated).

  8. Max, Can you point me towards that documentation? I'd pretty disappointed if migrations we're taken away. Is there a replacement? One thing is the Class.new way of doing migrations (see http://steamcode.blogspot.com/2009/05/temaplates-sequel-and-ramaze.html). Is that what you mean?

  9. http://sequel.rubyforge.org/rdoc/classes/Sequel/Migration.html

    Odds are I'm mistaken, but I was referring to how the file's path is "lib/sequel/deprecated_migration.rb" (at the top). Also, it doesn't look like their are too many methods there. But, I'm very new to migrations and web apps in general, so odds are I am incorrect. Perhaps you could enlighten me?

  10. Max, I think (note think not know) that it's just been moved to be an extension. See here:
    http://sequel.rubyforge.org/rdoc-plugins/index.html. I'll ask on sequel-talk though to double check.

  11. Max, I checked with sequel-talk and that is the explanation. Migrations have been moved and that's the explanation for the deprecation. I think (given their simplicity) that my older examples should work, but if you find something that doesn't, let me know and I'll try to fix them.

  12. Alright sounds good. What would I "require" (at the top) to use migrations now? 'sequel/pagination'?

  13. Max, I'm pretty sure that the migrations as I laid them out in http://steamcode.blogspot.com/2009/03/sequel-migrations.html should work fine. Let me know if they don't though.