How to JOIN Documents with COUNT() Aggregate function and using ORDER BY

SQL++
,
1

Hi CouchBase Gurus,

Need some help with N1QL Queries I’m a bit confuse with my query.
How do I JOIN documents as well as I can use COUNT() aggregate function and will be able to use ORDER BY

Here is my user documents:

[
   {
    "id": "user::032",
    "email": "e.bandido@gmail.com",
    "firstName": "Edward",
    "lastName": "Bandido",
    "type": "user"
   },
   {
    "id": "user::033",
    "email": "r.stew@gmail.com",
    "firstName": "Randy",
    "lastName": "Stew",
    "type": "user"
   },
   {
    "id": "user::043",
    "email": "r.stew@gmail.com",
    "firstName": "Eddie",
    "lastName": "Gordon",
    "type": "user"
   }
]

Here is my task documents:

[
   {
    "assignedTo": "user::032",
    "status": "pending",
    "name": "Show and Tell"
    "type": "task",
   },
   {
    "assignedTo": "user::033",
    "status": "pending",
    "name": "Show and Tell"
    "type": "task",
   },
   {
    "assignedTo": "user::043",
    "status": "pending",
    "name": "Show and Tell"
    "type": "task",
   },
   {
    "assignedTo": "user::033",
    "status": "pending",
    "name": "Painting Session"
    "type": "task",
   },
   
]

My goal here is to count the number of pending tasks that the users have. Also, I can ORDER BY lastName and totalPendingTask.

Here is my expected result:

[
   {
    "id": "user::032",
    "email": "e.bandido@gmail.com",
    "firstName": "Edward",
    "lastName": "Bandido",
    "type": "user"
    "totalPendingTask": 1
   },
   {
    "id": "user::033",
    "email": "r.stew@gmail.com",
    "firstName": "Randy",
    "lastName": "Stew",
    "type": "user",
    "totalPendingTask": 2
   },
   {
    "id": "user::043",
    "email": "r.stew@gmail.com",
    "firstName": "Eddie",
    "lastName": "Gordon",
    "type": "user",
    "totalPendingTask": 1
   }
]

Here the query I created initially but it doesn’t work :frowning:

SELECT
  META(`users`).id,
  `users`.firstName,
  `users`.lastName,
  `users`.email,
  COUNT(`task`.totalPendingTask) AS totalPendingTask
FROM `bucket` AS `users`

LEFT JOIN `bucket` AS `tasks`
  ON META(`users`).id = `tasks`.assignedTo 
  AND `tasks`.`type` = 'task'
  AND `tasks`.tenant = 'tenant::tenant01'
  AND `tasks`.status = 'pending'

WHERE `users`.`type`='user' 
AND `users`.tenant='tenant::tenant01'
AND (ANY territory IN `users`.territories SATISFIES territory IN  ["tenant::tenant01::territory::0029"]  END)
AND (ANY userRole IN `users`.roles SATISFIES userRole = 'role::sales representatives' END) 
ORDER BY LOWER(`users`.lastName) ASC

Need your help guys thanks.

2

Hi @lyndondonz ,

Add GROUP BY users will get results

SELECT
  META(`users`).id,
  `users`.firstName,
  `users`.lastName,
  `users`.email,
  COUNT(`task`.totalPendingTask) AS totalPendingTask
FROM `bucket` AS `users`

LEFT JOIN `bucket` AS `tasks`
  ON META(`users`).id = `tasks`.assignedTo 
  AND `tasks`.`type` = 'task'
  AND `tasks`.tenant = 'tenant::tenant01'
  AND `tasks`.status = 'pending'

WHERE `users`.`type`='user' 
AND `users`.tenant='tenant::tenant01'
AND (ANY territory IN `users`.territories SATISFIES territory IN  ["tenant::tenant01::territory::0029"]  END)
AND (ANY userRole IN `users`.roles SATISFIES userRole = 'role::sales representatives' END) 
GROUP BY  `users`
ORDER BY LOWER(`users`.lastName) ASC
1 Like
3

@vsr1

Thanks man… appreciated… :slight_smile: