0

I have message table in dynamodb with the following fields:

Primary partition key => conversation_id

Primary sort key => id

other attributes => message,date_time,sender_id

+------------------+----------+----------------------+------------------------+----------+
| conversation_id  | id       | message              | date_time              | sender_id|
+----------------------------------------------------+------------------------+----------+
| p1OS9E           | S04Ln    | Hi.. how are you..?  | 2016-11-30 06:58:11 pm | 11       |
| p1OS9E           | JZkSo    | Work finished..?     | 2016-11-30 06:58:13 pm | 11       |
| p1OS9E           | EN9N4    | I am fine.           | 2016-11-30 06:58:12 pm | 12       |
|                  |          |                      |                        |          |
| a0zgOO           | jmDdm    | In online..?         | 2016-12-40 08:43:12 pm | 51       |
| a0zgOO           | mAEdY    | Yes.. say..          | 2016-12-40 08:43:14 pm | 34       |
| a0zgOO           | aWKrp    | Come to play..       | 2016-12-40 08:43:12 pm | 51       |
+------------------+----------+----------------------+------------------------+----------+

While query items based on partition key, the result returned as unordered(not in inserted order). The sort key id, AttributeType is `string', a random generated code to make the Primary key as unique.

First i used date_time as sort key, but if within same conversation_id with same date_time a message will enter. So the data loss may occur.

How to get my items with the order the message get inserted(based on time)..?

Note: I am using PHP (Codeigniter MVC).

Jerodev
  • 32,252
  • 11
  • 87
  • 108
ArunValaven
  • 1,753
  • 2
  • 25
  • 44

1 Answers1

1

There are many ways you can achieve this, the following are the two approaches that I generally use:

1) Add indexes: You need to add LSI(local secondary index) to your table with range key as DateTime.

Now you can query you LSI by passing you hash key and for DateTime you can use >= or >= operators to get in sorted order.

2) Sorting at application level: I personally use this solution as we don't have to pay additional amount for index, Get all data based on Hash then sort manually at application level by DateTime

There is one more answer of mine with a similar issue.

Hope that helps

Community
  • 1
  • 1
Harshal Bulsara
  • 7,898
  • 4
  • 43
  • 61
  • @ Harshal Bulsara.. for LSI range key as `date_time`, then partition key..? – ArunValaven Jan 02 '17 at 12:10
  • @ Harshal Bulsara.. In order to add `date_time` as sort key, i need to remove the `date_time` in normal attribute...? If i create partition key in LSI as `conversation_id`, then i need to putitem into this field also..? – ArunValaven Jan 02 '17 at 12:13
  • No you hash key will be same as your tables key, and No you dont need to modify anything dynamodb will update the LSI when table data is changed – Harshal Bulsara Jan 02 '17 at 16:24
  • I think you should read about LSI first http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/LSI.html – Harshal Bulsara Jan 02 '17 at 16:24