Create A Nested Return Model With Knex.js

I'm using Knex.js to query a MySQL database in a Hapi.js route. The following code works but requires a nested query: { path: '/recipes', method: 'GET', handler: (req,

Solution 1:

Short answer: No.

With Knex, you can retrieve data the same as with SQL, which is record based, not object based, so the closest that you could come would be to use a join to allow doing just a single select to retrieve a single array having elements: recipes, guids, ingredients. This would repeat the recipe & guid for each ingredient, which you avoid by using nested objects. (See the answer below by @Fazal for an example of this.)

As another alternative, you could store the ingredients as a 'blob' field in the recipe table, but I don't believe that MySQL would allow you to create an Array field, so when retrieving the data, you would have to do a transform of the field into the array. And transform it from the Array before updating it into the table. Like: storableData = JSON.stringify(arrayData) and arrayData = JSON.parse(storableData)

There are a few other things that I would suggest to help you improve the code though. (Yeah, I know, not really the question here):

1. Separate the routing functionality from data handling.
2. Also, separate data manipulation functionality from retrieval.
3. Use throw & .catch for creating and handling unsuccessful responses.

The separation of routing, data retrieval, data manipulation makes testing, debugging, and future comprehension easier as each function has a more atomic purpose.

Throwing/.catching unsuccessful process conditions makes it much simpler to have more comprehensive error processing by allowing you to put (most of the time) a single .catch in your router response handling (Hapi.js may even do this .catch for you???).

Also, see the other .catch and .on('query-error' that I added for logging errors. You may have a different logging mechanism you want to use rather than the console. I use Winston. And note that .on('query-error' is NOT a .catch. There will still be an Error() that is thrown, and must be handled somewhere, this will just give you good info about the failure close to the source.

(Sorry, the below code is untested)

path: '/recipes',
method: 'GET',
handler: (req, res) => {
        returngetRecipeNIngredients()
            .then((recipes) => {
                res({
                    count: recipes.length,
                    data: recipes
                });
            })
            .catch((ex) => {
                res({
                    error: true,
                    errMessage: ex.message
                });
            });
};

    functiongetRecipeNIngredients() {
        let recipes = null;
        returngetRecipes()
            .then((recipeList) => {
                recipes = recipeList;
                const recipeGuids = recipes.map(recipe => recipe.guid);
                recipes.forEach(r => r.ingredients = []);
                returngetIngredients(recipeGuids);
            })
            .then((ingredients) => {
                recipes.forEach(r => {
                    ingredients.forEach(i => {
                        if (i.recipe === r.guid) {
                            r.ingredients.push(i);
                        }
                    });
                });
                return recipes;
            })
            .catch((ex) => {
                console.log(".getRecipeNIngredients ERROR ex:",ex); // log and rethrow error.throw ex;
            });
    };

    functiongetRecipes() {
        returnKnex.from('recipes')
            // .innerJoin('ingredients', 'recipes.guid', 'ingredients.recipe')
            .select()
            .orderBy('rating', 'desc')
            .limit(10)
            .on('query-error', function(ex, obj) {
                console.log("KNEX getRecipes query-error ex:", ex, "obj:", obj);
            })
            .then((recipes) => {
                if (!recipes || recipes.length === 0) {
                    thrownewError('no recipes found')
                }
            })
    };
    functiongetIngredients(recipeGuids) {
        returnKnex.from('ingredients')
            .whereIn('recipe', recipeGuids)
            .select()
            .on('query-error', function(ex, obj) {
                console.log("KNEX getIngredients query-error ex:", ex, "obj:", obj);
            })
    };

I hope this is Useful! Gary.

Solution 2:

You can easily avoid nest query. Just use subquery as-

knex.select('*')
  .from(function () {
    this.select('*').from('recipes').limit(10).as('recipes'); // limit here
  })
  .leftJoin('ingredients', 'ingredients.recipe_id', 'recipes.guid')
  .then((rec) => {
    console.log(rec);
  })

see.. just few lines of code.

Solution 3:

I created a library that return nested object even it has types for typescript

Nested Knex

import * as n from'nested-knex';

n.array(
  n.type({
    id: n.number("recipe.id", { id: true }),
    title: n.string("recipe.title"),
    ingredients: n.array(
      n.type({
        id: n.number("ingredients.id", { id: true }),
        title: n.string("ingredients.title")
      })
    )
  })
)
  .withQuery(
    knex
      .from("recipes")
      .innerJoin("ingredients", "recipes.guid", "ingredients.recipe")
      .select()
      .orderBy("rating", "desc")
      .limit(10)
  )
  .then(recipes => {});

so recipes even have types