Hi jeremy,
thanks a lot, everything is much more clear.
On Apr 29, 3:30 am, Jeremy Evans <[email protected]> wrote:
> 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.
That's exactly what I thought, and DB[:t1s].insert(:a1=>'C') doesn't
generate any SELECT because there's no Model to keep up to date,
nice !
>
> > 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')
I first tried to override _add_t2 to set join attributes, but yes
there's no aditional parameters. It could be a nice feature to have.
I the mean time I stick to
class T1
def _add_t2 t2
Tjoin.insert(:t1_id=>id, :t2_id=>t2.id, :ajoin=>'DEFAULT')
end
def my_add t2, attr
Tjoin.insert(:t1_id=>id, :t2_id=>t2.id, :ajoin=>attr)
end
end
>
> > # 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
DB[:tjoins,:t1s].select(:tjoins.*).where
(:tjoins__t1_id=>:t1s__id).and(:t1s__a1=>'A').all
works fine, but using
T1.eager_graph(:tjoins).where(:t1s__a1=>'A').select(:tjoins.*).all
generates a : undefined method `pk' for nil:NilClass
but the generated sql seems OK :
SELECT `tjoins`.* FROM `t1s` LEFT OUTER JOIN `tjoins` ON
(`tjoins`.`t1_id` = `t1s`.`id`) WHERE (`t1s`.`a1` = 'A')
I'm searching
Thanks again
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
