Wednesday, 18 September 2013

What would be the best method to save message conversations in Database

What would be the best method to save message conversations in Database

Note: I have already read all the articles and questions about chat and
messages on this site. So donot try to provide me links, they are just
helping out users with MySql but I am using SQL CE Note the CE in it. And
I know this aint a code generating site I respect the terms of use of this
site. But I got no choice! I have to go for a suggestion.
What I want: I want to have a messages table in my database. What I want
is that it should save the messages and then I would get the messages from
it. It should save Time, Message, Sender and Recipient and finally Seen
column.
What I am having: The database table that I am having contains these
columns. But the issue is that I want these:
Distinct sender and recipient. More like a threaded conversation.
Ordered by Time. So that the latest one comes up.
Rest will be accessed by a query inside the block. So that's not an issue
like the Profile Picture, UserId, UserName and others
What I was able to create is this:

You can see that I did create the table. That saved the chats! But the
point to note here is that I have used this query.
SELECT DISTINCT Sender, Recipient FROM Messages WHERE Sender =2 OR
Recipient =2
And I was able to get the results like a Thread, but they were not
ordered. They won't be ordered unless I select time too. But selecting the
TIME will remove the functionality of DISTINCT.
Any kind of suggestion will be appreciated. I just don't know how to
create a threaded view and order it by Time! Remember I am using SQL
Server CE so the tricks you can do on SQL Server won't work here. So
provide me with a code that would select these all and would help me out
too. I am stuck on this one!
Thanks in advance for your precious time guys, Cheers!

No comments:

Post a Comment