On Nov 21, 8:44 am, Jesús Gabriel y Galán <[email protected]>
wrote:
> 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]
You can probably just use "primary key :gatherer_id" here
> String :name
> String :cost
> String :spell_type
> String :power_toughness
> String :rules
> String :sets
If any of these shouldn't be NULL, you might want to set :null=>false
> 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
I think :default=>1 would make sense for 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?
It looks fine if you aren't worried about concurrent access. For
concurrent access, there's a race condition when you are updating.
The correct way to handle concurrent access would be to do:
deck_card_dataset.filter(:gatherer_id =>
card.gatherer_id).update(:qty=>:qty+1)
> 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?
In your many_to_many :cards association, add
a :select=>[:mtg_database.*, :deck_card__qty]. Then you can just do
deck.cards, and each entry in the array would have a :qty entry in the
values hash for the number of that card in the deck.
> 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:
It should only affect mass assignment (which create uses). There's
nothing wrong with it, though you have to be more careful with user
input if you unrestrict access to the primary key.
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
