Thanks, that works perfectly!
One thing, though: if I want to use `where` on my model to filter, I
have to use e.g. `.where(:ed1__value => 'foo')` rather than being able
to use `.where(:first_name => 'foo')`. Obviously that's entirely logical
— the SQL doesn't know that `first_name == ed1.value` — but is
there any way to inform Sequel that that's the case?
I'm guessing there's some sort of mapping internally (generated from the
`select` method?) because I do get the `first_name`, `first_name=`,
`last_name`, `last_name=` methods on instances of my model as I'd
expect.
Many thanks for your patience!
Rob
On 15 Sep 2013, at 0:53, Jeremy Evans wrote:
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.
--
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.
