StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORails, SQL: private chat, how to find last message in each conversation
text
Body
copied!<p>I'v got the folowing schema</p> <pre><code>+----+------+------+-----------+---------------------+--------+ | id | from | to | message | timestamp | readed | +----+------+------+-----------+---------------------+--------+ | 46 | 2 | 6 | 123 | 2013-11-19 19:12:19 | 0 | | 44 | 2 | 3 | 123 | 2013-11-19 19:12:12 | 0 | | 43 | 2 | 1 | ????????? | 2013-11-19 18:37:11 | 0 | | 42 | 1 | 2 | adf | 2013-11-19 18:37:05 | 0 | +----+------+------+-----------+---------------------+--------+ </code></pre> <p>from/to is the ID of the user's, message – obviously, the message, timestamp and read flag. When user open's his profile I want him to see the list of dialogs he participated with last message in this dialog. To find a conversation between 2 people I wrote this code, it's simple (Message model):</p> <pre><code>def self.conversation(from, to) where(from: [from, to], to: [from, to]) end </code></pre> <p>So, I can now sort the messages and get the last one. But it's not cool to fire a lot of queries for each dialog. How could I achieve the result I'm looking for with less queries?</p> <p><strong>UPDATE</strong>:</p> <p>Ok, looks like it's not really clear, what I'm trying to achieve. For example, 4 users – Kitty, Dandy, Beggy and Brucy used that chat. When Brucy entered in dialogs, she shall see</p> <pre><code>Beggy: hello brucy haw ar u! | <--- the last message from beggy ------- Dandy: Hi brucy! | <---- the last message from dandy -------- Kitty: Hi Kitty, my name is Brucy! | <–– this last message is from current user </code></pre> <p>So, three separated dialogs. Then, Brucy can enter anyone dialog to continue private conversation. And I can't figured out how could I fetch this records without firing a query for each dialog between users.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload