22 replies

Recursive query performance

Hi again

I am working on a Notion clone, currently focusing on the sidebar element. Which looks like this: https://imgur.com/itFF5qX

My schema for this entity is as follows:

export default defineSchema({
  documents: defineTable({
    title: v.string(),
    userId: v.string(),
    isArchived: v.boolean(),
    parentDocument: v.optional(v.id("documents")),
    content: v.optional(v.string()),
  })
  .index("by_user_id", ["userId"])
});

export default defineSchema({
  documents: defineTable({
    title: v.string(),
    userId: v.string(),
    isArchived: v.boolean(),
    parentDocument: v.optional(v.id("documents")),
    content: v.optional(v.string()),
  })
  .index("by_user_id", ["userId"])
});

export default defineSchema({
  documents: defineTable({
    title: v.string(),
    userId: v.string(),
    isArchived: v.boolean(),
    parentDocument: v.optional(v.id("documents")),
    content: v.optional(v.string()),
  })
  .index("by_user_id", ["userId"])
});

export default defineSchema({
  documents: defineTable({
    title: v.string(),
    userId: v.string(),
    isArchived: v.boolean(),
    parentDocument: v.optional(v.id("documents")),
    content: v.optional(v.string()),
  })
  .index("by_user_id", ["userId"])
});

As you can see, documents which are created at the top level have undefined as their parentDocument field, where children have a proper relation with their parent.

This is my API to fetch both parent documents, or child documents if I provide a parentDocument argument:

export const get = query({
  args: {
    parentDocument: v.optional(v.id("documents")),
    isArchived: v.optional(v.boolean()),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();

    if (!identity) {
      throw new Error("Not authenticated");
    }

    const userId = identity.subject;

    const documents = await ctx.db
      .query("documents")
      .withIndex("by_user_id", (q) => q.eq("userId", userId))
      .filter((q) =>
        q.and(
          q.eq(q.field("isArchived"), !!args.isArchived),
          q.eq(q.field("parentDocument"), args.parentDocument),
        )
      )
      .order("desc")
      .collect();

    return documents;
  },
});

export const get = query({
  args: {
    parentDocument: v.optional(v.id("documents")),
    isArchived: v.optional(v.boolean()),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();

    if (!identity) {
      throw new Error("Not authenticated");
    }

    const userId = identity.subject;

    const documents = await ctx.db
      .query("documents")
      .withIndex("by_user_id", (q) => q.eq("userId", userId))
      .filter((q) =>
        q.and(
          q.eq(q.field("isArchived"), !!args.isArchived),
          q.eq(q.field("parentDocument"), args.parentDocument),
        )
      )
      .order("desc")
      .collect();

    return documents;
  },
});

export const get = query({
  args: {
    parentDocument: v.optional(v.id("documents")),
    isArchived: v.optional(v.boolean()),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();

    if (!identity) {
      throw new Error("Not authenticated");
    }

    const userId = identity.subject;

    const documents = await ctx.db
      .query("documents")
      .withIndex("by_user_id", (q) => q.eq("userId", userId))
      .filter((q) =>
        q.and(
          q.eq(q.field("isArchived"), !!args.isArchived),
          q.eq(q.field("parentDocument"), args.parentDocument),
        )
      )
      .order("desc")
      .collect();

    return documents;
  },
});

export const get = query({
  args: {
    parentDocument: v.optional(v.id("documents")),
    isArchived: v.optional(v.boolean()),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();

    if (!identity) {
      throw new Error("Not authenticated");
    }

    const userId = identity.subject;

    const documents = await ctx.db
      .query("documents")
      .withIndex("by_user_id", (q) => q.eq("userId", userId))
      .filter((q) =>
        q.and(
          q.eq(q.field("isArchived"), !!args.isArchived),
          q.eq(q.field("parentDocument"), args.parentDocument),
        )
      )
      .order("desc")
      .collect();

    return documents;
  },
});

I will continue in the additional comments because the message is too long

Imgur

Recursive query performance

Similar Threads

Similar Threads

Similar Threads