Create a nested return model with Knex.js

Question

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, res) => {
        const getOperation = Knex.from('recipes')
        // .innerJoin('ingredients', 'recipes.guid', 'ingredients.recipe')
        .select()
        .orderBy('rating', 'desc')
        .limit(10)
        .then((recipes) => {
            if (!recipes || recipes.length === 0) {
                res({
                    error: true,
                    errMessage: 'no recipes found'
                });
            }

            const recipeGuids = recipes.map(recipe => recipe.guid);
            recipes.forEach(r => r.ingredients = []);
            const getOperation2 = Knex.from('ingredients')
                .whereIn('recipe', recipeGuids)
                .select()
                .then((ingredients) => {
                    recipes.forEach(r => {
                        ingredients.forEach(i => {
                            if (i.recipe === r.guid) {
                                r.ingredients.push(i);
                            }
                        });
                    });
                    res({
                        count: recipes.length,
                        data: recipes
                    });
                });
        });
    }
}

Is there a way to create a return model with Knex.js that has nested objects that match the parent's id/guid so that I don't have nested promises?

Answer 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) => {
        return getRecipeNIngredients()
            .then((recipes) => {
                res({
                    count: recipes.length,
                    data: recipes
                });
            })
            .catch((ex) => {
                res({
                    error: true,
                    errMessage: ex.message
                });
            });
};

    function getRecipeNIngredients() {
        let recipes = null;
        return getRecipes()
            .then((recipeList) => {
                recipes = recipeList;
                const recipeGuids = recipes.map(recipe => recipe.guid);
                recipes.forEach(r => r.ingredients = []);
                return getIngredients(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;
            });
    };

    function getRecipes() {
        return Knex.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) {
                    throw new Error('no recipes found')
                }
            })
    };
    function getIngredients(recipeGuids) {
        return Knex.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.