Select item collection together with item count

Heads-up: I'm an SQL & Drizzle newbie I have 3 tables: users, flashcard_collection and flashcards flashcard_collection has a creatorId foreign key which indicates what user a collection belongs to flashcards has a collectionId foreign key which indicates what collection a flashcard belongs to I want to select all flashcard collections that belong to a user together with each collection's flashcard count
4 Replies
Angelelz
Angelelz13mo ago
What do you have so far?
Venyl
VenylOP13mo ago
const userCollections = await db.query.flashcardCollections.findMany({
where: eq(flashcardCollections.creatorId, userId),
});
const userCollections = await db.query.flashcardCollections.findMany({
where: eq(flashcardCollections.creatorId, userId),
});
This works but doesn't return me the count I've seen that queries like this don't work with aggregate functions and count is of such type
const userCollections = await db
.select({
id: flashcardCollections.id,
name: flashcardCollections.name,
flashcardCount: sql<number>``
})
.from(flashcardCollections)
.where(eq(flashcardCollections.creatorId, userId));
const userCollections = await db
.select({
id: flashcardCollections.id,
name: flashcardCollections.name,
flashcardCount: sql<number>``
})
.from(flashcardCollections)
.where(eq(flashcardCollections.creatorId, userId));
so I'm trying to do something in this style
Angelelz
Angelelz13mo ago
The relational query builder does not support aggregations yet. You can either user the Crud API or the following workaround:
const userCollections = await db.query.flashcardCollections.findMany({
where: eq(flashcardCollections.creatorId, userId),
with: {
flashcards: true
}
});
const userCollections = await db.query.flashcardCollections.findMany({
where: eq(flashcardCollections.creatorId, userId),
with: {
flashcards: true
}
});
And then just count the array length with JS:
const aggregated = { ...userCollections,
flashcardCount: userCollentions.flashcards.length
}
const aggregated = { ...userCollections,
flashcardCount: userCollentions.flashcards.length
}
Venyl
VenylOP13mo ago
Oh my god why haven't I noticed that the with keyword works like that Thank you this solves my problem wait userCollections.flashcards is an empty array it shouldn't be Ok I had a bad id in my relations
Want results from more Discord servers?
Add your server