Use variable from parent query in correlated query


#1

Hi,

I am using the following query to fetch details of a report along with that I am fetching the count of all users who have liked this report. In order to achieve that I using a subquery. Following is my query

SELECT p.place_name,p.event_id,p.place_id,p.pin_story,p.place_lat,p.place_long,p.cat_name,p.child_cat_name,p.broadcast,p.status,
p.pin_create_date,ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.images END AS pictures,
ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.videos END AS videos,
'http://development.mapmyindia.com/api/v2.0.0/report/'||META(p).id AS resourceLocation,p.user_id,u.name,u.user_photo,META(p).id AS _ID, 
META(p).cas AS _CAS,(SELECT COUNT(*) AS count,`pin_id` FROM events USE INDEX(`like` USING GSI) WHERE `pin_id` IN [META(p).id] AND type="like" 
AND status=1 GROUP BY pin_id) AS `like_count` FROM pins p USE INDEX(`#pins` USING GSI) INNER JOIN users AS `u` ON KEYS `p`.user_id WHERE META(p).id='a5d3c6c4-9815-4b41-ba09-82ce0699d839' 
AND p.pin_type='report'

Error which I am getting is

[
  {
    "code": 5010,
    "msg": "Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM events."
  }
]

#2

Your subquery is correlated due to pin_id IN [META§.id]. Correlated subquery requires USE KEYS clause. In your case in parent query predicate META§.id is equality predicate you can try this.

SELECT p.place_name,p.event_id,p.place_id,p.pin_story,p.place_lat,p.place_long,p.cat_name,
       p.child_cat_name,p.broadcast,p.status, p.pin_create_date,
       ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.images END AS pictures,
       ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.videos END AS videos,
       'http://development.mapmyindia.com/api/v2.0.0/report/'||META(p).id AS resourceLocation,p.user_id,u.name,u.user_photo,META(p).id AS _ID,
       META(p).cas AS _CAS,
       (SELECT COUNT(1) AS count,`pin_id`
           FROM events USE INDEX(`like` USING GSI)
           WHERE `pin_id` IN ['a5d3c6c4-9815-4b41-ba09-82ce0699d839'] AND type="like" AND status=1
           GROUP BY pin_id) AS `like_count`
FROM pins p USE INDEX(`#pins` USING GSI)
INNER JOIN users AS `u` ON KEYS `p`.user_id
WHERE META(p).id='a5d3c6c4-9815-4b41-ba09-82ce0699d839'
AND p.pin_type='report';

#3

Thanks @vsr1

What if I do not know meta§.id, What I mean to say is that when this is my condition WHERE p.place_id=‘MMI000’ AND p.pin_type=‘report’ how do I achieve same.


#4

If pin_id of type = “like”, pin_type= “report” same replace that.

If not
change your data mode store the document id of type = “like” in other document

OR

Do non correlated query and apply array collection.

SELECT p.place_name,p.event_id,p.place_id,p.pin_story,p.place_lat,p.place_long,p.cat_name,
       p.child_cat_name,p.broadcast,p.status, p.pin_create_date,
       ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.images END AS pictures,
       ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.videos END AS videos,
       'http://development.mapmyindia.com/api/v2.0.0/report/'||META(p).id AS resourceLocation,p.user_id,u.name,u.user_photo,META(p).id AS _ID,
       META(p).cas AS _CAS,
       FIRST v.`cnt` FOR v IN lk WHEN v.pin_id = META(p).id END AS `like_count`
FROM pins p USE INDEX(`#pins` USING GSI)
LET lk = (SELECT COUNT(1) AS cnt,`pin_id`
           FROM events USE INDEX(`like` USING GSI)
           WHERE type="like" AND status=1
           GROUP BY pin_id)
INNER JOIN users AS `u` ON KEYS `p`.user_id
WHERE META(p).id='a5d3c6c4-9815-4b41-ba09-82ce0699d839'
AND p.pin_type='report';