On Thu, Nov 24, 2011 at 3:08 AM, Jeremy Evans <[email protected]> wrote:
> 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
I understood this would create an autoincrementing column, whereas I
want to set the id myself, because it comes from an external system.
>> 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
Thanks for the tip. I guess name and spell type shouldn't be null.
>
>> 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.
True.
>
>> 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)
OK, you mean to do this in the else part of the check right?
I mean I need to do different things for the first insertion and the
subsequent ones,
and I understand this statement needs that the row in the deck_card
table already exists, correct?
If so, wouldn't there still be a race condition?
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?
>> 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.
Great, thanks, I'll try this.
>
>> 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.
OK, but my question is why is that needed when the PK is not an
autoincrementing id?
If I need to build a model in which I need to setup the id myself when
creating, as is my case, what should I do?
Is this the recommended way? Until I add that line I was getting
errors when I tried to set the gatherer_id in a Card object.
Thanks a lot of for the help,
Jesus.
--
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.
