I have a many to many association in which the join table has
additional columns. I'm modeling a deck management application for
Magic the Gathering, and I have the following tables:

DB.create_table(:mtg_database) do
        Integer :gatherer_id
        primary_key [:gatherer_id]
        String :name
        String :cost
        String :spell_type
        String :power_toughness
        String :rules
        String :sets
end

DB.create_table(:deck) do
        primary_key :id
        String :name
        String :format
        String :description
end

DB.create_table(:deck_card) do
        foreign_key :deck_id, :deck
        foreign_key :gatherer_id, :mtg_database
        primary_key [:deck_id, :gatherer_id]
        Integer :qty
end

The deck_card table contains the relation between a deck and the cards
it contains. For each deck and card, it has an additional column named
qty which contains the number of such card a deck has. What I would
like to do is have a method in a deck model that allows me to add a
card to a deck, which will handle the qty field correctly (set it to 1
the first time a card is added to a deck, increment it from that point
on). I have managed to make it work, checking an old message from this
list related to books and locations, as so:

class Card < Sequel::Model(:mtg_database)
    unrestrict_primary_key
    one_to_many :deck_card
end

class DeckCard < Sequel::Model(:deck_card)
    many_to_one :card, :key => :gatherer_id
    many_to_one :deck
end

class Deck < Sequel::Model(:deck)
    many_to_many :cards, :join_table=>:deck_card, :right_key =>
:gatherer_id, :order=> :name
    one_to_many :deck_card

    def deck_card(card)
      deck_card_dataset[:gatherer_id => card.gatherer_id]
    end

    def add_card_to_deck card
        deck_card = deck_card card
        if deck_card
            deck_card.update({:qty => deck_card.qty + 1})
        else
            DeckCard.create({:card => card, :deck => self, :qty => 1})
        end
    end
end

This works and I'm able to add cards to decks like this:

deck = Deck.first(:id => some_id)
deck.add_card_to_deck(Card.first(:gatherer_id => some_card_id))

But I have some questions:

1.- Is this the best way to handle this?
2.- I would now need a way to iterate through all the deck's cards
having access to both the card info (all columns in mtg_database
table) along with the qty column from the join table. What would be
the best way to do that?
3.- I had to add the unrestrict_primary_key in the Card model. Until I
did, I wasn't able to create cards setting the gatherer_id, which is
an external id that I want to have as pk of that table. Am I doing
something wrong? I'm creating Cards like this:

    def update_or_create_card params
        card = Card.first :gatherer_id => params[:gatherer_id]
        card = Card.new unless card
        card.gatherer_id = params[:gatherer_id]
        card.name = params[:name]
        card.cost = params[:cost]
        card.spell_type = params[:type]
        card.power_toughness = params[:power_toughness]
        card.rules = params[:rules]
        card.sets = params[:sets]
        card.save
        card
    end

Regards,

Hi,

I have a many to many association in which the join table has
additional columns. I'm modeling a deck management application for
Magic the Gathering, and I have the following tables:

DB.create_table(:mtg_database) do
       Integer :gatherer_id
       primary_key [:gatherer_id]
       String :name
       String :cost
       String :spell_type
       String :power_toughness
       String :rules
       String :sets
end

DB.create_table(:deck) do
       primary_key :id
       String :name
       String :format
       String :description
end

DB.create_table(:deck_card) do
       foreign_key :deck_id, :deck
       foreign_key :gatherer_id, :mtg_database
       primary_key [:deck_id, :gatherer_id]
       Integer :qty
end

The deck_card table contains the relation between a deck and the cards
it contains. For each deck and card, it has an additional column named
qty which contains the number of such card a deck has. What I would
like to do is have a method in a deck model that allows me to add a
card to a deck, which will handle the qty field correctly (set it to 1
the first time a card is added to a deck, increment it from that point
on). I have managed to make it work, checking an old message from this
list related to books and locations, as so:

class Card < Sequel::Model(:mtg_database)
   unrestrict_primary_key
   one_to_many :deck_card
end

class DeckCard < Sequel::Model(:deck_card)
   many_to_one :card, :key => :gatherer_id
   many_to_one :deck
end

class Deck < Sequel::Model(:deck)
   many_to_many :cards, :join_table=>:deck_card, :right_key =>
:gatherer_id, :order=> :name
   one_to_many :deck_card
   def deck_card(card)
     deck_card_dataset[:gatherer_id => card.gatherer_id]
   end
   def add_card_to_deck card
       deck_card = deck_card card
       if deck_card
           deck_card.update({:qty => deck_card.qty + 1})
       else
           DeckCard.create({:card => card, :deck => self, :qty => 1})
       end
   end
end

This works and I'm able to add cards to decks like this:

deck = Deck.first(:id => some_id)
deck.add_card_to_deck(Card.first(:gatherer_id => some_card_id))

But I have some questions:
1.- Is this the best way to handle this?
2.- I would now need a way to iterate through all the deck's cards
having access to both the card info (all columns in mtg_database
table) along with the qty column from the join table. What would be
the best way to do that?
3.- I had to add the unrestrict_primary_key in the Card model. Until I
did, I wasn't able to create cards setting the gatherer_id, which is
an external id that I want to have as pk of that table. Am I doing
something wrong? I'm creating Cards like this:

   def update_or_create_card params
       card = Card.first :gatherer_id => params[:gatherer_id]
       card = Card.new unless card
       card.gatherer_id = params[:gatherer_id]
       card.name = params[:name]
       card.cost = params[:cost]
       card.spell_type = params[:type]
       card.power_toughness = params[:power_toughness]
       card.rules = params[:rules]
       card.sets = params[:sets]
       card.save
       card
   end

Regards,

  deck_card_dataset.filter(:gatherer_id =>
card.gatherer_id).update(:qty=>:qty+1)

def add_card_to_deck card
  deck_card = deck_card card
  if deck_card
    deck_card_dataset.filter(:gatherer_id =>
card.gatherer_id).update(:qty=>:qty+1)
  else
    DeckCard.create({:card => card, :deck => self, :qty => 1})
  end
end

Or am I misunderstanding and you meant a different thing?

Thanks a lot of for the help,

  Integer :gatherer_id, :primary_key=>true

  Model.create(hash){|m| m.primary_key = ...}