allen•2y ago

Also when querying a table is it

Also, when querying a table, is it possible to populate a relationship so that the returned result is a nested object of the related data? Or is it really best practice to iterate over the resultset and execute a query for each FK and append it?

7 Replies

allenOP•2y ago

(Hopefully my pestering is helping improve docs -- I am checking there first. There is something relational-ish in the Complex Queries section, but doesnt quite speak to this case.)

sshader•2y ago

Are you asking about something like

defineSchema({
    messages: {
        author: s.id("authors"),
        body: s.string(),
    },
    authors: {
        name: s.string()
    }
})

defineSchema({
    messages: {
        author: s.id("authors"),
        body: s.string(),
    },
    authors: {
        name: s.string()
    }
})

and wanting to return something like [{ _id: ..., body: "Hello", author: { _id: ..., name: "Sarah" }}, ...]? (omitting _creationTime) If so, you can't currently do this in a single db.query and have to execute a query for each author and massage them together in JS

allenOP•2y ago

Yup, that was my question. Is it recommended to do that merging within the function or client side to optimize for reactivity and caching strategies?

ballingt•2y ago

Server-side within the function generally. Convex's full page consistent data views mean you could safely do this on the client (foreign keys like this can be missing if tables are retrieved at different timestamps in other systems) but in my experience even though it's possible, it's always simpler to do it in the Convex function. Convex queries returning compound or synthetic objects unlocks the power here for query functions to replace both SQL and ORM.

allenOP•2y ago

Makes sense. It would be great to have this abstracted into the query language though. Being that its recommended to keep document sizes small and use relationships, this could quickly turn into some gnarly code if your relationships result in several layers of nesting. Plus a bit annoying to have to re-spec the doc type to extend in the populated items. Thinking something like: const book = await db.get(bookId).populate(['author', 'publisher', 'publisher.category']) Also unsure of perf issues of potentially running dozens of individual queries to achieve this for a page of results.

sp85•2y ago

+1 for the above ^

alexcole•2y ago

Yep that totally makes sense! Thanks for the feedback! I think having some syntactic sugar for this type of thing would be really nice. We also plan to add JOIN syntax some day (which could end up being similar to this). Under the hood though, the performance would be basically the same as doing this in JS. Because query and mutation functions run within your database this is actually quite efficient. The only performance thing to look out for is to make sure to parallelize loading the referenced documents. You can do this by using Promise.all like https://github.com/get-convex/convex-demos/blob/main/users-and-auth/convex/listMessages.js. This will enable Convex to load all the referenced documents in parallel.

Also when querying a table is it

Did you find this page helpful?