Query Filtering

[short fern]

Hi @candid sapphire. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

To convert this SQL query to Convex TypeScript, we need to use Convex's query API and filtering capabilities. Here's how you can approach this in Convex:

import { query } from "./_generated/server";
import { v } from "convex/values";
import { filter } from "convex-helpers/server/filter";

export const getMashups = query({
  args: {
    limit: v.number(),
    offset: v.number(),
    genres: v.optional(v.array(v.string())),
    keys: v.optional(v.array(v.string())),
    regions: v.optional(v.array(v.string())),
    bpm_min: v.optional(v.number()),
    bpm_max: v.optional(v.number()),
    variants: v.optional(v.array(v.string())),
  },
  handler: async (ctx, args) => {
    const { limit, offset, genres, keys, regions, bpm_min, bpm_max, variants } = args;

    return filter(
      ctx.db
        .query("mashups")
        .withIndex("by_visibility_and_releaseDate", (q) => 
          q.eq("visibility", "public")
        )
        .order("desc"),
      (mashup) => {
        if (genres && !genres.includes(mashup.genre_name)) return false;
        if (keys && !keys.includes(mashup.key)) return false;
        if (regions && !regions.includes("Global") && !regions.includes(mashup.region_name)) return false;
        if ((bpm_min !== undefined && mashup.bpm < bpm_min) || (bpm_max !== undefined && mashup.bpm > bpm_max)) return false;
        if (variants && !variants.some(v => mashup.variants.some(mv => mv.name === v))) return false;
        return true;
      }
    )
      .take(limit)
      .skip(offset);
  },
});

This Convex query function does the following:

1. We use the filter helper from convex-helpers to apply complex filtering logic. This is equivalent to the SQL WHERE clauses Complex filters in Convex.

2. We start with ctx.db.query("mashups") to query the mashups table.

3. We use .withIndex("by_visibility_and_releaseDate", ...) to filter for public mashups and order by release date. This assumes you have defined an index on the visibility and releaseDate fields in your schema.

4. The filter function applies all the additional filtering logic, which is equivalent to the SQL WHERE clauses.

5. We use .take(limit) and .skip(offset) for pagination, which is equivalent to LIMIT and OFFSET in SQL.

Note that this implementation assumes you have set up your schema correctly with the necessary fields and indexes. You might need to adjust the index name and fields based on your actual schema.

Also, the variants filtering in this TypeScript version assumes that variants is an array of objects with a name property. If your data structure is different, you'll need to adjust this part of the filter.

Remember that for optimal performance, you should try to use indexes for your most common queries. The current implementation uses an index for visibility and releaseDate, but depending on your usage patterns, you might want to create additional indexes for other frequently filtered fields.

Filtering provides more information on how to use filters in Convex queries.

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: