Suquery within same bucket



SELECT l.created_on,l.is_public,l.list_id,l.list_name,(select COUNT(id) AS count FROM lists USE KEYS l.list_id WHERE type=“list_item” AND user_id=
“a6a8be4aeb4bf02df817b0ed1e6b276f” AND status=1 AND list_id = l.list_id)[0] AS item FROM lists AS l USE INDEX(list USING GSI) WHERE l.type=“list”
AND l.list_id NOT IN[“7”] AND l.user_id IN[“a6a8be4aeb4bf02df817b0ed1e6b276f”,“dceec1f61ed0676bcf7fe7440e618888”] ORDER BY l.list_id=“2” DESC,
l.list_id=“3” DESC,l.list_id=“6” DESC, l.created_on DESC;

This is my query, I am trying to fetch all list created by any user with the count of items added to that list.Both list and listitem are in same bucket. I am trying to fetch result in same query.

There are items corresponding to a list but it is returning 0

            "created_on": "2016-05-10 10:52:28",
            "is_public": 0,
           "item": {
             "count": 0
           "list_id": "2",
           "list_name": "Favourites"


Check out the Data and query . It might be giving right. The subquery use USE KEYS. It is working on single document and filters might eliminated the document that is why count gives 0


@vsr1 no when I run subquery separately with list_id =2 it gives me the result, I mean in the database there are results corresponding to list id = 2. Is it that USE KEY is doing some kind of join internally. They both are in same bucket differentiated with type only, one has type = list and other has type = list_tem.


With out USE KEYS it consider all the document in the bucket applies predicate.
with USE KEYS it only considers the document present in USE KEYS and applies predicate.


@vsr1 so in order to run subquery I need to know the document key of list_items which has list id = 2 in it, but as you can see that I am fetching all lists of any user and list id will be dynamic, how would I know what are the document keys of all list items which has those list id, Is there any other way to achieve the same result.


If you are doing correlated subquery you need USE KEYS.

If you need the count you need to use GROUP BY queries.


I am fetching all lists as you can see, each list has some item in it. I am trying to each count of all items added to a list. they both are saved in the same bucket named list. documents are differentiated with the help of type parameter one is type and the other is list_item. How do I fetch count of all list Item of a particular list. I don’t think group by can solve my problem.


Make subquery non correlated GROUP by list_id for all list. This produces ARRAY then do array operations.

SELECT l.created_on,l.is_public,l.list_id,l.list_name, FIRST v.count FOR v IN alistitems WHEN v.list_id = l.list_id END AS item
alistitems = (SELECT list_id, COUNT(id) AS count FROM lists WHERE type="list_item"
              AND user_id = "a6a8be4aeb4bf02df817b0ed1e6b276f" AND status = 1
              GROUP BY list_id)
WHERE l.type="list"
AND l.list_id NOT IN["7"] AND l.user_id IN ["a6a8be4aeb4bf02df817b0ed1e6b276f","dceec1f61ed0676bcf7fe7440e618888"]
ORDER BY l.list_id="2" DESC, l.list_id="3" DESC, l.list_id="6" DESC, l.created_on DESC;