Just wondering if there's some kind of
Just wondering if there's some kind of way to db.insert() uniquely ? So, the table would have unique docs & if already exist, return an error ?
16 Replies
unique based on all fields or a single field?
on all fields.
Basically, it's a relationship table with fields userId & ideaId.
I think I'm not saying it correctly, sorry.
So there should be only one doc with same userId & ideaId
You have to query to check for an existing doc that matches first.
1. Make an index that includes all fields that must be unique
2. Query with that index, use
.unique() to collect 0 or 1 results, will throw if more
3. If there's no result, run your insertThank you
If you could help me on how to index, please. Thank you
interested: defineTable({
userId: v.id("users"),
ideaId: v.id("ideas"),
}).index("by_ideaId_userId", ["ideaId", "userId"]),
I followed your step 1 & changed from 2 invidual index to this. However, there are places where I need to get all users for a certain idea or get all ideas for a certain user.
So, how should I form my schema that helps me with both queries (& ensure unique docs as well) ?
You would add additional indexes, one for each field:
Use the ideaId index to query users for an ideaId, and vice versa
ohh, I see. I thought maybe that was not best practice. Thank you 🌼
One of the best and most disorienting things about Convex is how often the obvious, simple approach is also the ideal approach.
I 100% agree. having superr fun with convex
I like to imagine DB indexes as book indexes
they take a little space (a few pages) and add extra work each time there is a mutation, as those indexes have to be up-to-date
but when looking for an info, they'll help A LOT
about having multiple indexes, you're paying that price a few times which, for most use cases, is completely fine
however, there is one little "trick" - I don't think you need
but only
since
"by_ideaId_userId" is already sorted by ideaId
the "book index" would look something like
- idea 1
- user 1
- user 2
- user 3
- idea 2
- user 1
- user 6
so the DB can properly sorte by ideaId only (but not from userId only, hence the 2nd index)here is a real use-case I have
conceptually it's fine, but I don't really know if there is any Convex-specific issue with that approach
note that types work flawlessly
it wont even allow me to use
kind as the first index.eq()
and will only allow
kind on the second one, but not mandatory
That does work, but keep in mind the results will be ordered by the unused index(es), and then lastly by creation date, which may not be expected.
Thank you for the explanation. I think you're right, I don't need the "by_ideaId" index. I was actually trying out getManyFrom() from convex-helpers & when I use the "by_ideaId_userId", it requires ideaId & userId as arguments.
this approach withIndex works perfectly fine. maybe I'm not using them properly
this approach withIndex works perfectly fine. maybe I'm not using them properly