Dexie.js - how to join tables?

Question

I'm currently using Dexie.js to store data locally. I have 3 different tables, that are joined with each other by using foreign keys. I managed to setup the schema and insert the corresponding data. However, when I want to retrieve the data, I failed to find an example of how to join different tables.

Here's an example:

var db = new Dexie('my-testing-db');
db.delete().then(function() {

  db.version(1).stores({
    genres: '++id,name',
    albums: '++id,name,year,*tracks',
    bands: '++id,name,*albumsId,genreId'
  });

  db.transaction('rw', db.genres, db.albums, db.bands, function() {
    var rock = db.genres.add({
        name: 'rock'
      }),
      jazz = db.genres.add({
        name: 'jazz'
      });

    var justLookAround = db.albums.add({
      name: 'Just Look Around',
      year: 1992,
      tracks: [
        'We want the truth', 'Locomotive', 'Shut me out'
      ]
    });

    var sickOfItAll = db.bands.add({
      name: 'Sick Of it All'
    });

    justLookAround.then(function(album_id) {
      rock.then(function(rock_id) {
        sickOfItAll.then(function(band_id) {
          db.bands.update(band_id, {
            genreId: rock_id,
            albumsId: [album_id]
          }).then(function(updated) {

          });
        });
      });
    });

  }).then(function() {
    //how to join the tables here????
    db.bands.each(function(band) {
      console.log(band);
    });
  });
});

Sorrows

To spot & link duplicates, I note this apparently identical question the asker apparently then posted at github.com/dfahlander/Dexie.js/issues/232 but that seems to have less info than here. – Adscititious

Answer 1

Here's how to join the result. Disclaimer: code not tested!

var all = Dexie.Promise.all;

function joinBands (bandCollection) {

    // Start by getting all bands as an array of band objects
    return bandCollection.toArray(function(bands) {

        // Query related properties:
        var genresPromises = bands.map(function (band) {
            return db.genres.get(band.genreId || 0);
        });
        var albumsPromises = bands.map(function (band) {
            return db.albums.where('id').anyOf(band.albumsId || []).toArray();
        });

        // Await genres and albums queries:
        return all ([
            all(genresPromises),
            all(albumsPromises)¨
        ]).then(function (genresAndAlbums) {

            // Now we have all foreign keys resolved and
            // we can put the results onto the bands array
            // before returning it:
            bands.forEach(function (band, i) {
                band.genre = genresAndAlbums[0][i];
                band.albums = genresAndAlbums[1][i];
            });
            return bands;
        });
    });
}

// Join all:
joinBands(db.bands.toCollection()).then(function (bands) {
    alert ("All bands: " + JSON.stringify(bands, null, 4));
}).catch(function (error) {
    alert ("Oops: " + error);
});

// Query and join:
joinBands(db.bands.where('genreId').anyOf([1,5,19]).limit(25)).then(function (bands) {
    alert ("Some bands: " + JSON.stringify(bands, null, 4));
}).catch (function (error) {
    alert ("Oops: " + error);
});

Preferably call joinBands() from within a transaction to speed the queries up as well as getting a more reliable and atomic result.

Reckless

I wish it were a little more intuitive ;-) – Jujitsu

Answer 2

Unfortunately, I arrived here from google looking for actual joins, you know, something of the kind:

db.bands.where(...).equals(..).join(
  db.genres.where(...).etc(), 'genreId -> genres.id').then(
    function(band, genre) { ... });

This, I think is closer to what the original questioner asked, but based on the the answer provided by @david-fahlander, it seems that this plugin, https://github.com/ignasbernotas/dexie-relationships, might be a bit easier, if you're looking to build a nice object tree.

The readme of the plugin is very similar to your example, so I've copied it verbatim here:

Schema

Note the use of -> which sets the foreign keys.

import Dexie from 'dexie'
import relationships from 'dexie-relationships'

var db = new Dexie('MusicBands', {addons: [relationships]})

db.version(1).stores({
    genres: 'id, name',
    bands: 'id, name, genreId -> genres.id',
    albums: 'id, name, bandId -> bands.id, year'
});

Usage

db.bands
  .where('name').startsWithAnyOf('A', 'B') // can be replaced with your custom query
  .with({albums: 'albums', genre: 'genreId'}) // makes referred items included
  .then(bands => {
      // Let's print the result:
      bands.forEach (band => {
          console.log (`Band Name: ${band.name}`)
          console.log (`Genre: ${band.genre.name}`)
          console.log (`Albums: ${JSON.stringify(band.albums, null, 4)}`)
      });
})

Jujitsu

Too bad this package isn't maintained anymore... – Coyotillo

Looks like the package has been transferred to @David Fahlander and is being maintained again – Enlarger