On Apr 28, 3:44 pm, "[email protected]" <[email protected]> wrote:
> Hi there, I'm not a SQL expert and it the first time that I play with
> sequel, so maybe I'm missing the point.
> I wanted to test many_to_many association using a join table with
> attributes.
> There is my sample with comments.
>
> require 'sequel'
> #
> LOGGER = Object.new()
> def LOGGER.method_missing( name, args )
> puts "[#{name}] #{args}"
> end
> DB = Sequel.sqlite('sql_test.db', :loggers => [LOGGER] )
> #
> Sequel::Model.plugin(:schema)
> #
> class T1 < Sequel::Model
> set_schema do
> primary_key :id, :auto_increment => true
> text :a1
> end
> one_to_many :tjoin
That should probably be:
one_to_many :tjoins
> many_to_many :t2s, :join_table=>:tjoins
If you want to use the :ajoin field from the join tables:
many_to_many :t2s, :join_table=>:tjoins, :select=>
[:t2s.*, :tjoins__ajoin]
> end
> #
> class T2 < Sequel::Model
> set_schema do
> primary_key :id, :auto_increment => true
> text :a2
> end
> one_to_many :tjoin
> many_to_many :t1s, :join_table=>:tjoins
> end
> #
> class Tjoin < Sequel::Model
> set_schema do
> foreign_key :t1_id, :table => :t1s, :null => false
> foreign_key :t2_id, :table => :t2s, :null => false
> text :ajoin #, :null => false I would love to be able to use
> it *sigh*
You should be able to use this now.
> primary_key [:t1_id,:t2_id]
> end
> many_to_one :t1
> many_to_one :t2
> end
> #
> T1.create_table!
> T2.create_table!
> Tjoin.create_table!
> # why is there a (SELECT ... LIMIT 1) statement executed after each
> INSERT, UPDATE, ETC
> t1 = T1.create(:a1=>'A')
> t2 = T2.create(:a2=>'B')
The SELECT and UPDATES are to get the values for the record just
inserted. It does this to get a complete record, since many values
may be filled in by database defaults. Sequel refreshes the record
after creation, but not after updates.
> t1.add_t2 t2
> # isn't there a better way to do that ??
> Tjoin.where(:t1_id=>t1.id,:t2_id=>t2.id).first.update(:ajoin=>'JOIN')
Sure:
class T1
def _add_t2(t2)
DB[:tjoin].insert(:t1_id=>id, :t2_id=>t2.id, :ajoin=>'JOIN')
end
end
> t3 = T2.create(:a2=>'C')
> t1.my_add t3, 'MY_ADD'
That you currently can't do easily. You could add an attr_accessor to
T2 that held the variable you wanted to use where _add_t2 can use it
later. Really, the solution would be to allow extra arguments from
the default add_association methods to be passed directly to the
_add_association methods, so you could do:
t1.add_t2(t2, 'MY_ADD')
> # isn't ther a way to produce something like this
> puts DB[:tjoins,:t1s].select(:tjoins.*).where
> (:tjoins__t1_id=>:t1s__id).and(:t1s__a1=>'A').sql
> # using Sequel::Model ???
> T1.where(:a1=>'A').first.tjoin_dataset.each do |set| puts set end
You could use eager graphing to do something similar:
T1.eager_graph(:tjoins).where(:t1s__a1=>'A').select(:tjoins.*).all
Hope this helps. If not, please ask more questions.
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
-~----------~----~----~----~------~----~------~--~---
