sqlsqlitemany-to-manyandroid-sqlitesql-order-by

Many to many relationship when order matters

I have many to many relation between User and Group: group may contains few users and consists in multiply groups. Server send me json like this, and I need to store it to DB:

group: {
  name: "test", id: "UUID"
  users: [
    {name: "user1", id: "UUID"}, 
    {name: "user2", id: "UUID"}, 
    {name: "user3", id: "UUID"} 
  ]
}

The problem is: users order in group matters - users should be displayed in the same order like in servers json. The fist solution that I use is add to group field like user_ids: [user1_uuid, user2_uuid, user3_uuid] and store it into DB in json fromat, but I see 2 problems in this solution:

  1. It contradicts to first normal form (1NF)
  2. When I SELECT values they have wrong order:

SELECT * FROM user WHERE id IN (:user_ids)

How to store it into DB and SELECT?

Solution

  • In SQL, table rows indeed are unordered; the only guaranteed way to get ordered rows is to use ORDER BY.

    If the order of your array elements is important, add a column for the array index.