Sylar Sylar - 11 months ago 46
SQL Question

Show last message in a message array

Regarding my last attempted answer, I could achieve what I wanted. Once the app is working I noticed something is different: only the person that created the message and sends a message was only showing. If I reply, I do not see the reply and I know the problem.

My Message model has tables:

[:id, :user_id, :to, :body, ...]

would be similar to
as that is used to determine who created the message.

User 1
would be Client and
User 2
is Student. Student sends a message to Client. Only my client side the code is as this:

#Controller Index:

@messages = Message.where(to: # My replies are not included here
.order(user_id: :asc, created_at: :desc)
.select('distinct on (user_id) *')

I'm saying give me all messages that's addressed to me (
) an id of

I won't see my reply because of the

My (client) reply looks like this:

to: #student id
user_id: #client id

And student message:

to: #client id
user_id: #student id

So because the
now point to an id, let's say
I wont see the reply. I may need to change up the model but if you see an easy way, please let me know.


Answer that works great:

@messages = Message.where(to:
.order(connection: :desc, created_at: :desc)
.select('distinct on (connection) *')

Answer Source

Since you are using Rails 5, you can take advantage of it's or method:

).order(connection: :desc, created_at: :desc)
 .select('distinct on (connection) *')