CODEWITHSUNDEEP
javascriptnode.jsarraysjson
Anderson Dev
Anderson Dev

Reputation: 275

Nested array with knex and node.js

I currently have a table in the database that I use to register the system menus, it is called menu and has a field id_menu, dsc_menu and parent_item. Id_menu is autoincrement and parent_item receives the id_menu of its main menu items. In this case, they become submenus. It turns out that I have tried in many ways to generate a json by nesting with my query, that is, the main menu items and if there is one, a node containing the submenus of that item in question. I thought about making a first query, which will bring me only the main menus, then I would do a foreach in this array, take the id_menu and make a new query now only for the items that have parent_item = id_menu. Then I could concatenate this data again and transform it into a complete array. I made an outline below my idea, however, it doesn't work.

// here I take all the parent menus
var result = await knex.raw("SELECT * FROM menus WHERE parent_item is null");

result[0].forEach(element => { 

    // Here I need to use the parent menu id to get the child menus and add them to a new array that will be inside the "submenus" element of the main array.
    if(element.num_sub_menus > 0) {
         var jsonSubMenus = await knex.raw("SELECT dsc_menu FROM menus WHERE parent_item = ?", element.num_sub_menus);
         // if I try to call the knex again here, using await, it gives an error and if I don't use awaita it returns an empty promisse.
    }

    resultado += '{';
    resultado += '"id_menu_admin": ' + element.id_menu_admin + ',';
    resultado += '"dsc_menu": "' + element.dsc_menu + '",';
    resultado += '"parent_item": ' + element.parent_item + ',';
    resultado += '"rota": "' + element.rota + '",';
    resultado += '"icone": "' + element.icone + '",';
    resultado += '"num_sub_menus": ' + element.num_sub_menus + ',';
    resultado += '"submenus": ['+jsonSubMenus+']';
    resultado += '},';}
);

For subqueries, either return an empty promise if I don't use await, as an example, or give a compilation error if I use await.

I imagine that there must be a more professional way of doing this, bringing a nested array through a single query. Remembering that my menu items and submenu are in the same table.

Upvotes: 0

Views: 674

Answers (1)

CertainPerformance
CertainPerformance

Reputation: 370989

While you could use a for loop instead of forEach so that await would be permitted inside, it'd be better to use a somewhat different approach - use Promise.all so that all submenus are queried at once, in parallel, then construct the resultado.

It looks like resultado is JSON, so you should use an object literal and then JSON.stringify instead of manually concatenating. Something along the lines of:

const getSubmenus = elm => elm.num_sub_menus > 0
  ? knex.raw("SELECT dsc_menu FROM menus WHERE parent_item = ?", elm.num_sub_menus)
  : null;
const withSubmenus = await Promise.all(result[0].map(
  elm => Promise.all([element, getSubmenus(element)])
);
const resultArr = withSubmenus.map(([e, submenus]) => ({
  id_menu_admin: e.id_menu_admin,
  dsc_menu: e.dsc_menu,
  // ...,
  ...(submenus ? { submenus } : {})
}));
const resultJSON = JSON.stringify(resultArr);

Upvotes: 1

Related Questions