Hi Jeremy, thanks for the reply. You're right that many_to_many is probably wrong. I had been working with one_to_many, but I couldn't get that to work either. So I was trying many_to_many when I wrote my post.
The table I'm working with here stores all previous versions of the row. So there will be multiple rows with the same emplid, acad_career, stdnt_car_number. But they will have different effective dates and effective sequences. (This is PeopleSoft data, btw.) My eventual goal here is to get the rows with most recent effective date and effective sequence. In straight SQL, that ends up looking like this https://gist.github.com/IanWhitney/7ef1335591466e5b1dcf I'm clearly going about this wrong, though. If you have ideas, they would be much appreciated. On Tuesday, April 22, 2014 3:57:57 PM UTC-5, Jeremy Evans wrote: > > 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.
