More complex sort order

N1QL
1

I have a user Menu which can have multiple level and i would like to be able to output a correctly sorted format. To arrange my menu i have a parentid, a level and a position. So the first item has no parent and would be level 0 and item 0. Then a child has this item as parentid, a level of 1 and would be position 0 as its the first child.
Below is a sample of one of the Menu docs…

 {
    "_id" : "menu_item::db1c475c-4389-4fb4-9913-d432473a655e",
 	"_type" : "menu_item",
 	"name": "New Farm List",
    "tooltip": "New Farm List",
    "parent_id" :"menu_item::c8fe63ee-ee03-4e4d-9b65-3829609bdec6",
    "link": "FarmList",
    "image" : "help",
    "imagelibary" :"material",
    "visible": true,
    "admin": false,
    "class" :"",
    "level": 1,
    "position": 3
    
 }

So they way i want to return the query is to to return by level where 0 is the lowest and the position of 0, then all items that are a child of that parent and any possible children, sorted by position. Then the next item with level 0 and the next position and so on…
I am not sure if this is even possible or if I will have to keep using some external script to create the sorted output.

2

Hi @aponnath ,

Sorts by parent_id, within same parent_id sorts by level, within the same level sorts by position

SELECT d.*
FROM default AS d
WHERE .....
ORDER BY d.parent_id,  d.`level`, d.`position`;
3

That would be to simple :grinning:

Problem here is with your sample that it will sort by the parent_id for level 0 items and ignore the sort order. Also the parent of a level 2 menu item is not the parent_id of the level 0 but the one of level 1 parent.

4

Here is how i do it currently within my api


 const menu_mobile_sorted = async(input) =>{
   
    let max_level = 0;
    var sorted_by_level = {};
  
    input.forEach(i => {
      if (sorted_by_level.hasOwnProperty("level_" + i.level.toString())) {
        sorted_by_level["level_" + i.level.toString()].push(i);
      } else {
        sorted_by_level["level_" + i.level.toString()] = [i];
        if (i.level > max_level) {
          max_level = i.level;
        }
      }
    });
  
  
    for (level = max_level; level > 0; level--) {
      sorted_by_level["level_" + level.toString()].forEach(child => {
        const oldparent = sorted_by_level[
          "level_" + (level - 1).toString()
        ].filter(p => p.menu_item === child.parent_id)[0];
        const parentIndex = sorted_by_level[
          "level_" + (level - 1).toString()
        ].findIndex(p => p.menu_item === child.parent_id);
        let newparent;
        
        // Delete all Unused Child keys
        delete child.menu_item;
        delete child.parent_id;
        delete child.level;
        delete child.visible;
        delete child.position;
  
        if (oldparent.hasOwnProperty("item") && oldparent.item) {
          var mItems = [...oldparent.item, child];
          mItems.sort((a, b) => (a.position > b.position) ? 1 : -1);
          newparent = {
            ...oldparent,
            item: mItems
          };
        } else {
          newparent = {
            ...oldparent,
            item: [child]
          };
        }
        sorted_by_level["level_" + (level - 1).toString()][
          parentIndex
        ] = newparent;
      });
    }
    sorted_by_level.level_0.sort((a, b) => (a.position > b.position) ? 1 : -1);
  
    let newArray = sorted_by_level.level_0
  
    // Delete all Unused Keys in Parent Node
    for(var i = 0; i < newArray.length; i++) {
      delete newArray[i]['menu_item'];
      delete newArray[i]['parent_id'];
      delete newArray[i]['visible'];
      delete newArray[i]['level'];
      delete newArray[i]['position'];
  }
  
    // console.log(util.inspect(newArray, false, null, true /* enable colors */ ))
  
    return newArray
  
  }
5

Are you looking recursive hierarchy queries. level-2 parent is level-1, level-1 parent is level-0 (like organization, family tree)

At present we don’t have recursive hierarchy constructions

Only way is N1QL query with Self Referencing Hierarchy - The Couchbase Blog

external one is better