Following query gives result but the subquery part is coming empty for every user.What should be done to get the result from following query.
SELECT pin_id,old_pin_id,place_id,place_name,pin_story,pin_rate,cat_name,child_cat_name,pin_create_date,
place_lat,place_long,pin_type,images,(SELECT t2.user_photo,t2.name FROM users t2 use keys t1.user_name WHERE t2.user_name= t1.user_name) AS user
FROM pins t1 where user_name=‘mustanish123’ ORDER BY pin_create_date DESC;
@vsr1 I do not have user_name as primary key in my another bucket.both buckets have user_name as its content. I am basically trying to implement subquery to fetch users detail in same so that I do not have to hit database again. Can you suggest what should be done to minimize database query.
SELECT pin_id, old_pin_id, place_id,place_name, pin_story, pin_rate, cat_name, child_cat_name,
pin_create_date, place_lat, place_long, pin_type, images,
(SELECT t2.user_photo, t2.name
FROM users t2
WHERE t2.user_name = “mustanish123”
) AS user
FROM pins t1
WHERE t1.user_name = "mustanish123"
ORDER BY t1.pin_create_date DESC;
Please check out your needs. Assume if outer query produces 1000 document subquery results are same for all of them and transporting same results part of each of document will be over head.
This case is working when I am reading data for one user, but what If I am not passing any username in where clause and wants to read all entry with corresponding name and photo as these may change by user from profile section.