Filtering out results

Hello. I have a query filtering structure that I want to ask about.

   const likedProducts = await ctx.db
      .query("productLikes")
      .withIndex("userId", (q) => q.eq("userId", ctx.user._id))
      .collect();
    const dislikedProducts = await ctx.db
      .query("productDislikes")
      .withIndex("userId", (q) => q.eq("userId", ctx.user._id))
      .collect();

    const likedProductIds = likedProducts.map((like) => like.productId);
    const dislikedProductIds = dislikedProducts.map(
      (dislike) => dislike.productId,
    );
    
    const  notInProductIds= [...likedProductIds, ...dislikedProductIds];

   const likedProducts = await ctx.db
      .query("productLikes")
      .withIndex("userId", (q) => q.eq("userId", ctx.user._id))
      .collect();
    const dislikedProducts = await ctx.db
      .query("productDislikes")
      .withIndex("userId", (q) => q.eq("userId", ctx.user._id))
      .collect();

    const likedProductIds = likedProducts.map((like) => like.productId);
    const dislikedProductIds = dislikedProducts.map(
      (dislike) => dislike.productId,
    );
    
    const  notInProductIds= [...likedProductIds, ...dislikedProductIds];

What is the correct way to perform a query request from the products table without these ids? I will bring them in sets of 10. That's why filtering is needed on the db side. Should the query be written like this?

 
  const products = await ctx.db
      .query("products")
      .withIndex("by_id")
      .filter((q) =>
        q.and(...notInProductIds.map((id) => q.neq(q.field("_id"), id))),
      )
      .take(10)

 
  const products = await ctx.db
      .query("products")
      .withIndex("by_id")
      .filter((q) =>
        q.and(...notInProductIds.map((id) => q.neq(q.field("_id"), id))),
      )
      .take(10)

5 Replies

Michal Srb•10mo ago

I think your solution is feasible, you could also filter out the results you don't want in JS (construct a Set and then check if the _id is in the set). The downside to this approach is that the query depends on all products the user liked/disliked. A more precise read from the DB would be to fetch for each retrieved product whether it was liked or disliked, and then filter out. You can use a compound index on ["userId", "productId"] on the productLikes and productDislikes tables for this.

hasanaktasTROP•10mo ago

Since we have thousands of product records, filtering the products to be shown to each user on the JavaScript side seems very inefficient. What I want is to fetch items that are not in the array like "NOT IN" in SQL. We are trying to establish a structure in products that Tinder likes. We keep the products that each user likes and dislikes in a separate table with userId and ProductId. I'm trying to set up this structure to query the products we will show to the user. I didn't understand what the benefit of adding a compound index would be. I only query via userId. I need a list of products that do not include likes and dislikes to show in Discover.

Michal Srb•10mo ago

... filtering the products to be shown to each user on the JavaScript side seems very inefficient. ... "NOT IN" in SQL

These are equally efficient. You might want to read through this post, which explains why: https://stack.convex.dev/complex-filters-in-convex (In fact the filtering in JS might be slightly more effiecient if a Set is used, as the filter written via q.and is essentially O(n×m) (where n is the number of products fetched and m is the number of liked/disliked products), whereas the Set approach is O(n×log m)) But what I'm suggesting you do instead, in SQL parlance, is a JOIN first (to find out if the product was liked or disliked), then WHERE clause to filter out the liked/disliked products. Unless you severely limit the number of products a user can like/dislike, this will likely be more efficient. The big difference between the approaches is in what range of data the query subscribes to, and the amount of data that's required to be read from the DB (in the filtering approach, all likes/dislikes have to be read every time). The compound index is only needed if you use the JOIN approach.

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.

sshader•10mo ago

(Also https://stack.convex.dev/databases-are-spreadsheets, which covers what "in" and "not equal" would really do under the hood)

hasanaktasTROP•10mo ago

There are still things that don't sit well in my mind. I will get back to you after reviewing and testing. Thanks

Filtering out results

Did you find this page helpful?