On Tuesday, April 22, 2014 1:24:42 PM UTC-7, Ian Whitney wrote:
>
> I'm probably just missing something simple here, but after spending a
> bunch of time looking at the association_basics documentation and digging
> through StackOverflow, I still can't puzzle this one out.
>
> I have a table that I need to join onto itself. The join uses 3 keys.
>
> I've gotten the join to work with code like:
>
> DB[:ps_acad_prog___a].join(:ps_acad_prog___b, [[:a__emplid, :b__emplid],
> [:a__acad_career, :b__acad_career], [:a__stdnt_car_nbr,
> :b__stdnt_car_nbr]]).where("a.effdt <= ?", Time.now)
>
>
> But I'd like to move to using Sequel::Model. Something like
>
> PsAcadProg < Sequel::Model
> #magic join code
> end
>
> Obviously I'm stuck on that 'magic join code' line. Most of my attempts
> have looked something like:
>
> class PsAcadProg < Sequel::Model(:ps_acad_prog)
> many_to_many :acad_progs,
> :left_key => [:emplid, :acad_career, :stdnt_car_nbr],
> :right_key => [:emplid, :acad_career, :stdnt_car_nbr],
> :join_table => self
> end
>
> And the error I'm getting is:
>
> Sequel::Error: mismatched number of left keys: [:emplid, :acad_career,
> :stdnt_car_nbr] vs []
>
There are multiple issues here. First, are you sure you should be using
many_to_many? That implies a join table, when there doesn't seem to be a
join table in this case (there's only two tables in your dataset, not
three). I doubt you are using the same table as:
1) the current model table
2) the associated model table
3) and the join table.
Self referential many_to_many associations aren't rare (where 1) and 2) are
the same but 3) is different), but I've not come across the case where all
three tables are the same unless you are storing a tree and looking for
grandparent/grandchild relationships.
Assuming you did mean to use many_to_many, the number of primary key
columns needs to match the number of foreign key columns. You need to set
[:emplid, :acad_career, :stdnt_car_nbr] as the value of the
:left_primary_key and :right_primary_key association options. I'm assuming
those columns aren't the actual primary key for the model, otherwise the
join wouldn't make any sense. Also, the :join_table option value should be
a symbol, not a model class. Maybe table_name instead of self for that?
However, before you do anything else, you should ask yourself why you are
using an association at all. The purpose of associations is so you can do:
o = PsAcadProg.first
o.acad_progs # Get PsAcadProg instances related to this instance
It is not the purpose of associations just to hide JOIN conditions, so if
that is the only reason you are attempting to do it, you probably shouldn't.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
