Wednesday, October 7, 2009

Sequel Models many_to_one / one_to_many (Revisited)

I was working on a post to show how to user Ramaze and Sequel to issue challenge questions if a user forgets his password. I was using a one_to_many / many_to_one relationship between users and challenge questions (each user will have a single challenge question and each challenge question could have many users). In the past when I've used this relationship, I've added the one to the many using the add_X() method. For example in some library code I was writing I had the concept of locations that could have copies of books. Each location could have many copies, but each copy would have only a single location. I would use something along the lines of location.add_copy(copy) when I created a new copy of a book. In the user/challenge question though it made more sense (to me anyway) to add the challenge question to the user. I first tried user.add_challenge_question(challenge_question) and that failed. I finally emailed the Sequel list and when you're adding that direction, you need to just use an "=". So you end up with user.challenge_question = challenge_question.

The other issue I had was the naming of the challenge question model. In this case, I had named the database table "challenge_questions". I thought then that the model should be Challenge_Question, but apparently it should be ChallengeQuestion. I vaguely recall seeing this somewhere (Rails perhaps?), but thought I'd get it written down here and hopefully save someone else the trouble.

Here's some sample code:

require 'rubygems'
require 'sequel'

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

# Create the users table that will contain a user name and
# a foreign key to the challenge question for this user.
DB.create_table(:users) do
primary_key :id
String :user_name
foreign_key :challenge_question_id, :challenge_questions

# Create a table for the challenge questions.
DB.create_table(:challenge_questions) do
primary_key :id
String :question

# Create the User model. This is many to one with the
# challenge_questions table. So ... many users can have
# one challenge_question. Note: challenge_question is
# singular.
class User < Sequel::Model
many_to_one :challenge_question

# Create the Challenge Question model. This is one to many
# with the users table. So ... one challenge_question can have
# many users. Note: users is plural. Also note that even though
# the table had an "_" (underscore), the model does not.
class ChallengeQuestion < Sequel::Model
one_to_many :users

# First we'll create a user and a challenge question then
# add the challenge question to the user. Since user is many to one
# with the question, we can just use the "=" (equal) sign.
u1 = User.create(:user_name => 'A User')
q1 = ChallengeQuestion.create(:question => 'Where?')
u1.challenge_question = q1
puts "User: #{u1.user_name} Question: #{u1.challenge_question.question}"

# First we'll create a user and a challenge question then
# add the user to the challenge question . Since challenge_question is one to many`
# with the user, we use the add_user() method.
u2 = User.create(:user_name => 'Another User')
q2 = ChallengeQuestion.create(:question => 'Who?')
puts "User: #{u2.user_name} Question: #{u2.challenge_question.question}"

# Add another user and show that we can use the add_user() method to
# add them to the second question.
u3 = User.create(:user_name => 'Yet Another User')
puts "User: #{u3.user_name} Question: #{u3.challenge_question.question}"

The code explains pretty well what's going on and with the notes above, you should be fine.

Let me know if you have any questions or comments.


  1. Hi. Can You show how to make in Sequel tree associations. i'm trying to make litile blog on Ramaze with tree comments.

  2. one_1, I'm afraid that's a bit beyond my capabilities right now. If I get a chance, I'll try to take a look at it though. I'd try asking on the Sequel mail list. There's a ton of smart helpful people there.

  3. Hi Scott,

    I followed your example above, and when we do like this:

    u1 = User.create(:user_name => 'A User')
    q1 = ChallengeQuestion.create(:question => 'Where?')
    u1.challenge_question = q1

    When I query users table, this user does not have a foreign key value from challenge_questions table. However, the other two users get the challenge_question_id populated.

    Were you aware of this? If so, what's your purpose to do this without the foreign key value in the table. I am curious.

    Thanks for your good explaining of the example.


  4. Hi Scott,

    If I add after setting q1 to u1.challenge_question as below, it will save to the database. I am not sure if it's a bug or working as design in Sequel. I emailed Jeremy and asked him about it. Will keep you update.

    u1 = User.create(:user_name => 'A User')
    q1 = ChallengeQuestion.create(:question => 'Where?')
    u1.challenge_question = q1


  5. Hi Scott,

    When I added, it worked so I think it works as design since create or add_* method will automatically save to the database. That's well document in Sequel doc.