I am working on a query which will get me a list of all contacts which i can page thru based on what data is past in from query to display in the grid. So far so good. I now have a field in my grid which is called “LastActivity” and that could be either a email, a phone call or other interactions. All of these are stored in individual docs which have a parentId which is equal the contact docId. So how can i get the latest activity based on multiple subqueries and be able to get back the date, the ID and the _type so i can display type and date and use the id to open details if user clicks on this.
Please provide sample documents and expected output.
SELECT c.*, d.* FROM default AS c JOIN default AS d ON KEY d.parentId FOR c WHERE c.type = "contacts" AND d.type = c.LastActivity; CREATE INDEX ix1 ON default(parentId);