Editor server using a where clause with mjoin.

naspersgasp

Hi,

Good day.

I'm looking to limit the results returned of a parent table that uses an mjoin.

Tables:

Ingredients - [ Ingredient,... ]
IngredientUnit - [unit, quantity,... ] // Ingredient metadata Left join table
Recipe - [ Title,... ]
RecipeIngredient - [ ingredient_id, recipe_id ] // Mjoin table

Server side:

let recipeId = req.body.recipeId ? req.body.recipeId : ''; // Get the parent table id using https://datatables.net/blog/2016-03-25
let editor = new Editor(db, 'Ingredients').fields(
            new Field('Ingredients.Ingredient').validator(Validate.notEmpty()),
            new Field('IngredientUnit.unit').validator(Validate.notEmpty()),
            new Field('IngredientUnit.quantity').validator(Validate.notEmpty()),
        )
        .leftJoin('IngredientUnit', 'Ingredients.id', '=', 'IngredientUnit.ingredient_id')
        .join(
            new Mjoin('Recipes')
                .link('Ingredients.id', 'RecipeIngredient.ingredient_id')
                .link('Recipes.id', 'RecipeIngredient.recipe_id')
                .fields(
                    new Field('id')
                )
                .where({'Recipes.id': recipeId})
        )

JSON example:

    {
            "DT_RowId": "row_1",
            "Ingredients": {
                "Ingredient": "bicarbonate of soda"
            },
            "IngredientUnit": {
                "unit": "ml",
                "quantity": "15.00"
            },
            "Recipes": [
                {
                    "id": 82
                }
            ]
        },

The limit works in the mjoin. How would I use that for the parent table though. As I'm looking to display only Ingredients relevant to a single Recipe. I tried moving the where clause to the end:

editor.where({'Recipes.id': recipeId});

Which should work, but then I get the following:

Unhandled promise error:  [object Promise]Error: select count(`Ingredients`.`id`) as `cnt` from `Ingredients` left join `IngredientUnit` on `Ingredients`.`id` = `IngredientUnit`.`ingredient_id` where `Recipes`.`id` = '110' - ER_BAD_FIELD_ERROR: Unknown column 'Recipes.id' in 'where clause'
stack: Error: ER_BAD_FIELD_ERROR: Unknown column 'Recipes.id' in 'where clause'

The "Parent / child editing with Editor" blog post and rf1234 help in https://datatables.net/forums/discussion/51405/editor-parent-child-editing-in-modal so far has got me nearly there. The issue is that that works with a leftJoin. Hopefully I'm missing something simple.

Regards.

naspersgasp

Nevermind this question. My design won't work.

I'll have to create custom server side functions. As I don't want the user to edit the existing ingredients. They should be allowed to link them to the recipe only. But still use a datatable.

Regards.