-1

Hi I'm building a chat messaging system and am reading/writing to the DB for my first time. I'm creating the method calls to retrieve relevant data I need for this chat. Like with most chat systems, I want to have a general list of message with the name, most recent message, and time/date associated with the most recent message. Once I click on that thread, the corresponding messages will show up. I'm trying to call the DB but am having trouble using the correct command to get the most recent message.

This is what one message contains:

{
   "_id":  "134a8cba-2195-4ada-bae2-bc1b47d9925a" ,
   "clinic_id": 1 ,
   "created": 1531157560 ,
   "direction":  "o" ,
   "number":  "14383411234" ,
   "read": true ,
   "text":  "hey hows it going"
}

Every single message that is sent and received gets POSTed like this. I'm having trouble coming up with the correct commands to get the most recent message of all the distinct "number" so that for number x, I get its corresponding recent message and with number y, I get its corresponding recent message. "created" is the time when the message was created in UNIX time.

This is what I have started with:

Retrieve all thread numbers:

r.db('d2').table('sms_msg').between([1, r.minval], [1, r.maxval], {index:'clinic_number'}).pluck(['number']).distinct()

Retrieve all messages in specific thread:

r.db('d2').table('sms_msg').getAll([1, "14383411234"], {index:'clinic_number'})

Retrieve recent message for all distinct threads:

r.db('d2').table('sms_msg').filter()....???

Some help would really be appreciated!

Sam
  • 495
  • 3
  • 11
  • 19

1 Answers1

0

That's a very tricky query for any database, and usually involves a multitude of sub-queries. You might want to consider denormalizing it, keeping a reference to last entry in another table, for each number.

But basically, with your current approach, this might work (untested) but might be highly inefficient:

r.table('sms_msg').orderBy(r.desc('created')).group('number').nth(0)

It's usually fast to get the lowest value of the property of a document, but when you want the whole document of a sorted list like this, it is very inefficient in my experience.

jishi
  • 24,126
  • 6
  • 49
  • 75