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.