Facing problem with leftJoin and leftJoinRelation

[k98kumar]

How the db is set up:

• Each user can have multiple ShoppingLists and multiple Recipes
• Each ShoppingList and each Recipe can have multiple Ingredients
  • There is a polymorphic association between ShoppingList/Recipe and Ingredients, following https://vincit.github.io/objection.js/recipes/polymorphic-associations.html

I am having an issue with the file at /src/routes/test.js. First step is to do the post: curl -X POST -H "Content-Type:application/json" http://localhost:8000/test. In the router.get('/:userId', async(req, res) => {}), the leftJoins won't work. Also, when I replace the two leftJoin calls with the leftJoinRelation call, the same thing is logged. However, with the req.context.models.User.knex().raw() the join works.

Question: Am I forgetting to do a step before joining? Also, is there a method to output the data as a JSON object, like the one we put into insertGraph? Currently, I am just using knex.raw() and I'm receiving an array of row objects.

DB: Postgres

/db/migrations/schema.js

var uuidv4 = require('uuid/v4');
exports.up = knex => {
  return knex.schema
    .createTable('users', table => {
      table.increments('id').primary();
      table.string('username');
      table.string('password');
    })
    .createTable('shoppingLists', table => {
      table.uuid('id').defaultTo(uuidv4()).primary();
      table.integer('user_id').unsigned().index();
      table.text('title');
    })
    .createTable('recipes', table => {
      table.uuid('id').defaultTo(uuidv4()).primary();
      table.integer('user_id').unsigned().index();
      table.text('title');
    })
    .createTable('ingredients', table => {
      table.increments('id').primary();
      table
        .uuid('document_id')
        // .references('id')
        // .inTable('shoppingLists')
        // .references('id')
        // .inTable('recipes')
        // .onDelete('CASCADE')
        .index();
      table.enu('document_type', ['shoppingList', 'recipe']);
      table.string('name');
    });
}
exports.down = knex => {
  return knex.schema
    .dropTableIfExists('ingredients')
    .dropTableIfExists('recipes')
    .dropTableIfExists('shoppingLists')
    .dropTableIfExists('users');
}

/src/index.js

import 'dotenv/config';
import cors from 'cors';
import bodyParser from 'body-parser';
import express from 'express';
import Knex from 'knex';
import { Model } from 'objection';
import models from './models';
import test from './routes/test';
import knexConfig from '../knexfile';
const knex = Knex(knexConfig.development);
Model.knex(knex);
const app = express();
app.use(cors());
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(async (req, res, next) => {
  req.context = { models };
  next();
});
app.use('/test', test);
const server = app.listen(process.env.PORT, () => {
  console.log(`App listening on port ${process.env.PORT}!`);
});
server.setTimeout(5000);

/src/models/index.js

import User from './User';
import ShoppingList from './ShoppingList';
import Recipe from './Recipe';
import Ingredient from './Ingredient';
const models = {
  User, ShoppingList,
  Recipe, Ingredient
};
export default models;

/src/models/Ingredient.js

import { Model } from 'objection';
import ShoppingList from './ShoppingList';
import Recipe from './Recipe';
class Ingredient extends Model {
  static get tableName() {
    return 'ingredients';
  }
  static get relationMappings() {
    return {
      shoppingLists: {
        relation: Model.BelongsToOneRelation,
        modelClass: ShoppingList,
        join: {
          from: 'ingredients.document_id',
          to: 'shoppingLists.id'
        }
      },
      recipes: {
        relation: Model.BelongsToOneRelation,
        modelClass: Recipe,
        join: {
          from: 'ingredients.document_id',
          to: 'recipes.id'
        }
      }
    }
  }
}
export default Ingredient;

/src/models/Recipe.js

import { Model } from 'objection';
import uuidv4 from 'uuid/v4';
import User from './User';
import Ingredient from './Ingredient';
class Recipe extends Model {
  $beforeInsert(queryContext) {
    super.$beforeInsert(queryContext);
    this.id = uuidv4();
  }
  static get tableName() {
    return 'recipes';
  }
  static get relationMappings() {
    return {
      users: {
        relation: Model.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: 'recipes.user_id',
          to: 'users.id' 
        }
      },
      ingredients: {
        relation: Model.HasManyRelation,
        modelClass: Ingredient,
        filter(builder) {
          builder.where('document_type', 'recipe');
        },
        beforeInsert(model) {
          model.document_type = 'recipe';
        },
        join: {
          from: 'recipes.id',
          to: 'ingredients.document_id' 
        }
      }
    }
  }
}
export default Recipe;

/src/models/ShoppingList.js

