How to get the most recent relation item instead of a list in Android Room

Question

I have a structure as follows:

A Conversation that has one or more Messages. Conversation and Message are each in their own tables. I created a ConversationDetailed relation POJO merging the Conversation with a list of its messages.

public class ConversationDetailed {

    @Embedded
    public Conversation conversation;

    @Relation(
        parentColumn = "id",
        entityColumn = "conversation_id",
        entity = Message.class
    )
    public List<Message> messageList;

}

So far this works as expected and found no issues. Now I am trying to create a ConversationSummary relation where there is only the most recent message belonging to that conversation and I cannot find an example of how to do this anywhere. This is what I am trying to achieve:

public class ConversationSummary {

    @Embedded
    public Conversation conversation;

    @Relation(
        parentColumn = "id",
        entityColumn = "conversation_id",
        entity = Message.class
    )
    public Message recentMessage; // <- the most recent message of this conversation

}

I know I can use the ConversationDetailed and just pick the most recent message from the list, but that means querying from the database a lot of messages without any need, when all I want is just the most recent one.

How can I achieve this using a Relation POJO like the example above?

EDIT: After trying the initial answer suggested by another user I discovered that relations are separate queries, that is the reason why queries with Relation POJOs use Transaction, so the question remains unanswered. Is there a way to specify the order of the relation item?

EDIT2: After a detailed explanation by the same user that provided the first answer I was finally able to make this work. Here's the rationale that worked for my case.

A combination of MAX with JOIN allows me to select the select the conversation with the most recent message out of all, including out of all conversations unrelated to that message. Adding GROUP BY for the conversation id split the above logic to get the MAX for each unique conversation id. After that it was a matter of applying the ORDER BY to the message creation date.

@Query(
    "SELECT " +
    "*, MAX(Message.date)" +
    "FROM Conversation " +
    "JOIN Message " +
    "ON Conversation.id = Message.conversation_id " +
    "GROUP BY Conversation.id " +
    "ORDER BY Message.date DESC "
)
abstract List<ConversationSummary> getSummaryList();

although the provided solution in your edits might provide a working answer, my limited but growing understanding is that you are better off going with two queries here and then mapping the results in code — Sameer J, Sep 16 '21 at 20:40
@SameerJ could you help improve the answers by providing the one you're suggesting so others like myself know what you are trying to explain in your comment? Specifically the part about mapping the results returned by two different queries. — Shadow, Sep 20 '21 at 14:10

score 1 · Accepted Answer · answered Aug 09 '20 at 13:21

1

Try next workaround (since I don't know straight method to do what you want only with Relations):

Write a query to get all recent messages within conversation. Let's say you have some field posted that you can use to define the message was recent (in other words you should find detailed conversation with maximal posted).
Use Room Relations to attach Conversation entity to query's result.

So, additional class (query should return List of that Class values):

public class RecentMessage {

    @Embedded
    public Message recentMessage;

    @Relation(
        parentColumn = "conversation_id",
        entityColumn = "id"
    )
    public Conversation conversation; 

}

And query:

SELECT * FROM conversation_detailed as table1 JOIN (select conversation_id, max(posted) as posted from conversation_detailed) as table2 on table1.conversation_id=table2.conversation_id and table1.posted = table2.posted

answered Aug 09 '20 at 13:21

sergiy tikhonov

4,961
1
10
27

I'd like to avoid custom queries, but to be honest I cannot see any other way of doing this without the use of a custom query like the one you suggested. I'll give it a try to see if it works. – Shadow Aug 10 '20 at 13:36
Your suggestion works correctly, but I had to adjust for my use cases since they vary from the example. However, I still want to test another method in case it works, otherwise I will mark your answer as accepted. Thanks! – Shadow Aug 11 '20 at 00:32
Seems like my other option is tied to the Paging library, which deviates a lot from this question so yours is the best solution - and probably the right one - so far and marking it as such. – Shadow Aug 11 '20 at 01:10
Looks like this was not the answer after all, the recentMessage the relation is returning completely ignores the query order and instead returns the latest entry which does not always mean it is the most recent message. Same issue pointed here: https://stackoverflow.com/questions/61159504/android-room-relation-left-join – Shadow Aug 12 '20 at 02:32
I am aware that Room's relations ignore subquery's order. That's why in my answer I haven't relied on that. Custom query's goal in the answer - is to get only messages with maximal field `posted` attached to each `conversation id`. So custom query should return only recent messages to each `conversation_id` (that's why operator `max` and INNER JOIN table to itself were used). But to attach `Conversation` object instead of `conversation_id` to result class - Relation was used. In this case it would be one-to-one relation and there would be no order issue there. May be I misunderstood you. – sergiy tikhonov Aug 12 '20 at 08:04
So I shouldn't use the Relation as it is, but instead work around the issue by making use of a field from the subquery to hint the Relation into using that to fetch the most recent message. That's very interesting, I will try it right now. Sorry for posting my answer, but I did not get the notification with your reply before posting it. Let me see if your idea works. – Shadow Aug 12 '20 at 13:09

score 0 · Answer 2 · answered Aug 12 '20 at 13:06

It seems that @Relation has never been designed to take into account sorting from custom @Query. This has been confirmed in the issue related to this problem located here: https://issuetracker.google.com/issues/128470264

The second best alternative is the to use multiple queries and build the returned data structure manually, as per the suggestion in the linked issue.

For my case it will be a JOIN between both tables, grouped by the conversation id and ordered by newest message first.

score 0 · Answer 3 · answered Mar 17 '22 at 23:35

Wanted to share an alternative solution, one I applied to my own same issue. You could instead save a reference to the latest Message in Conversation, making sure to write that correctly, and then use that in your Relation. For example, you could add "recent_message_id" to Conversation, and then use that in relation to "message_id", targeting that specific message. This assumes there's a "message_id" in Message, of course.

public class ConversationSummary {

    @Embedded
    public Conversation conversation;

    @Relation(
        parentColumn = "recent_message_id",
        entityColumn = "message_id",
        entity = Message.class
    )
    public Message recentMessage;
}

How to get the most recent relation item instead of a list in Android Room

3 Answers3