Sequel accessing many_to_many join table when adding association

Question

I'm building a wishlist system using Sequel. I have a wishlists and items table and an items_wishlists join table (that name is what sequel chose). The items_wishlists table also has an extra column for a facebook id (so I can store opengraph actions), which is a NOT NULL column.

I also have Wishlist and Item models with the sequel many_to_many association set up. The Wishlist class also has the :select option of the many_to_many association set to select: [:items.*, :items_wishlists__facebook_action_id].

Is there a way that I can add in extra data when creating the association, like wishlist.add_item my_item, facebook_action_id: 'xxxx' or something? I can't do it after I create the association as the facebook id is has NOT NULL on the column.

Thanks for any help

Answer 1

The recommended way to do this is to add a model for the join table. However, if you don't want to do that, you can do:

class Wishlist
  def _add_item(item, hash={})
    model.db[:items_wishlists].insert(hash.merge(:item_id=>item.id, :wishlist_id=>id))
  end
end

Answer 2

I think there is also another possibility.

First a MWE (minimal working example) of your question:

  require 'sequel'
  Sequel.extension :pretty_table  #Sequel::PrettyTable.print()/Sequel::PrettyTable.string()

  DB = Sequel.sqlite
  DB.create_table(:wishlists){
        primary_key :id
        String :listname
  }
  DB.create_table(:items){
        primary_key :id
        String :descr
    }
  DB.create_table(:items_wishlists){
        primary_key :id
        foreign_key :wishlist_id, :wishlists
        foreign_key :item_id, :items

        add_column :facebook_id, type: :nvarchar
  }

  class Item < Sequel::Model
    many_to_many :wishlists 
  end

  class Wishlist < Sequel::Model
    many_to_many :items
  end

  w1 = Wishlist.create(listname: 'list1')
  w1.add_item(Item.create(descr: 'item 1'))
  #w1.add_item(Item.create(descr: 'item 2'), facebook_id: 'fb2')  ##<- This does not work

  #Sequel::PrettyTable.print(Wishlist)
  #Sequel::PrettyTable.print(Item)
  Sequel::PrettyTable.print(DB[:items_wishlists])

To allow ad_itemwith a parameter (Wishlist#add_item(Item.create(descr: 'item 2'), facebook_id: 'fb2')) you must define an adder as in this example:

require 'sequel'
Sequel.extension :pretty_table  
Sequel::PrettyTable.print()/Sequel::PrettyTable.string()

DB = Sequel.sqlite
DB.create_table(:wishlists){
      primary_key :id
      String :listname
}
DB.create_table(:items){
      primary_key :id
      String :descr
  }
DB.create_table(:items_wishlists){
      primary_key :id
      foreign_key :wishlist_id, :wishlists
      foreign_key :item_id, :items

      add_column :facebook_id, type: :nvarchar
}

class Item < Sequel::Model
  #~ many_to_many :wishlists 
end

class Wishlist < Sequel::Model
  many_to_many :items, 
                join_table: :items_wishlists, class: Item,
                left_key: :wishlist_id, right_key: :item_id, 
                adder: (lambda do |item, facebook_id: nil|
                      self.db[:items_wishlists].insert(wishlist_id: self.id, item_id: item.id, facebook_id: facebook_id)
                end)
end

w1 = Wishlist.create(listname: 'list1')
w1.add_item(Item.create(descr: 'item 1'))
w1.add_item(Item.create(descr: 'item 2'), facebook_id: 'fb2')

Sequel::PrettyTable.print(DB[:items_wishlists])

The result:

+-----------+--+-------+-----------+
|facebook_id|id|item_id|wishlist_id|
+-----------+--+-------+-----------+
|           | 1|      1|          1|
|fb2        | 2|      2|          1|
+-----------+--+-------+-----------+

But the next problem will come later:

With w1.items you get the list of items, but you have no access to the parameters. (at least I found no way up to now. I'm still researching, but I expect, that I need a model of the join table for this (see Jeremys recommendation))