[Feature Request] Many to Many Example?

[oleersoy]

Hi, Thank you for making Dexie! It's really amazing. Just curious if there are any many to many examples for Typescript? I read through the documentation and checked on SO, but did not come across any thus far.

[dfahlander]

I don't think we have one example yet. Don't know why. But the most generic way would be to create a junction table just as you would do in SQL.

Sample using junction table

const db = new Dexie('many2ManyJunction');
db.version(1).stores({
  users: '++id',
  groups: '++id',
  usersInGroups: '[userId+groupId],userId,groupId'
});

async function putUserInGroup(user, group) {
  await db.usersInGroups.put({userId: user.id, groupId: group.id});
}

async function removeUserFromGroup(user, group) {
  await db.usersInGroups.delete([user.id, group.id]);
}

async function getUserGroups(user) {
  const groupLinks = await db.usersInGroups.where({userId: user.id}).toArray();
  const groups = await Promise.all(groupLinks.map(link => db.groups.get(link.groupId)));
  return groups;
}
async function getGroupUsers(group) {
  const groupLinks = await db.usersInGroups.where({groupId: id}).toArray();
  const users = await Promise.all(groupLinks.map(link => db.users.get(link.userId)));
  return users;
}

NOTE: This code is dry-written and might not compile

Explanation: The junction table (usersInGroups) use a compound primary key [userId+groupId] that identifies each entry. This makes sure the same user cannot be put into the same group twice. It also simplifies deletion as you can see in the removeUserFromGroup.

In Dexie version 3 (alpha), you wouldn't need the extra userId index on the junction table, as it can utilize the compound index for searching on userId only.

The sample is rather simple. You might want to use transactions to maintain consistency.

One could also use arrays of keys on one side and in that way skip the junction table. If one side of the relationships are few, you could also use arrays of keys (multiEntry indexed) on that side and a single key on the other side, but the sample below does not do that).

[dfahlander]

With consistency added:

function putUserInGroup(user, group) {
  return db.transaction('rw', db.users, db.groups, db.usersInGroups, async () => {
    const [validUser, validGroup] = await Promise.all([
      db.users.where({id: user.id}).count(),
      db.groups.where({id: group.id}).count()
    ]);
    if (!validUser) throw new Error('Invalid user');
    if (!validGroup) throw new Error('Invalid group');
    await db.usersInGroups.put({userId: user.id, groupId: group.id});
  });  
}

...

function getUserGroups(user) {
  return db.transaction('r', db.usersInGroups, db.groups, async ()=>{
    const groupLinks = await db.usersInGroups.where({userId: user.id}).toArray();
    const groups = await Promise.all(groupLinks.map(link => db.groups.get(link.groupId)));
    return groups;
  });
}

...

[oleersoy]

Sweet! Thank you so much for explaining this. That's a huge help. I'm going to play around with it and will write up a blog post ASAP. I will also patch the documentation once I have a properly working example.

[oleersoy]

@dfahlander I created a stackblitz with the code samples. I pasted the code and tried this:

putUserInGroup(user1, group1);
putUserInGroup(user2, group1);

let groups = getUserGroups(user1);
console.log(groups);
groups.then(g=>console.log("Sup G", g));

However I'm not seeing group1 logged. Any ideas? It's also drawing some red squigglies under usersInGroup with the message:

Property 'usersInGroups' does not exist on type 'MyAppDatabase'. any

In addition I added the consistency examples with a C appended at the end of the method names, but commented them out because a lot of errors were being generated, and I figured it would be better to get the non consistency examples working first.

[dfahlander]

Typings

In Typescript, you can subclass Dexie like this (See our Typescript tutorial):

class MyDB extends Dexie {
   users: Dexie.Table<User, number>;
   groups: Dexie.Table<Group, number>;
   usersInGroups: Dexie.Table<UserInGroup, [number, number]>;

  constructor() {
    this.super('many2ManyJunction');
    this.version(1).stores({...});
    this.users = this.table('users');
    this.groups = this.table('groups');
    this.usersInGroups = this.table('usersInGroups');
  }
}

Race Condition

As the putUserInGroup returns a Promise, you will need to wait for it to complete before it will be persisted.

So in your sample code, you are requesting getUserGroups(user1) before knowing that the two previous operations have completed. I suppose that could be the reason why you are only seeing one of the groups logged. There could also be a typo or an error thrown somewhere. In any case, be sure to call the Promise-returning functions properly. The easiest way to do that is using async/await: Then you'll not miss any typo or exception thrown.

async function test() {
  await putUserInGroup(user1, group1);
  await putUserInGroup(user2, group1);

  let groups = await getUserGroups(user1);
  console.log(groups);
}

test()
  .then(() => console.log("Done."))
  .catch(error => console.error(error));

[oleersoy]

In Typescript, you can subclass Dexie like this (See our Typescript tutorial):

OK nice - almost there! I think I forgot to initialize the table properties after the constructor in the MyAppDatabase class. I have this now (Does the UserInGroup class look OK? - I used an number[] for the id property type):

class UserInGroup {
  id: number[];
  userId: number;
  groupId: number;
}
import Dexie from 'dexie';

class MyAppDatabase extends Dexie {
    users: Dexie.Table<User, number>; 
    groups: Dexie.Table<Group, number>; 
    usersInGroups: Dexie.Table<UserInGroup, [number, number]>;
    constructor () {
        super("MyAppDatabase");
        this.version(1).stores({
            users: '++id',
            groups: '++id',
            usersInGroups: '[userId+groupId],userId,groupId'
        });
        this.users = this.table('users');
        this.groups = this.table('groups');
        this.usersInGroups = this.table('usersInGroups');
    }
}

I noticed that the usersInGroups is defined like this:

    usersInGroups: Dexie.Table<UserInGroup, [number, number]>;

So should the indexeddb property be defined like this:

usersInGroups: '[userId, groupId],userId,groupId'

Instead of like this?:

usersInGroups: '[userId+groupId],userId,groupId'

I also updated the methods to use the typescript db.table construct like this:

async function putUserInGroup(user:User, group:Group) {
  await db.table("usersInGroups").put({userId: user.id, groupId: group.id});
}

This is the updated stackblitz:

https://stackblitz.com/edit/typescript-dexie-manytomany

Not logging anything yet, but I have a feeling it's something really trivial. I just realized I have not initialized the user and group tables with those entities. Sorry for all the chattyness. I'm getting a little too excited about this. Dexie is such an awesome piece of work!

[oleersoy]

OK - I think it's working now. I did not test the consistency methods, but I assume those should be fine as well. This is the Stackblitz:

https://stackblitz.com/edit/typescript-dexie-manytomany

I'll post the answer to SO and I can patch the documentation.

[oleersoy]

Wrote a blog post here: https://medium.com/@ole.ersoy/many-to-many-relationships-with-dexiejs-753b8e305d4e

Can we just link this in the documentation or do you want me to explicitly patch it. Just let me know. I really appreciate all your help with this.