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]
1 2 Joe
1 3 Bloggs
2 1 [email protected]
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`).
Is this something I'm going to have to dip into SQL for? I'd really like
to avoid it if possible, since I feel like I'm very close at the moment,
and can currently generate these datasets programatically which is the
ultimate aim: the legacy schema I'm dealing with is very, very abstract
and being able to generate models from it will make it much more useful.
(As ever, I can't modify the schema I'm dealing with.)
Thoughts appreciated.
Rob
--
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.
