neq condition for `withIndex`

here are pseudocodes that fails to compile:

ctx.db
  .query('inventory')
  .withIndex('by_status', (q) => q.neq('status', 'removed')) // in_stock, sold, removed

ctx.db
  .query('inventory')
  .withIndex('by_status', (q) => q.neq('status', 'removed')) // in_stock, sold, removed

ctx.db
  .query('inventory')
  .withIndex('by_status', (q) => q.or(q.eq('status', 'sold'), q.eq('status', 'in_stock')))

ctx.db
  .query('inventory')
  .withIndex('by_status', (q) => q.or(q.eq('status', 'sold'), q.eq('status', 'in_stock')))

both neq and or are not supported with withIndex() how can I do something like above pseudocode?

13 Replies

Hmza•4mo ago

q.neq will fail because you cannot do that directly but only in equality comparsion but to your second query. you'd want to use Compound Index

ian•4mo ago

You can achieve this by running both indexed queries (at the same time if you like).

ian•4mo ago

https://stack.convex.dev/databases-are-spreadsheets

Databases are Spreadsheets

I want to share my mental model of databases: - Databases are just big spreadsheets - An index is just a view of the spreadsheet sorted by one or mor...

Hmza•4mo ago

ohh.

ian•4mo ago

neq works for filters, but indexes are about finding a contiguous region to search over so for multiple regions you would run multiple queries specifically in that article check out the section on "Inequalities"

Hmza•4mo ago

@ian this would be an approach too maybe ? https://docs.convex.dev/database/indexes/indexes-and-query-perf

ian•4mo ago

compound indexes are when the index is over multiple fields and you want to find equality on them. In this example if the index was on more than status, you could use a compound query

Hmza•4mo ago

yeah he has in_stock so my first thought was to just get all in_stock = false items directly still equality but makes sense.

Jacob KimOP•4mo ago

Is there any performance hit when I run two indexed queries instead of just one indexed query?

ian•4mo ago

nope - it's a matter of how much you read, not how many db.query calls so doing multiple indexed calls will be very cheap, compared to scanning them all and filtering them down, for example

Jacob KimOP•4mo ago

I doubt there is any meaningful difference here but I’m curious. if I change the schema from strings to 0, 1, 2(numbers), I could run the query in a single gt(). Would this change be better for storage & query performance?

ian•4mo ago

the cost of fetching would be the same, but you could do it in one query. So you wouldn't have to manually run the two queries in parallel, so it'd be more convenient. And you could do things like .take or .paginate where it'd go through one then the other, in sequence order. there's a lot of clever things you can do when you manipulate the orderings, but I'd recommend doing the more readable thing to start.

Jacob KimOP•4mo ago

I see thanks for the detailed response

neq condition for `withIndex`

Did you find this page helpful?