Querying across tables
I am trying to build a dynamic query that can filter records across multiple tables
For example, I have a companies table with name, headcount, and userId
Then I have a crmCompanies table with companyId (relation), crmId, and tier
I want to build a query for companies where userId = X and tier = Y without having to denormalize or fetch all the records in one of the tables
What are my options?
17 Replies
Thanks for posting in <#1088161997662724167>.
Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets.
- Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.)
- Use search.convex.dev to search Docs, Stack, and Discord all at once.
- Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI.
- Avoid tagging staff unless specifically instructed.
Thank you!
what would save you so much time and effort is to index the table, and search by the index for that specific user, and Running a Promise.all or .settled to grab the next few data for that specific user,
your index should look something like this:
companyTable.index("by_someData",["userId", "tier"])
and grab the data as:
await ctx.db.query("companyTable").withIndex("by_someData", q=>q.eq("userId", userId).eq("tier",tierId)).unique()
that would grab the only instance that matches the userId, you could change it for first or collect if you expect the user to have multiple tier instancesyou could also look into the filter function from convex-helpers if you don't think this is what you're looking for
https://stack.convex.dev/complex-filters-in-convex#collecting-only-some-of-the-results
Using TypeScript to Write Complex Query Filters
There’s a new Convex helper to perform generic TypeScript filters, with the same performance as built-in Convex filters, and unlimited potential.
Sorry I don’t think that solves it. The whole point is — what if I want to run an indexed query across multiple tables? I don’t want to have all my fields in a single table
I may have missudestood you, does a many to many relation make sense to your case?
if so, I'd query one table, and then run a promise.all to find the other table data (from the second table) and merge the results I'm looking for
Sorry I wasn’t clear.
I have 2 tables that have a 1 to 1 relation. I want to give the user the ability to query using fields that exist across BOTH tables.
So in my example above, the user should have the ability to query for companies where userId = X and tier = Y (even thought userId is on table #1 and tier is on table #2)
If they were querying with EITHER userid or tier, we could use a simple index and return it. But because user id and tier live in separate tables, this is not as straight forward. In normal SQL, you’d use a join.
Does that make sense?
sooo... how do you expect to get either if you don't have a point of connection? you've confused me so much, but i can send you some resources
https://stack.convex.dev/translate-sql-into-convex-queries
https://stack.convex.dev/relationship-structures-let-s-talk-about-schemas
query first table => tables exists => query second table based on the known data from the first table,
if you don't have a key that connects tables, I'm not sure how to implement this
Sorry not sure why this is so hard to grasp:
You have two tables:
companies {
name: v.string(),
userId: v.id("users")
}
crmCompany {
companyId: v.id("companies")
tier: v.string()
}
I want to be able to query for companies where:
- userId = X
- tier = Y
Without having to fetch ALL records in either table (i.e. querying companies where userId = X and then manually fetching crmCompany for each record, etc.)
Does that make it clear?
Yep, this makes sense, I would assume you know the userId and tier and want to either return one or presumably paginate the query,
let me think of the most efficient way to do it
The only solution I've found that is better than O(n) is using streams -- not sure if that's the best option
that's a good option too!
is there a better one?
the next best thing would be to use table joins like i mentioned before, with pagination if you're expecting t fetch a lot of items
so you would get the first 20 for instance, get all things related to it, then return data
if you're expecting 1 or two, with table indexes is best in my honest opinion
thats O(n)
not ideal
will have a lot of records in that first query
@cone You’ll want to denormalize into a small join/edge table so you can index across both fields instead of scanning.
schema.ts
companyByUserTier: {
userId: v.id("users"),
tier: v.string(),
companyId: v.id("companies")
}
.index("by_user_tier", ["userId","tier"])
your query
ctx.db.query("companyByUserTier")
.withIndex("by_user_tier", q => q.eq("userId", userId).eq("tier", tier))
.collect()Hey @Hmza , how would you maintain the denormalized table in that case? Triggers/wrapping mutations on either table to update this table accordingly?
good question. there's a convex-helpers package that has "custom-functions"
https://www.npmjs.com/package/convex-helpers#custom-functions
so we can define our own query/mutation and pre add the data to normalized table everytime we work with a ceratin update or even query.