Knex.js - Inlcuding multiple models in the response

index.js

const NODE_ENV = process.env.NODE_ENV || 'development';
const config = require('./knexfile')[NODE_ENV];
const knex = require('knex')(config);
const express = require('express');
const logger = require('knex-logger');

const app = express();
app.use(logger(knex));


// Gets all the users using a promise and making multiple SQL calls to fetch each list of symbols
// This is bad because it causes an N+1 query
app.get('/users', (req, res) => {
  getUsers().then(users => {
    // Make a SQL call for each of the users fetching out their symbols
    return Promise.all(users.map(u => {
      return getUserSymbols(u.id)
    })).then(symbols => {
      return users.map((u, i) => {
        u.sybmols = symbols[i].map(s => (s.name))
        return u
      })
    })
  })
  .then(users => {
    res.json(users)
  })
})

// Gets all the users with their symbols, using a single SQL call
// Processing is done in the then blocks to filter down results
app.get('/others', (req, res) => {
  knex('users')
    .select('users.name', 'users.id', 'symbols.name as symbolName')
    .join('symbols_users', 'user_id', '=', 'users.id')
    .join('symbols', 'symbol_id', '=', 'symbols.id')
    .then(results => {
      // Take all the results from the query and return an object that contains the user fiels and an array of symbols
      // {
      //   '1': {
      //     name: "Dave",
      //     symbols: ["APPL", "TD", "LHL"],
      //   }
      // }
      return results.reduce((acc, row) => {
        let user = acc[row.id] || { id: row.id, name: row.name, symbols: [] }
        user.symbols.push(row.symbolName)
        acc[row.id] = user
        return acc
      }, {})
    }).then(results => {
      // Map the object back to an array
      // [ { name: "Dave", symbols: ["APPL", "TD", "LHL"] } ]
      return Object.keys(results).map(k => (results[k]))
    }).then(results => {
      res.json(results)
    })
})

app.listen(3000)

const getUsers = () => {
  return knex('users')
}

const getUserSymbols = (user_id) => {
  return knex('symbols')
    .select('name')
    .join('symbols_users', 'symbol_id', '=', 'symbols.id')
    .where('user_id', user_id)
}