I have two tables like this:
Conversation
:id
:user_id # the user who started the conversation
Message
:id
:conversation_id # the conversation this message is part of
:user_id # the user who posted this particular message
:text
These are Sequel::Model classes, set up as you might expect
Conversation.one_to_many :messages
Message.many_to_one :conversation
Now, I'd like to do a query where I filter messages still grouped by
conversation. For example, let's say we have the following conversations:
Conversation 1:
- Message 1, User 1, text = "Hello world!"
- Message 2, User 2, text = "Goodbye."
Conversation 2:
- Message 3, User 3, text = "ET?"
- Message 4, User 1, text = "phone home"
So, it's pretty easy to get all the conversations with their messages
loaded:
Conversation.eager(:messages).all
But when it comes to actually searching I need to join and filter on
message columns. Going through something like:
Conversation.join(:messages, :conversation_id =>
:id).where(:conversations__user_id
=> 1)
^ the above sort of works, but it returns a kind of strange result which is
one "conversation" per matching message with the id and user_id from the
matching message clobbering the conversation ID. I understand this is
because of the actual tuples returned from the DB and the identical column
names...
So, disregarding the bit of weirdness above, I can get basically the data I
want just writing this out like:
result = DB["SELECT c.id as conv_id, m.id as msg_id, c.user_id as
conv_user_id, m.user_id as msg_user_id, m.text FROM conversations as c
INNER JOIN messages as m ON m.conversation_id = c.id WHERE c.user_id = ?
ORDER BY c.id", 1]
This gives me exactly the data I'm after, but in a flat array of hashes.
The data structure I need is actually nested like this:
[{conv_id: 1, messages: {msg_id: 1, ...}},{conv_id: 2, messages: {msg_id: 4
,...}}]
Now, I know I can loop through those hashes and build a nested structure,
so, I can get to the data structure I want from here so long as I just
stick with hashes. However, what I'd like to do is get the same result with
my actual ruby models, such that I would have a result like:
[<conversation: 1>, <conversation: 2>]
^ each being a Conversation instance, and each having it's `.messages`
method return just the messages that matched the filter.
I'm thinking maybe this sort of thing just isn't possible, but before I
gave up and start implementing a whole custom view, I figured it would be
smart to ask.
Any suggestions?
Thanks!
Andrew
--
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 https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
