Honey•4w ago

Help with query

import { query } from "./_generated/server";

export const getAllUsersWithSubscriptions = query({
  handler: async (ctx) => {
    const users = await ctx.db.query("users").collect();
    
    const usersWithSubs = await Promise.all(
      users.map(async (user) => {
        const subscription = await ctx.db
          .query("subscriptions")
          .withIndex("by_user", (q) => q.eq("userId", user._id))
          .first();
        
        return {
          _id: user._id,
          name: user.name,
          email: user.email,
          subscription: subscription!,
        };
      })
    );
    
    return usersWithSubs;
  },
});

import { query } from "./_generated/server";

export const getAllUsersWithSubscriptions = query({
  handler: async (ctx) => {
    const users = await ctx.db.query("users").collect();
    
    const usersWithSubs = await Promise.all(
      users.map(async (user) => {
        const subscription = await ctx.db
          .query("subscriptions")
          .withIndex("by_user", (q) => q.eq("userId", user._id))
          .first();
        
        return {
          _id: user._id,
          name: user.name,
          email: user.email,
          subscription: subscription!,
        };
      })
    );
    
    return usersWithSubs;
  },
});

3 Replies

HoneyOP•4w ago

let me know if its correct whey to combine 2 tables, i want query all users and all subscriptions

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

export default defineSchema({
  users: defineTable({
    name: v.string(),
    email: v.string(),
  }),
  
  subscriptions: defineTable({
    userId: v.id("users"),
    plan: v.string(),
    status: v.string(),
  }).index("by_user", ["userId"]),
});

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

export default defineSchema({
  users: defineTable({
    name: v.string(),
    email: v.string(),
  }),
  
  subscriptions: defineTable({
    userId: v.id("users"),
    plan: v.string(),
    status: v.string(),
  }).index("by_user", ["userId"]),
});

Does it count as one query or as many queries as we used the map? and let say i have 100 users

Clever Tagline•4w ago

The metrics that count are function calls and KB/MB returned, not the number of times that .query is called inside a function. The query function still only ran once, so that counts as one function call. You might consider approaching this in reverse: query the subscriptions table, then get the users using the IDs from those subscription documents. For example, if you have 10K users, but only 100 have subscriptions, using your current process you'll grab all 10K user documents first, then get their subscription info. That's 10,100 documents read in total. However, if you get the subscription documents first and then find the users, you'll only collect those users with subscriptions: 100 user documents + 100 subscription documents. Less data read that way.

HoneyOP•3w ago

Thanks

Help with query

Did you find this page helpful?