Multiple questions on querying

SQL++
,
1

Hello. I have started to work with N1QL and I am struggling to set up my database models and query on them. Right now, all of the documents I created are in the same bucket, and their types are specified in the docType field.

In this example, there are multiple entities that relate to one another, described as follows:
There are Stores, where Clients can make Purchases and Sales. The associations between Stores and Clients is stored under StoreClients, whereas Purchases and Sales have a reference to a Client and a Store.

A question that one could ask, is “Show all Client information of Store ‘A’ and their purchases and sales. If clients don’t have purchases/sales, show the clients anyway”. I am able to get information about the clients of Store A, along with their information in the next query:

CREATE INDEX idx_store ON bucket(storeId);

SELECT c.name
FROM bucket as s 
LEFT OUTER JOIN bucket as sc ON KEY sc.storeId for s
LEFT JOIN bucket as c ON KEYS sc.clientId
WHERE s.docType='Store'
AND sc.docType='StoreClients'
AND c.docType='Client'
AND s.name="A"
ORDER BY c

…but how can I cross reference this information with the information about purchases and sales? So, here are a some questions regarding this example:

  1. Is there a problem in querying the documents in the same bucket? Should I have a bucket per document type in order to facilitate N1QL queries?
  2. Is it a good practice to try and fit all on one query alone? Or are there any alternatives I am not aware of?
  3. How can I cross reference this information with the information about purchases and sales in the previous example?

Thank you in advance for your help!
Best regards,
Manuel

2

Hi @manusyone,

  1. Storing different documents of different types in a single bucket and querying them is fine. It is a best practice.
    N1QL itself can query buckets in a single or multiple buckets seamlessly. Since you have document type in each document and using it in query, you’ll be fine. Just create the right indices.
  2. You can have as many joins and filters you need in a single query. The best practices (filters, indices on the filters, composite indices) from RDBMS apply here as well.

See this and other N1QL articles @DZone as well
https://dzone.com/articles/understanding-index-scans-in-couchbase-n1ql-query

1 Like
3

Hi @keshav_m, thank you for your answer.

Regarding answer 1, does it make sense to create indices in fields with same name in different types? For example:

CREATE INDEX idx_store ON bucket(clientId) WHERE docType='Purchase' OR docType='Sales';

I have edited the previous post to contain the question 3, based on your answer for question 2.

Thank you in advance!

4

Hi @manusyone,

My recommendation is to create separate indices for (docType = ‘Purchace’), (‘docType’ = ‘Sales’).

This index below will ONLY be used if you have the predicate (docType=‘Purchase’ OR docType=‘Sales’) AND predicate on clientId.

CREATE INDEX idx_store ON bucket(clientId) WHERE docType=‘Purchase’ OR docType=‘Sales’;

5

@keshav_m thank you for your help, it makes sense.

How about question 3? Could you enlight me on how could do it?

Thank you in advance

6

Hi @manusyone,

The cross reference is done via storing the document id in the child documents.

E.g. If store is the parent doc, the the sales doc, you can store the store document key.
Then you can join the sales and stores using that key.

7

Hi @keshav_m, the problem here is that both the sales and the purchase doc have references to the storeId and the clientId. Extending the first example:

CREATE INDEX idx_store ON bucket(storeId) WHERE docType="StoreClients";
CREATE INDEX idx_purchase ON bucket(storeId) WHERE docType="Purchase";
CREATE INDEX idx_sales ON bucket(storeId) WHERE docType="Sales";

SELECT c.name, ARRAY_AGG(p),  ARRAY_AGG(sl)
FROM bucket as s 
LEFT OUTER JOIN bucket as sc ON KEY sc.storeId for s
LEFT JOIN bucket as c ON KEYS sc.clientId
LEFT OUTER JOIN bucket as p ON KEY p.storeId for s
LEFT OUTER JOIN bucket as sl ON KEY sl.storeId for s
WHERE s.docType='Store'
AND sc.docType='StoreClients'
AND c.docType='Client'
AND p.docType='Purchase'
AND sl.docType='Sales'
AND s.name="A"
GROUP BY c

The result should be the client information, along with purchases and sales.
I can join all the documents, but both the purchases and sales should reference the clientId in this query. Can you help me with my query given the requirements? Or should this be as simple as WHERE sl.clientId=c.docId?