Loopback: How can I Implement recursive join using loopback self relations?
How to create parent child hierarchy using self-join in strongloop. I have created model name as menu.
menu.json
{
"name": "Menu",
"base": "PersistedModel",
"strict": false,
"idInjection": false,
"options": {
"validateUpsert": true
},
"properties": {
"MenuID": {
"type": "Number",
"id": true,
"required": true
},
"Name": {
"type": "string"
},
"ParentMenuID": {
"type": "Number"
}
},
"validations": [],
"relations": {
"menus": {
"type": "hasMany",
"model": "Menu",
"foreignKey": "ParentMenuID",
}
},
"acls": [],
"methods": {}
}
Table data like:
menuId Name ParentID
1 parent 0
2 child 1
3 grandchild 2
I tried REST API call using filter, but I am getting one level of data like http://localhost:3000/api/Menus/?filter[include]=menus
[
{
"MenuID": 1,
"Name": "parent",
"ParentMenuID": 0,
"menus": [
{
"MenuID": 2,
"Name": "child",
"ParentMenuID": 1
}
]
},
{
"MenuID": 2,
"Name": "child",
"ParentMenuID": 1,
"menus": [
{
"MenuID": 3,
"Name": "grandchild",
"ParentMenuID": 2
}
]
},
{
"MenuID": 3,
"Name": "grandchild",
"ParentMenuID": 2,
"menus": []
}
]
But I Expected Output Like:
[
{
"MenuID": 1,
"Name": "parent",
"ParentMenuID": 0,
"menus": [
{
"MenuID": 2,
"Name": "child",
"ParentMenuID": 1,
"menus": [
{
"MenuID": 3,
"Name": "grandchild",
"ParentMenuID": 2
}
]
}
]
}
]
Please suggest any idea or example.
katherdx
All 15 comments
hey @katherdx I think this is the same question with https://github.com/strongloop/loopback/issues/1760#issuecomment-150380078
Can you git clone that repo and see if the nest include works for you?
jannyHou
on 18 Feb 2016
@jannyHou I have single model only, see my model definition. I am not getting a clear idea from #1760. How can I make RESTAPI calls for recursive self join? Can you give any example. I am expected output is
[
{
"MenuID": 1,
"Name": "parent",
"ParentMenuID": 0,
"menus": [
{
"MenuID": 2,
"Name": "child",
"ParentMenuID": 1,
"menus": [
{
"MenuID": 3,
"Name": "grandchild",
"ParentMenuID": 2
}
]
}
]
}
]
hey @katherdx I created a sandbox to implement your expected nest output:
https://github.com/jannyHou/loopback-sandbox/tree/nest-include
Please check our filter doc to get more details. I think the point is using scope to get any nest models you want.
RUN sandbox:
npm install- open localhost:3000/explorer
- GET /menus with filter:
{
"include":{
"relation":"menus",
"scope":{
"include":{
"relation":"menus"
}
}
}
}
Response data:
[
{
"name": "menu1",
"id": 1,
"menus": [
{
"name": "menu2",
"id": 2,
"menuId": 1,
"menus": [
{
"name": "menu3",
"id": 3,
"menuId": 2
}
]
}
]
},
{
"name": "menu2",
"id": 2,
"menuId": 1,
"menus": [
{
"name": "menu3",
"id": 3,
"menuId": 2,
"menus": []
}
]
},
{
"name": "menu3",
"id": 3,
"menuId": 2,
"menus": []
}
]
@jannyHou Thanks for your great effort and I have finally two question
- How to write rest RESTAPI call for this logic.
- How to get 1st object from your output that is I need only below code (instead of using Array [0])
[
{
"name": "menu1",
"id": 1,
"menus": [
{
"name": "menu2",
"id": 2,
"menuId": 1,
"menus": [
{
"name": "menu3",
"id": 3,
"menuId": 2
}
]
}
]
}
]
@katherdx sorry for replying you late, quite busy recently.
I create two remote methods:
- nest: get nested results
- getElementById: get the first element
to implement your requirements, please check https://github.com/jannyHou/loopback-sandbox/blob/nest-include/common/models/menu.js
[1] How to write rest RESTAPI call for this logic:
You can do the same logic in
Model.find({filter}, function(err, results) {
// Process results here
})
in your case, it's:
Menu.find({
"include":{
"relation":"menus",
"scope":{
"include":{
"relation":"menus"
}
}
}
}, function(err, results) {
if(err) return callback(err);
callback(err, results);
});
[2] How to get 1st object from your output that is I need only below code (instead of using Array [0]):
I m not quite understand your requirement here, but I assume you are looking for findById:
Model.findById(id, {filter}, function(err, result) {
// Process result here
})
@jannyHou Thanks for response. I got answer.
katherdx
on 2 Mar 2016
@jannyHou I tried your example but its only return 2 levels of data. Self relation (using Recursive) is not working upto N level.
My table data like (sample data that is normal find() method result. )
[
{
"name": "menu1",
"id": 1,
"menuId": 0
},
{
"name": "menu2",
"id": 2,
"menuId": 1
},
{
"name": "menu3",
"id": 3,
"menuId": 2
},
{
"name": "menu4",
"id": 4,
"menuId": 3
},
{
"name": "menu5",
"id": 5,
"menuId": 4
},
{
"name": "menu1",
"id": 6,
"menuId": 0
},
{
"name": "menu2",
"id": 7,
"menuId": 6
},
{
"name": "menu3",
"id": 8,
"menuId": 7
},
{
"name": "menu4",
"id": 9,
"menuId": 8
},
{
"name": "menu5",
"id": 10,
"menuId": 9
}
]
I have pass below filter
{ "where": {"menuId": 0},
"include":{
"relation":"menus",
"scope":{
"include":{
"relation":"menus"
}
}
}
}
But I am getting only 2 levels of data. that is
[
{
"name": "menu1",
"id": 1,
"menuId": 0,
"menus": [
{
"name": "menu2",
"id": 2,
"menuId": 1,
"menus": [
{
"name": "menu3",
"id": 3,
"menuId": 2
}
]
}
]
},
{
"name": "menu1",
"id": 6,
"menuId": 0,
"menus": [
{
"name": "menu2",
"id": 7,
"menuId": 6,
"menus": [
{
"name": "menu3",
"id": 8,
"menuId": 7
}
]
}
]
}
]
My Expected OUTPUT like
[
{
"name": "menu1",
"id": 1,
"menuId": 0,
"menus": [
{
"name": "menu2",
"id": 2,
"menuId": 1,
"menus": [
{
"name": "menu3",
"id": 3,
"menuId": 2,
"menus": [
{
"name": "menu4",
"id": 4,
"menuId": 3,
"menus": [
{
"name": "menu5",
"id": 5,
"menuId": 4
}
]
}
]
}
]
}
]
},
{
"name": "menu1",
"id": 6,
"menuId": 0,
"menus": [
{
"name": "menu2",
"id": 7,
"menuId": 6,
"menus": [
{
"name": "menu3",
"id": 8,
"menuId": 7,
"menus": [
{
"name": "menu4",
"id": 9,
"menuId": 8,
"menus": [
{
"name": "menu5",
"id": 10,
"menuId": 9
}
]
}
]
}
]
}
]
}
]
@jannyHou I am waiting for your response.
katherdx
on 4 Mar 2016
@katherdx could you try filter as
{ "where": {"menuId": 0},
"include":{
"relation":"menus",
"scope":{
"include":{
"relation":"menus",
"scope":{
"include":{
"relation": "menus"
}
}
}
}
}
}
@jannyHou Thanks for your response. I am tried but its only return 3 level of data. my requirement is upto N level.
katherdx
on 4 Mar 2016
@katherdx If you know how many levels you want to do the nest query(like N levels), then just use
"include": {"relation": "menus"}
N times.
But I don't think you can get all nest levels if you dont know the certain level number.
jannyHou
on 4 Mar 2016
@jannyHou I cant determine levels, it should be N level .
katherdx
on 5 Mar 2016
@katherdx then I am afraid you could not get nest levels as many as possible.....
jannyHou
on 10 Mar 2016
@jannyHou thanks, I have another question, I wan to implement post and share like face book . please give any idea. please look this link https://github.com/strongloop/loopback/issues/2133
katherdx
on 10 Mar 2016
@katherdx I am closing this issue since the question turns clear after our chat above, and for the new issue you open, I discussed with richardpringle about the join query before, I believe if you want to use both tables(post and share), then his answer is perfect with current supported loopback features.
I just come up with another workaround but this would change your structure: use one table instead of two, like:
- A model called
News, Another model calledUser NewshasOneUseras postUserNewshasManyUseras sharedUser- use filter
"where": {
"or": [
{"postId": your_id},
{"sharedId": your_id}
]
}
@jannyHou I have a question about recursive model
Example
Food Model : Salmon Sushi Role
Menu : Japanese -> sushi -> sushi role
I want to get a list of food in each menu also sub-menu
Could you advise me how to implement and how to use REST API to receive a list of food in each category related ?
As I think path REST API : /api/food/category/:name
if I call any of this
/api/food/category/japanese
/api/food/category/sushi
/api/food/category/sushi_role
should return Salmon Sushi Role
Thanks
bellgetlinks
on 9 May 2018
Related issues
JoeShi
路
4Comments
imasif
路
3Comments
htmlauthor
路
3Comments
AnupDeshpande23
路
4Comments
germanamz
路
3Comments