import { Model } from 'objection';
import uuidv4 from 'uuid/v4';
import User from './User';
import Ingredient from './Ingredient';
class ShoppingList extends Model {
  $beforeInsert(queryContext) {
    super.$beforeInsert(queryContext);
    this.id = uuidv4();
  }
  static get tableName() {
    return 'shoppingLists';
  }
  static get relationMappings() {
    return {
      users: {
        relation: Model.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: 'shoppingLists.user_id',
          to: 'users.id' 
        }
      },
      ingredients: {
        relation: Model.HasManyRelation,
        modelClass: Ingredient,
        filter(builder) {
          builder.where('document_type', 'shoppingList');
        },
        beforeInsert(model) {
          model.document_type = 'shoppingList';
        },
        join: {
          from: 'shoppingLists.id',
          to: 'ingredients.document_id' 
        }
      }
    }
  }
}
export default ShoppingList;

/src/models/User.js

import { Model } from 'objection';
import ShoppingList from './ShoppingList';
import Recipe from './Recipe';
class User extends Model {
  static get tableName() {
    return 'users';
  }
  static get relationMappings() {
    return {
      shoppingLists: {
        relation: Model.HasManyRelation,
        modelClass: ShoppingList,
        join: {
          from: 'users.id',
          to: 'shoppingLists.user_id'
        }
      },
      recipes: {
        relation: Model.HasManyRelation,
        modelClass: Recipe,
        join: {
          from: 'users.id',
          to: 'recipes.user_id'
        }
      }
    }
  }
}
export default User;

/src/routes/test.js

import { Router } from 'express';
import { transaction } from 'objection';
const router = Router();
function getModel(models, documentType) {
  const docTypeLC = documentType.toLowerCase();
  return models[`${docTypeLC.charAt(0).toUpperCase()}${docTypeLC.slice(1)}`];
}
router.post('/', async (req, res) => {
  const insertedGraph = await transaction(req.context.models.User.knex(), trx => {
    return req.context.models.User.query(trx)
      .allowInsert('[shoppingLists.[ingredients], recipes.[ingredients]]')
      .insertGraph({
        username: 'username',
        password: 'password',
        shoppingLists: [{
          title: 'Shopping List',
          ingredients: [{ name: 'shoppingList-ingredient', document_type: 'shoppingList' }]
        }],
        recipes: [{
          title: 'Recipe',
          ingredients: [{ name: 'recipe-ingredient', document_type: 'recipe' }]
        }]
      })
      .catch(error => error);
  });
  console.log(insertedGraph);
  return res.send(insertedGraph);
});
router.get('/:userId', async(req, res) => {
  const userId = req.params.userId;
  let userAndDocuments = await req.context.models.User
    .query()
    .select()
    // .leftJoinRelation('[shoppingLists.[ingredients], recipes.[ingredients]]')
    .leftJoin('shoppingLists', 'users.id', 'shoppingLists.user_id')
    .leftJoin('ingredients', 'shoppingLists.id', 'ingredients.document_id')
    .where('users.id', Number(userId))
    .catch(error => error);
  console.log(userAndDocuments);
  const knex = await req.context.models.User.knex();
  userAndDocuments = await knex.raw(`
    SELECT * FROM users
    LEFT JOIN "shoppingLists" ON users.id = "shoppingLists".user_id
    LEFT JOIN ingredients ON "shoppingLists".id = ingredients.document_id
    WHERE users.id = ${userId}
  `);
  console.log(userAndDocuments.rows);
  return res.send(userAndDocuments);
});
export default router;

[k98kumar]

I just realized that I can use eager loading to get all the data I'm looking for without doing joins myself. However, I am still wondering why the leftJoin and leftJoinRelation methods did not work and knex.raw() worked.

[koskimas]

Joins don't nest the objects like eager loading. They simply do an SQL join.

[k98kumar]

I understand that, but it seems like the leftJoin() and leftJoinRelation() don't do anything. When I do curl http://localhost:8000/test/1: For the leftJoins, I get:

[ User { id: 1, username: 'username', password: 'password' } ]

But for knex.raw(), I get:

[
  {
    id: 1,
    username: 'username',
    password: 'password',
    user_id: 1,
    title: 'Shopping List',
    document_id: '012023b2-30e9-4cff-87af-0c154ca0b2c9',
    document_type: 'shoppingList',
    name: 'shoppingList-ingredient'
  }
]

[k98kumar]

@koskimas Is there a reason that leftJoin doesn't actually do the SQL join like knex.raw?

[eopo]

The join is made, but the fields aren't included. You have to manually .columns(['origin.','related.'] to include the fields in your query. You can enable debug logging and view the query that is actually made to see this behavior.