Example of "group by" of database in server

[Clement Yew]

I'm learning about appery's DB which is based on mongodb. I need something like a one-to-many relationship. E.g : Level and Units in a high-rise apartment.
Each level will have many units. Each unit will hv few properties such as Size, Address, Owner name, etc. I want to retrieve a list of all the levels and their units.

I tried to embed the units array into the Level collection as an array.
However, I feel it's not suitable as the array will handle more and more properties in future. Hence, as mongodb is not a relational database, I want to try the pointer.

My idea :

1. Create 2 collections (Levels and Units).

2. Create pointer in Units collection to point to Level

3. Query for all units in Units collection (server code)

4. Group the units to levels using the pointer. Then, generate JSON array which is grouped by Level. (server code)

5. Return response for mapping to UI.

6. Preferred return result :

   {
   _id: "54332433423a23342",
   name: "Level 1",
   units: [
   {
   address: "A-1-1",
   size: "1200",
   owner: "John",
   },
   {
   address: "A-1-2",
   size: "1140",
   owner: "Doe",
   }
   ]
   }
   {
   _id: "54332433423a23342",
   name: "Level 2",
   units: [
   {
   address: "B-2-1",
   size: "1200",
   owner: "Alex",
   },
   {
   address: "B-2-2",
   size: "1140",
   owner: "Chang",
   }
   ]
   }

   Is there any example or can someone provide a sample server code to do step (4)?

[Serhii Kulibaba]

Hello,

Could you use sort by level field? https://devcenter.appery.io/documenta...

[Clement Yew]

Not suitable for my case. I need the units to be grouped into levels. Will then be used to draw a grid.

Here's my solution so far, but still face problems in the JSON mapping.

1. Use server code to retrieve all Units
   var retrieveObjectResult = Collection.query(DB_ID, "Units");

2. Use underscore.js to group units by "Level" field.
   var groupData = _.groupBy(retrieveObjectResult, function(obj) {
   return obj.level;
   });

3. Return the result as response and then perform mapping.

   The problem is with the return result JSON. This is what I'm getting now :
   {
   "1": [
   {
   "id": "5582a70be4b04bd716817d64",
   "name": "A-1-08",
   "createdAt": {
   "$date": "2015-06-18T11:10:03.403Z"
   },
   "updatedAt": {
   "$date": "2015-06-18T15:43:52.372Z"
   },
   "size": 700,
   "level": "1"
   }
   ],
   "2": [
   {
   "id": "5582a71de4b04bd716817d66",
   "name": "B-2-03",
   "createdAt": {
   "$date": "2015-06-18T11:10:21.199Z"
   },
   "updatedAt": {
   "$date": "2015-06-18T15:43:49.155Z"
   },
   "size": 1200,
   "level": "2"
   },
   {
   "id": "5582a714e4b04bd716817d65",
   "name": "B-2-01",
   "createdAt": {
   "$date": "2015-06-18T11:10:12.466Z"
   },
   "_updatedAt": {
   "$date": "2015-06-18T15:43:50.572Z"
   },
   "size": 1400,
   "level": "2"
   }
   ]
   }

   *In order to perform Mapping in the UI in a nice grid, I would require this format :
   [
   {
   "Level":"1",
   "Units":[
   {
   "name":"A-1-1",
   "size":"567"
   },
   {
   "name":"A-1-2",
   "size":"567"
   },
   {
   "name":"A-1-3",
   "size":"567"
   }
   ]
   },
   {
   "Level":"2",
   "Units":[
   {
   "name":"A-2-1",
   "size":"567"
   },
   {
   "name":"A-2-2",
   "size":"567"
   },
   {
   "name":"A-2-3",
   "size":"567"
   }
   ]
   }
   ]

   Problem is, how do I format the JSON properly so that I can map it?

[Evgene Karachevtsev]

Hello Clement,

Thank you for the update, glad it works!

[Clement Yew]

; JSONString += ""\""Units\"":"" + JSON.stringify(arr); if (i != (arr.length - 1)) { JSONString += ""}