For saving conversation between two person, I created table MessageBox, which has the following columns.
from => id of one who first requested to become friends (String)
to => id of one who accepted friend request (String)
message => Data Relation (1:N): Message
=> Message table contains sid(sender id which is either equal to from or to), text (text body)
Is it possible to retrieve all my MessageBox with last Message in a single query? (single step retrieval)
I know that to retrieve all my MessageBox where clause should be from = ‘myId’ OR to = 'myId", but I have no idea how to retrieve last created object of relation.
If possible can you provide example code?
The purpose of above code is to retrieve latest message for each conversation (friend) and show it to message list view before going into chat room.
If the Message table already contains sid and the text and then then it seems like the MessageBox table is an overkill. Wouldn’t the schema be simpler if the Message table gets “from” and “to” and you get rid of the MessageBox table?
Now to answer your question, what you could do is request sorting by the “created” column using the QueryOptions object (assuming you’re using either Android or iOS).
Wouldn’t the schema be simpler if the Message table gets “from” and “to” and you get rid of the MessageBox table?
=> I’m not quiet understanding above suggestion. Are you suggesting to store rid (related id - message objectId?) in MessageBox table?
Well, I looked support site, articles, code gen, and samples from git, but I couldn’t find answer to retrieving all rows where id is myid along with single top (as in SELECT TOP) item related object.(last message)
If my schema is overkill, I really need some suggestions.
First, let me explain better.
The point in creating such schema is to minimize number of calls.
When user logs into server, I want to retrieve most recent messages for each conversation.(each friend)
Most recent message can be one I sent or friend sent.
The whole point to having MessageBox is to retrieve most recent messages for each conversation with a SINGLE QUERY.
I don’t want to query for latest Message for number of conversation(friend) when user logs in.
Previously with different Baas, I created MessageBox and Message table, which both were having the same columns - text, sender Id + name, receiver Id + name, etc. The MessageBox is used for storing latest message per conversation, and Message table is container for each message. So whenever user sends message, it overwrites to MessageBox and creates new Message, which is not very efficient. MessageBox also contains delete time. Since Message(array) should be readable to two people, it had two distinct deleteTime - it will only delete Messages created before deleteTime if both deleteTime is set. (if not deleted, one with deleteTime will query messages after deleteTime)
No, I was suggesting to go with only one table without the split between Messages and MessageBox. Let’s table that discuss and figure out how to get the latest message with a single query.
The sample article I referenced in my previous response shows how to sort your objects by the “created” column. If you sort your messages by the “created” column in the descending order, then the very first message is going to be the latest one.Does it answer your question about a single query request?
The thing is that I don’t delete messages even if the user reads because my app supports multi device login just like facebook messenger. Which means I would have to query for all messages and get latest messages for each friend.
And I was hoping to find a way around it because if user has 120 friends with 10 messages each, then I would have to either
A) query for once to retrieve all 1200 messages and figure out(by client) latest messages for each friend
B) query 120 times get latest ones for each.
Both of above will take too much time so I was looking for single step query to get latest message for each friend.
The need for polling latest message for each friend is for displaying friends list with photo, name, and last message just like any other messenger apps.
Can you give me advise? I really have no idea other than having message box which keeps latest message for each friend.
With the scenario you described (120 friends with 10 messages each), do you need to load all 1200 messages at once and know which message is the latest for each friend?
This would be possible, but I am curious what the UI looks like that would require such a request. Any chance you could share a screenshot or a mockup?
Please find attached images. MessageBox shows list of friends with last message (could be received or sent), and chat is for displaying conversation between two friends. My app or any messenger app has similar UI as those ones attached.
As we all know, What’s app, Line, Viber, Tango or any apps that uses single device login by phone number, stores all messages to device once messages are retrieved then deletes retrieved messages on the server.
Unlike single device login apps, facebook messenger, badoo, or anything with multi device login(eg. email login) support, messages can not be deleted once retrieved because messages can be retrieved from another device.
Again, is it possible to get last message from each friend with single query. That’s best way that I can think of.
I think I understand what you’re looking for now. Would it be possible for you to make an export from your app (preferably with data) so I can recreate the tables on my side? You can do it in Backendless console by going to Manage > Export and then select “All Data Tables”.
I sent email to you!
Exported data must not be seen by others so I figured sending email is better.
I also wrote explanation on how I’ve changed schema.
