Turbo•2y ago

Complex Filtering

I'm a bit confused on how i should go about conditionally filtering my queries and filtering entries in general. I have a table with two fields by which I want to filter the results. But the args i provide in the query function are optional. Let's take the field "map" because that's the easiest to filter out of the two In the image below i filter the returned documents. Is this an efficient way to filter queries? I mean it works, but I do want to make it more efficient than to get everything and then filter, because who knows when the table could have thousands of entries, which as i understood from the indexing docs, does take longer to go through all the entries.

9 Replies

TurboOP•2y ago

And the second field named tags is more complicated in the db it's a list of objects structured as so

{
  id: string,
  displayName: string,
  category: "ability" | "side"
}

{
  id: string,
  displayName: string,
  category: "ability" | "side"
}

basically i take all the objects in the tags field with the category "side" and i check if the new list has atleast one of the entries in tags.sides argument if isn't empty and then i further filter the with tags.abilities in the same way, checking if it has atleast one of the entries and then i return the posts that meet all these conditions But my worry still is that it isn't the most efficient way to filter, as i'm going through all the posts in the db.

TurboOP•2y ago

I was planning to do an if condition inside the db.query().filter() but that didn't seem to work i'm not the most well versed with backend, so forgive me if what i've explained is not clear enough 😅

ballingt•2y ago

I'm headed to bed, but a few thoughts: - it's ok not to do this the most efficient way for now, Convex being relational helps make it easy to change data access patterns later - for doing a condition inside the filter it would help to see code, that's a reasonable thing to do! here's an example of that: https://docs.convex.dev/using/database-queries#equality-conditions - indexes are the next optimization after that. It sounds like an index on the posts table by map would be useful here, since you want to grab all the posts of a given map value - for the second part, there are ways to data model this - but you could wait to get into this until you have thousands of posts with the same map value to do it, because doing this in memory as you already are sure is convenient

Querying the Database | Convex Developer Hub

Push queries down to the database.

TurboOP•2y ago

it's ok not to do this the most efficient way for now, Convex being relational helps make it easy to change data access patterns later

👍 got it!

for doing a condition inside the filter it would help to see code, that's a reasonable thing to do!

About this, i still don't understand how i would make it filter the query, only if my map arg is not "" or "All" should i do it like so?

let postsQuery = db.query("posts").order("desc")
if (map !== "" && map !== "All) {
  postsQuery = postsQuery.filter(q => q.eq(q.field("map"), map))
}

const posts = await postsQuery.collect()

let postsQuery = db.query("posts").order("desc")
if (map !== "" && map !== "All) {
  postsQuery = postsQuery.filter(q => q.eq(q.field("map"), map))
}

const posts = await postsQuery.collect()

I'll test it now anyway, but i just want your input on if this a "proper" way to do it

indexes are the next optimization after that. It sounds like an index on the posts table by map would be useful here, since you want to grab all the posts of a given map value

I did read up on indexing, and it did sound interesting. I'll go through it once more and try to put it to use ✨

for the second part, there are ways to data model this - but you could wait to get into this until you have thousands of posts with the same map value to do it, because doing this in memory as you already are sure is convenient

Oh, i see. i think i'll need more time working with backend to get into it then 😅 This is only the second time i'm even doing anything database related

TurboOP•2y ago

I did a little messing around and made it to this and it works fine as far as i can tell so, yay?

ballingt•2y ago

this looks great oh I misunderstood about the "conditional filtering," I thought the filter was the condition. Yeah, I would put the if statement outside of the query and write two queries just like you did.

TurboOP•2y ago

ah, i was confused by your first reply to that I was thinking there was a way to do that within filter() and dug through the api docs for a good minute 😄

ballingt•2y ago

sorry about that!

TurboOP•2y ago

👌 Got it done in the end Thanks so much for the help!!

Complex Filtering

Did you find this page helpful?