Order by limit within union query

SQL++
1

Hi,

I am trying to order by my result but somehow query is failing. What I am trying to achieve is, I am trying to get all published reviews at a place if no userid is passed but when passed my query becomes a union query but some query is when I try to apply limit and order by clause.Following is my quey.

SELECT reviewCount, place_name,event_id,place_id,description,pin_rate,place_lat,place_long,broadcast,status,created_on,user_id,pictures_url,videos_url,
resourceLocation,_ID,_CAS (
(SELECT (SELECT COUNT(`place_id`) AS `count` FROM `pins` WHERE place_id='MMI000' AND pin_type='review' AND status=1 ) AS reviewCount,p.place_name,
p.event_id,p.place_id,p.description,p.pin_rate,p.place_lat,p.place_long,p.broadcast,p.status,p.created_on,p.user_id,
ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.pictures END AS pictures_url,
ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.videos END AS videos_url,
'http://development.mapmyindia.com/explore/api/events/'||META(p).id AS resourceLocation,META(p).id AS _ID, META(p).cas AS _CAS FROM pins p 
USE INDEX(`place-review` USING GSI) WHERE p.place_id='MMI000' AND p.pin_type='review' AND p.status=1)
UNION 
(SELECT (SELECT COUNT(`place_id`) AS `count` FROM `pins` WHERE place_id='MMI000' AND pin_type='review' AND status=1 ) AS reviewCount,p.place_name,
p.event_id,p.place_id,p.description,p.pin_rate,p.place_lat,p.place_long,p.broadcast,p.status,p.created_on,p.user_id,
ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.pictures END AS pictures_url,
ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.videos END AS videos_url,
'http://development.mapmyindia.com/explore/api/events/'||META(p).id AS resourceLocation,META(p).id AS _ID, META(p).cas AS _CAS FROM pins p 
USE INDEX(`user_review` USING GSI) WHERE p.user_id='a6a8be4aeb4bf02df817b0ed1e6b276f' AND p.status IS NOT MISSING AND p.place_id='MMI000' 
AND p.pin_type='review') 
) AS result ORDER BY user_id='a6a8be4aeb4bf02df817b0ed1e6b276f' DESC, pin_create_date DESC LIMIT 10 OFFSET 0
2

Could you please more specific what is failing.
Also check ORDER BY user_id=‘a6a8be4aeb4bf02df817b0ed1e6b276f’ DESC. This makes this specific user results at the top.

3

Individual query is giving result but when I run them with union and use order by clause, query fails, what I want to know how do I write this kind of query in COUCHBASE. My scenario is I want to fetch all published reviews at a place when place_id is passed but when user_id is passed, I also want to fetch review of that user regardless of it is published or unpublished and I also want to order by result by user_id when user_id is passed. This is what I am trying to achieve with this query.I hope I made my question clear

4

Could you please more specific? Is it giving error? not giving any results? results are not what you expected?

Post the sample documents and expected out put.
You can also use two different queries (one user id passed and other not passed) instead of single query

If UNION query if you are looking second results on top you can try this

SELECT DISTINCT reviewCount, place_name,event_id,place_id,description,pin_rate,place_lat,
       place_long,broadcast,status,created_on,user_id,pictures_url,videos_url, resourceLocation,_ID,_CAS
FROM (
      (SELECT
             (SELECT COUNT(`place_id`) AS `count` FROM `pins` WHERE place_id='MMI000' AND pin_type='review' AND status=1 ) AS reviewCount,
             p.place_name, p.event_id,p.place_id,p.description,p.pin_rate,p.place_lat,p.place_long,
             p.broadcast,p.status,p.created_on,p.user_id,
             ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.pictures END AS pictures_url,
             ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.videos END AS videos_url,
             'http://development.mapmyindia.com/explore/api/events/'||META(p).id AS resourceLocation,META(p).id AS _ID, META(p).cas AS _CAS,
              0 AS sorder
       FROM pins p USE INDEX(`place-review` USING GSI)
       WHERE p.place_id='MMI000' AND p.pin_type='review' AND p.status=1
      )
      UNION ALL
      (SELECT
              (SELECT COUNT(`place_id`) AS `count` FROM `pins` WHERE place_id='MMI000' AND pin_type='review' AND status=1 ) AS reviewCount,
              p.place_name, p.event_id,p.place_id,p.description,p.pin_rate,p.place_lat,p.place_long,
              p.broadcast,p.status,p.created_on,p.user_id,
              ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.pictures END AS pictures_url,
              ARRAY 'https://s3.ap-south-1.amazonaws.com/mmi-uat-exploreimages/pin/event/original/' || v FOR v IN p.videos END AS videos_url,
              'http://development.mapmyindia.com/explore/api/events/'||META(p).id AS resourceLocation,META(p).id AS _ID, META(p).cas AS _CAS ,
               1 AS sorder
       FROM pins p USE INDEX(`user_review` USING GSI)
       WHERE p.user_id='a6a8be4aeb4bf02df817b0ed1e6b276f' AND p.status IS NOT MISSING AND p.place_id='MMI000' AND p.pin_type='review'
      )
     ) AS result
ORDER BY sorder DESC, pin_create_date DESC
5

@vsr1 thanks for the help I was doing something wrong, It was my mistake. I was able to resolve it.