Drizzle Team•11mo ago

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•11mo ago

What do you have so far?

Venyl•11mo 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•11mo 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•11mo 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

Gaming

Programming

Select item collection together with item count