Getting a document that doesn't have a reference in another document

SQL++
,
1

Hello,

I have a document type Client and a document type Product. I also have a document type ClientProduct that has the relation between a Client and a Product by their ID. The idea is when a Client buys a Product this association is stored in the ClientProduct document. With this model I can easilly get all the Products a given Client bought. However, I’m not sure the approach I should follow in order to get only the Products that weren’t bought by that Client in particular.

  1. How can I get only the Products that weren’t bought by a given Client?

Thanks in advance. :slight_smile:

2

Two options. This is the same as in a relational database.

(1) Use EXCEPT.

(2) Use NOT IN.

See the N1QL documentation on both.

3

You can perform the following queries:

CREATE INDEX on bucket("Client::" || clientId) 
WHERE docType="ClientProduct";

SELECT p.* 
FROM bucket p 
WHERE docType="Product" 
AND p.docID NOT IN ARRAY r.productId FOR r IN (
  SELECT cp.productId
  FROM bucket c USE KEY "Client::" || $1
  LEFT OUTER JOIN bucket pc ON KEY "Client::" || cp.clientId FOR c
  WHERE c.docType="Client"
  AND cp.docType="ClientProduct"
) END;

where $1 is you given client.

Do you agree with this approach? @geraldss

4

I agree as long as it works :slight_smile:

Please try it out.

5

@geraldss, I was already using NOT IN, but I was doing the array part wrong.

Thanks, @manusyone! It worked! :smiley:

1 Like