Add SQL join functionality to views and DQL Merged

Adding join functionality to views would allow linked data to be brought together, avoiding things such as needing to duplicate data across documents.

A simple example would be an application where users belong to a "reporting area" - managers and directors want the data, stats, totals etc. in views categorised by area and name - ideally I would only store a "Unique User ID Reference" on each document and then use joins so the views can display other "user" information such as name and area, etc. I should not need to store the name and area values on each document.

So data joins would enable us to normalise data, avoid data duplication and simply data models, improve data consistency, avoid the need for overnight agents to update documents (to cover things like users changing name or area), etc.

Another example... lets say we have 3 different but related sets of documents (products, shops, stock levels). We could create a single view that joins the data spread across those 3 sets of records. Now any forms/agents/rest APIs etc. that need to use the consolidated data can find it in one place rather than looking in three places and then joining the data.

Even for things like standard weekly/monthly/business data reporting, having view indexes (containing joined data) which get incrementally updated as and when the underlying data changes should be more optimal than generating the data dynamically each time a report is run.

As for this being a hard operation - maybe it is - and maybe there would need to be some caveats or recommendations about how and when to use it, but I think it would be a powerful feature to have.

Hope this helps answer your question.

Why join for views? This is a hard operation. Maybe you need to correctly develop the views?