On Saturday, September 14, 2013 11:42:43 PM UTC, Rob Miller wrote: > > Hello, > > I've got a database table that's in > EAV<https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model>format. > > So, it might have three fields, stored as: > > entry_id field_number value > 1 1 [email protected] <javascript:> > 1 2 Joe > 1 3 Bloggs > 2 1 [email protected] <javascript:> > 2 2 Bob > 2 3 Smith > > So, to get the first name, last name, and email for each entry, it's > necessary to join the table on itself three times, each with the same > entry_id constraint but a different field_number. > > That's fine in SQL: > > SELECT ed1.value first_name, ed2.value last_name, ed3.value email > FROM entries e > JOIN entry_detail ed1 ON e.id = ed1.entry_id AND ed1.field_number = 1 > JOIN entry_detail ed2 ON e.id = ed2.entry_id AND ed2.field_number = 2 > JOIN entry_detail ed3 ON e.id = ed3.entry_id AND ed3.field_number = 3 > > …but how do I model this in Sequel? If I try to use Dataset#join, I get a > complaint that the table/alias is not unique (since I can't do the ed1, > ed2, ed3 aliases, or can't see how to anyway). Additionally, I can't seem > to control the aliases in the SELECT (I really need them to be named, > whereas now the value is selected as value). > Something like the following should work:
ds = DB[:entries___e]. join(:entry_detail___ed1, :e__id=>:ed1__entry_id, :ed1_field_number=>1). join(:entry_detail___ed2, :e__id=>:ed2__entry_id, :ed2_field_number=>1). join(:entry_detail___ed3, :e__id=>:ed3__entry_id, :ed3_field_number=>1). select(:ed1__value___first_name, :ed2__value___second_name, :ed3__value___email) class Entry < Sequel::Model(ds) end If you want to save entries, you'll need to implement your own custom saving routines. 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/groups/opt_out.
