CODEWITHSUNDEEP
sqlsqlitemany-to-manyandroid-sqlitesql-order-by
anber
anber

Reputation: 3665

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?

Upvotes: 0

Views: 526

Answers (2)

dbilid
dbilid

Reputation: 277

You could use the following schema:

CREATE TABLE user(id integer primary key, name text);
CREATE TABLE grp(id integer primary key, name text);
CREATE TABLE usergrp(userid integer, grpid integer, position integer);
CREATE INDEX positionindex on usergrp(grpid, position);

Then you can retrieve users belonging to a group with the query 

SELECT userid FROM usergrp WHERE grpid=? ORDER BY position;

Also, maybe you would like to use the "without rowid" sqlite table storage option for usergrp.

Upvotes: 0

CL.
CL.

Reputation: 180172

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.

Upvotes: 1

Related Questions