Suquery within same bucket

SQL++
1

Hi,

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"
         }
   ]
2

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

3

@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.

4

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.

5

@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.

6

If you are doing correlated subquery you need USE KEYS.

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

7

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.

8

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
FROM lists AS l USE INDEX(list USING GSI)
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;