Complex queries based off of items in an array
I'd like to be able to:
.widthIndex("by_userId_primarySkills_secondarySkills", q => q.includes("primarySkills", arrayItem))
I have some complex query requirements and the above would help immensely!
But perhaps there's another way to efficiently accomplish this?
I have a users table and a separate table skills
Each skills document has a type field that can hold one of three types primary, secondary, and lookingFor. Each user can have up to 3 primary types/docs, 3 secondary types/docs, and 6 lookingFor types/docs.
With this schema, it's challenging to write a complex query without a lot of application logic and costly ($$) additional reads. But if I could denormalize the data for users to have users.primarySkills where primarySkills is an array, and if I could query at the array item level, that would be a seamless solution9 Replies
I should note that the above is a simplified example, mainly to illustrate the problem. i mention this because the following workaround might not make complete sense with the above at face value.
my current workaround is to create a separate table purely for querying purposes and use a join thereafter. Problem is, I would have to forego querying of
secondarySkill , essentially limiting my end user's ability to filter optimally.
Essentially, im trying to find a way to write efficient queries where the fields i want to query against live on two separate tables.Hey @zid, I think our answer is that Convex is a relational database, and performing joins should be totally ok. You shouldn't really have to worry about the cost (although our current pricing with its minimum document size is perhaps steering you in the wrong direction).
convex-helpers have some handy utils for performing joins.
I'm curious what is the actual query you're trying to write (I understand I think the data model, but it's not clear what you're trying to query).
Hey Michal! Sorry for the delay, I just saw this.
I'm actually in between errands atm so simply touching base but I will say that for this specific issue, I re-made the entire experience so not too concerned with this one anymore but i will try to share briefly from what i recall.
First, from a user perspective, I want the user to be able to query users withIndex essentially from two tables:
-
.widthIndex("by_boolOne_boolTwo_primarySkill_secondarySkill"
- boolOne is from users, and it is a boolean
- boolTwo is from users also, and it is a boolean
- primarySkill is a record from skills, that can be of 2 types primary or secondary. type: v.union(v.literal("primary"), v.literal("secondary"))-
secondarySkill is a record from table 2, ...
Thanks @zid! How would you describe the query without referring to the code? For example: "I want the logged in user to see the list of primary skills they have". I'm still a bit unclear on the use case.
there is apage with a list of users.
i want each user to be able to filter these users using a variety of options.
These options can be stacked on top of each other.
An example could be, I want a list of users with:
- a primary skill of {value}
- a secondary skill of {value}
- is looking for {value} opportunities
ecommerce stores could be more illustrative as their filtering menus involve a wide array of checkboxes/filters that can be stacked on top of each other.
I see. Are "all" the filters required, or "some"?
If "some", this can be done as a union of three individual joins.
If "all", you could either perform an intersection of the individual joins, or have the data denormalized in a table:
with all the permutations. Then you could use
withIndex.
I think you have probably figured this all out already. "Joins" (in JS) and denormalization plus withIndex are the tools at your disposal for efficient querying, and you could also filter in JS for a less efficient approach.
Any other database would use one of these under the hood, with Convex you get to decide what approach fits your app the best.its not clear to me what three individual joins means precisely. Do you mean make three separate queries and concatenate them together with my own app logic?
the latter is the approach i've been trying to figure out, but the main challenge iswriting an elegant algorithm that handles all the potential permutations. Specifically, I need to account for scenarios where I essentially want to ignore primary_skill, thus including all users regardless of their primary skill. Same with the other arguments (secondarySkill, etc).
hm by tweaking the experience a little, arguably somewhat cleaner/more intuitive, i reduced the permutations into a manageable if/else statement tree. Still have to test
Yeah, making seperate database
queryies and doing a union or intersection in JavaScript is what I meant.
To ignore primary_skill you could set it to null in the denormalized table, maybe?Yep, null is one of the allowed values.
What helped was to introduce a single piece of state client-side to be responsible for managing a specific type/category of choices which points to multiple fields in the document.
Appreciate your help Michal, thank you!