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
In this example, there are multiple entities that relate to one another, described as follows:
Clients can make
Sales. The associations between
Clients is stored under
Sales have a reference to a
Client and a
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:
- 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?
- Is it a good practice to try and fit all on one query alone? Or are there any alternatives I am not aware of?
- 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!