#？

12 messages · Page 1 of 1 (latest)

leaden smelt
#

what if i want all the unique combination of volume and issues in the dropdown for volumn and issues how can query that without scanning the entire db
articles: defineTable({
title: v.string(),
abstract: v.optional(v.string()),
refId: v.number(),
volume: v.optional(v.number()),
issue: v.optional(v.number()),
status: v.union(
v.literal(ARTICLE_STATUS.SUBMITTED),
v.literal(ARTICLE_STATUS.REVIEW),
v.literal(ARTICLE_STATUS.COPYEDIT),
v.literal(ARTICLE_STATUS.PUBLISHED),
v.literal(ARTICLE_STATUS.REJECTED),
v.literal(ARTICLE_STATUS.ARCHIVED)
),
createdBy: v.id("users"),
createdAt: v.number(),
updatedAt: v.number(),
publishedAt: v.optional(v.number()),
})

small cliffBOT
#

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

#

To avoid scanning the whole articles table, you’ll want to:

  1. Add a compound index on volume and issue.
  2. Use a SELECT DISTINCT-style pattern over that index to get unique (volume, issue) pairs.

1. Define a compound index

In convex/schema.ts:

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  articles: defineTable({
    title: v.string(),
    abstract: v.optional(v.string()),
    refId: v.number(),
    volume: v.optional(v.number()),
    issue: v.optional(v.number()),
    status: v.union(
      v.literal("SUBMITTED"),
      v.literal("REVIEW"),
      v.literal("COPYEDIT"),
      v.literal("PUBLISHED"),
      v.literal("REJECTED"),
      v.literal("ARCHIVED")
    ),
    createdBy: v.id("users"),
    createdAt: v.number(),
    updatedAt: v.number(),
    publishedAt: v.optional(v.number()),
  }).index("by_volume_issue", ["volume", "issue"]),
});

Compound indexes let you efficiently constrain and sort by both fields together.[Indexing multiple]

2. Get distinct (volume, issue) pairs

Convex doesn’t have a built‑in DISTINCT, but you can implement it efficiently using the index. The pattern is:

  • Walk the index in order.
  • Only keep the first row for each distinct prefix of the index key (here: [volume, issue]).[Select distinct]

Using the stream helpers from the SELECT DISTINCT article, it looks like:

import { query } from "./_generated/server";
import { stream } from "convex-helpers/server/streams"; // from the Stack article’s library
#

export const distinctVolumeIssues = query({
args: {},
handler: async (ctx) => {
const s = stream(ctx.db, ctx.schema)
.query("articles")
.withIndex("by_volume_issue")
.distinct(["volume", "issue"]) // prefix of the index
.map(async (article) => ({
volume: article.volume,
issue: article.issue,
}));

const { page } = await s.paginate({ numItems: 1000 }); // or your desired page size
return page;

},
});


This uses the index order and a `distinct` helper to avoid scanning the entire table in one go, and only returns one row per unique `(volume, issue)` combination.[[Distinct helper](<https://stack.convex.dev/merging-streams-of-convex-data#distinct>)]

If you’re not using the helper library, the same idea can be implemented manually with repeated indexed queries as shown in the `SELECT DISTINCT` article, but the sources don’t provide that exact code for a 2‑column key; they only show the 1‑column pattern for `channelId`.[[Translate SQL](<https://stack.convex.dev/translate-sql-into-convex-queries#distinct>)]
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------
#

@leaden smelt Kapa.ai is still learning and improving, please let me know how I did by reacting below.

leaden smelt
#

@small cliff i want all the possible combo of vol and iss so do i need pagination

small cliffBOT
#

Let me check the knowledge base for you again. One moment! :books:

#

You only need pagination if the number of distinct (volume, issue) combos could be large enough to be slow or hit read limits.

From the docs:

  • Any query that might touch “more than a few thousand documents” should use pagination or limits for scalability.[Other DB best practices]
  • When using indexes without a range expression, you should pair them with .first(), .take(n), or .paginate(opts) to avoid full-table scans.[Sorting with indexes]

So:

  • If you expect a small number of distinct (volume, issue) pairs (e.g. tens or low hundreds), you can safely collect them all in one query (using an index + distinct pattern).
  • If you expect many volumes/issues over time (hundreds/thousands of combos or a very large articles table), you should use .paginate(...) on the distinct stream so you don’t scan/return everything at once.[Distinct streams]

In practice: for a dropdown that will always stay reasonably small, you can skip pagination; if you’re unsure or planning for growth, implement it with pagination now.

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

#

@leaden smelt Kapa.ai is still learning and improving, please let me know how I did by reacting below.

leaden smelt
#

@small cliff mod export const listDistinctVolumeIssues = query({
args: {},
handler: async (ctx) => {
const s = stream(ctx.db, schema)
.query("articles")
.withIndex("by_volume_issue")
.distinct(["volume", "issue"])
.map(async (article: any) => ({
volume: article.volume as number | undefined,
issue: article.issue as number | undefined,
}));

    const { page } = await s.paginate({ cursor: null, numItems: 1000 });
    return page;
},

}); to collect all

small cliffBOT
#

Let me check the knowledge base for you again. One moment! :books:

#

You don’t need paginate if you truly want to load all distinct (volume, issue) pairs and you know the total will stay reasonably small. You can just call .collect() on the stream, which works the same way as on a normal Convex query.[Streams collect; Query.collect]

Be aware of the usual .collect() caveat: only use it when the result set is tightly bounded (e.g. you know you’ll never have thousands of distinct combos).[Only use collect]

Here’s your function modified to collect all results:

import { query } from "./_generated/server";
import { stream } from "convex-helpers/server/stream";
import schema from "./schema";

export const listDistinctVolumeIssues = query({
  args: {},
  handler: async (ctx) => {
    const s = stream(ctx.db, schema)
      .query("articles")
      .withIndex("by_volume_issue")
      .distinct(["volume", "issue"])
      .map(async (article: any) => ({
        volume: article.volume as number | undefined,
        issue: article.issue as number | undefined,
      }));

    // Load all distinct (volume, issue) pairs
    const allCombos = await s.collect();
    return allCombos;
  },
});

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