Relationships from within arrays?

[Sjoerd82]

I have a lot of Dexie tables that have an array with foreign keys. For example, a shopping bag with items:

{
    shoppingBagId: 1000,
    weight: 42,
    bagContents: [
        {
            itemId: 299,
            quantity: 1,
        },
        {
            itemId: 792,
            quantity: 1,
        },
        {
            itemId: 458,
            quantity: 1,
        },
    ]
}

This was all fine when I my application was always online and my relational backend did all the joining, but now that I'm moving to an offline mode, suddenly all the relational goodness has to be baked in into the IndexedDB key store. But I'm rambling.. so ideally I would make those itemId's point to my item table.

{
    shoppingBagId: 1000,
    weight: 42,
    bagContents: [
        {
            itemId: 299, -> points to the item table
            quantity: 1,
        },
        ...
    ]
}

Is this anywhere within the realm of possibilities? Also, it makes me wonder at which point the key store becomes a relational database again ;-).

[Sjoerd82]

Been browsing around a bit; this may be similar to #37.

[dfahlander]

You can have a fully relational model in IndexedDB as has the most important parts for this: ACID transactions across tables and indices. Dexie-relationships solves only a subset of the use cases and relational models but its really just some syntactic sugar and no foreign key enforcements or full relational support.

I find myself using plain dexie queries many times when modelling relational models though. In this case, I would probably just use bulkGet() to resolve them:

async function getShoppingBag(shoppingBagId) {
  const bag = await db.shoppingBags.get(shoppingBagId);
  const itemIds = bag.bagContents.map(bc => bc.itemId);
  const referredItems = await db.items.bulkGet(itemIds);
  return {
    ...bag,
    bagContents: bag.bagContents.map((bc, idx) => ({
      ...bc,
      item: referredItems[idx]
    }))
  };
}

[Sjoerd82]

Ah, interesting, seems to work great. Hadn't used get()/bulkGet() before. Until now I have done pretty much everything with .where's, and would have written this like:

async function getShoppingBag(shoppingBagId) {
  const bag = await db.shoppingBags.where('shoppingBagId').equals(shoppingBagId).first();
  const itemIds = bag.bagContents.map(bc => bc.itemId);
  const referredItems = await db.items.where('itemId').anyOf(itemIds).toArray();
  return {
    ...bag,
    bagContents: bag.bagContents.map((bc, idx) => ({
      ...bc,
      item: referredItems[idx]
    }))
  };
}

Differences are:

.get(shoppingBagId) vs .where('shoppingBagId').equals(shoppingBagId).first()

and

.bulkGet(itemIds) vs .where('itemId').anyOf(itemIds).toArray()

Both versions return the same results.
Curious, are .get()'s faster?

So, the answer to the original question is, no, not with dexie-relationships.
I do wonder about the performance impact of the proposed approach, will it scale with "large*" number of rows? (*tens of thousands)

[dfahlander]

• where().equals().first() returns same as get. If not found, both returns undefined.
• bulkGet() is different from anyOf() as bulkGet() will always return an array of the same size as given array. Non-found items will have undefined in their corresponding positions. anyOf() will omit not-found entries so it's not safe to rely on the index position. Another difference is that bulkGet does the queries in parallell, while anyOf uses a cursor to iterate results one by one so the former should be faster. I have not performed any tests that confirms it though.