Create sub quey use keys meta().id

SQL++
1

hi guys,

I want to create subquery and use keys with parent query meta().id like below:

SELECT META(category).id AS _ID,
       META(category).cas AS _CAS,
       category.*,
       (
           SELECT *
           FROM default USE KEYS META(category).id
           WHERE class = "com.ecommerce.inventory.model.product.Product" ) childs
FROM `default` category
WHERE category.class = "com.ecommerce.inventory.model.category.Category"

but result is empty.

regard.

2

In your parent query you are getting class Category
FROM default USE KEYS META(category).id
means you are getting same document again now you looking class Product which will eliminate.

Please post sample documents relation fields and what you need.

3

thanks,
sample data is like:

 {
        "_CAS": 1590152446941331456,
        "_id": "33f82e3a-990c-49d5-a682-775eff10fd03",
        "class": "com.ecommerce.inventory.model.category.Category",
        "createdts": 1589477362312,
        "creator": "xxx",
        "description": "blah blah blah",
        "parentId": "df90009c-d085-429d-af64-c5e8eb71fe4b",
        "status": "ACTIVE",
        "title": "blah blah blah"
    }, {
        "_CAS": 1589530253738377216,
        "_id": "df90009c-d085-429d-af64-c5e8eb71fe4b",
        "class": "com.ecommerce.inventory.model.category.Category",
        "createdts": 1589530302037,
        "creator": "e58b6777-2b76-4643-a51d-1f1d7ad6d2fe",
        "description": "blah blah blah",
        "status": "ACTIVE",
        "title": "blah blah blah"
    }

and after run query sample must like this:

{
"id": "df90009c-d085-429d-af64-c5e8eb71fe4b",
"title": "blah blah blah",
"status": "ACTIVE",
"description": "blah blah blah",
"childCounts": 2,
"productCounts": 30,
"childs": [
{
"id": "33f82e3a-990c-49d5-a682-775eff10fd76",
"title": "blah blah blah",
"status": "ACTIVE",
"description": "blah blah blah",
"productCounts": 0,
"createdts": "2020-05-14T21:59:22.312Z",
"creatorId": "xxx",
"parentId": "df90009c-d085-429d-af64-c5e8eb71fe4b"
}
],
"createdts": "2020-05-15T12:41:42.037Z",
"creatorId": "e58b6777-2b76-4643-a51d-1f1d7ad6d2fe"
}
4 
CREATE INDEX ix1 ON default(class, parentId) WHERE  class = "com.ecommerce.inventory.model.category.Category";

SELECT l.*, c AS childs
FROM default AS l
LEFT NEST default AS  c 
          ON META(l).id = c.parentId AND c.class = "com.ecommerce.inventory.model.category.Category"
WHERE l.class = "com.ecommerce.inventory.model.category.Category"

N1QL supports correlated subquery through parent document (doesn’t support generically). If needed use JOIN /GROUP BY.

5

after run query i get
[
{
“code”: 3000,
“msg”: “Ambiguous reference to field r.”,
“query”: “SELECT l.*,\n r AS childs\nFROM default AS l LEFT NEST default AS c ON META(l).id = r.parentId\n AND r.class = “com.ecommerce.inventory.model.category.Category”\nWHERE l.class = “com.ecommerce.inventory.model.category.Category””
}
]

6

updated previous post. replace r with c

1 Like
7

thank you so much its work :star_struck:

8

sorry and if i want to get childs in 5 level what i need to do ?

9

N1QL doesn’t have support for CONNECT BY. You can write that through the application similar technique described in the blog. cc @binh.le

1 Like