Query Speed Up
please how do I optimise this query, I feel like am not doing it the right way
export const list = query({
args: {},
handler: async (ctx) => {
await getUserId(ctx);
const users = await ctx.db.query("customers").collect();
const usersWithDownline = await Promise.all(
users.map(async (user) => {
const userDetail = await ctx.db.get(user.userId);
const downline = await ctx.db
.query("downlines")
.withIndex("customer", (q) => q.eq("leader", user._id))
.collect();
const downlineWithFollower = await Promise.all(
downline.map(async (dl) => {
const follower = await ctx.db.get(dl.follower);
const followerDetails = await ctx.db.get(follower!.userId);
return {
...dl,
followers: {
userId: followerDetails!._id,
id: follower!._id,
email: followerDetails!.email,
firstName: follower!.firstName,
lastName: follower!.lastName,
},
};
}),
);
const upline = await ctx.db.get(user.upline ?? "");
const uplineDetails = await ctx.db.get(upline!.userId);
const products =
user.products?.map(async (productId: Id<"products">) => {
return await ctx.db.get(productId);
}) ?? [];
return {
...user,
user: userDetail,
productsList: await Promise.all(products),
downline: downlineWithFollower,
upline: upline
? {
id: upline._id,
email: uplineDetails!.email,
name: uplineDetails!.name,
firstName: upline.firstName,
lastName: upline.lastName,
}
: null,
};
}),
);
return usersWithDownline;
},
});
export const list = query({
args: {},
handler: async (ctx) => {
await getUserId(ctx);
const users = await ctx.db.query("customers").collect();
const usersWithDownline = await Promise.all(
users.map(async (user) => {
const userDetail = await ctx.db.get(user.userId);
const downline = await ctx.db
.query("downlines")
.withIndex("customer", (q) => q.eq("leader", user._id))
.collect();
const downlineWithFollower = await Promise.all(
downline.map(async (dl) => {
const follower = await ctx.db.get(dl.follower);
const followerDetails = await ctx.db.get(follower!.userId);
return {
...dl,
followers: {
userId: followerDetails!._id,
id: follower!._id,
email: followerDetails!.email,
firstName: follower!.firstName,
lastName: follower!.lastName,
},
};
}),
);
const upline = await ctx.db.get(user.upline ?? "");
const uplineDetails = await ctx.db.get(upline!.userId);
const products =
user.products?.map(async (productId: Id<"products">) => {
return await ctx.db.get(productId);
}) ?? [];
return {
...user,
user: userDetail,
productsList: await Promise.all(products),
downline: downlineWithFollower,
upline: upline
? {
id: upline._id,
email: uplineDetails!.email,
name: uplineDetails!.name,
firstName: upline.firstName,
lastName: upline.lastName,
}
: null,
};
}),
);
return usersWithDownline;
},
});
5 Replies
this is my current schema
users: defineTable({
name: v.optional(v.string()),
image: v.optional(v.string()),
email: v.optional(v.string()),
emailVerificationTime: v.optional(v.number()),
phone: v.optional(v.string()),
phoneVerificationTime: v.optional(v.number()),
isAnonymous: v.optional(v.boolean()),
role: v.string(),
})
.index("email", ["email"])
.index("role", ["role"]),
customers: defineTable({
userId: v.id("users"),
firstName: v.string(),
lastName: v.string(),
downline: v.array(v.id("downlines")),
upline: v.id("customers"),
bankName: v.string(),
accountName: v.string(),
accountNumber: v.string(),
pin: v.string(),
payments: v.array(v.id("payments")),
orders: v.array(v.id("orders")),
earning: v.number(),
active: v.boolean(),
products: v.optional(v.array(v.id("products"))),
})
.index("userId", ["userId"])
.index("product", ["products"]),
downlines: defineTable({
leader: v.id("customers"),
product: v.id("products"),
follower: v.id("customers"),
level: v.string(),
commission: v.optional(v.number()),
indirectCommision: v.optional(v.number()),
totalCommision: v.optional(v.number()),
totalIndirectCommision: v.optional(v.number()),
totalCommisionEarned: v.optional(v.number()),
type: v.string(),
}).index("customer", ["leader", "follower"]),
users: defineTable({
name: v.optional(v.string()),
image: v.optional(v.string()),
email: v.optional(v.string()),
emailVerificationTime: v.optional(v.number()),
phone: v.optional(v.string()),
phoneVerificationTime: v.optional(v.number()),
isAnonymous: v.optional(v.boolean()),
role: v.string(),
})
.index("email", ["email"])
.index("role", ["role"]),
customers: defineTable({
userId: v.id("users"),
firstName: v.string(),
lastName: v.string(),
downline: v.array(v.id("downlines")),
upline: v.id("customers"),
bankName: v.string(),
accountName: v.string(),
accountNumber: v.string(),
pin: v.string(),
payments: v.array(v.id("payments")),
orders: v.array(v.id("orders")),
earning: v.number(),
active: v.boolean(),
products: v.optional(v.array(v.id("products"))),
})
.index("userId", ["userId"])
.index("product", ["products"]),
downlines: defineTable({
leader: v.id("customers"),
product: v.id("products"),
follower: v.id("customers"),
level: v.string(),
commission: v.optional(v.number()),
indirectCommision: v.optional(v.number()),
totalCommision: v.optional(v.number()),
totalIndirectCommision: v.optional(v.number()),
totalCommisionEarned: v.optional(v.number()),
type: v.string(),
}).index("customer", ["leader", "follower"]),
products: defineTable({
name: v.string(),
description: v.string(),
price: v.number(),
slug: v.string(),
stock: v.number(),
theme: v.id("themes"),
commission: v.number(),
downline: v.number(),
indirectDownline: v.number(),
imageUrl: v.string(),
isFeatured: v.boolean(),
discount: v.number(),
rating: v.number(),
sold: v.number(),
status: v.string(),
orders: v.optional(v.array(v.id("orders"))),
category: v.id("categories"),
customers: v.optional(v.array(v.id("customers"))),
})
.index("category", ["category"])
.index("status", ["status"])
.index("theme", ["theme"])
.index("isFeatured", ["isFeatured"]),
products: defineTable({
name: v.string(),
description: v.string(),
price: v.number(),
slug: v.string(),
stock: v.number(),
theme: v.id("themes"),
commission: v.number(),
downline: v.number(),
indirectDownline: v.number(),
imageUrl: v.string(),
isFeatured: v.boolean(),
discount: v.number(),
rating: v.number(),
sold: v.number(),
status: v.string(),
orders: v.optional(v.array(v.id("orders"))),
category: v.id("categories"),
customers: v.optional(v.array(v.id("customers"))),
})
.index("category", ["category"])
.index("status", ["status"])
.index("theme", ["theme"])
.index("isFeatured", ["isFeatured"]),
It looks fine to me. Queries are running in parallel with Promise.all and each query uses an index. Are you observing bad performance?
Not really yet, but from the standpoint of a large data and knowing the every database query counts a function call, Am just looking for more ways to optimise the queries, and also am yet to really graps how relationship work with the convex db
ctx.db.query does not count as a function call. The query function query({ args, handler }) counts as a single function calloh, great, now am good :convex: 😋