Drizzle TeamDT
Drizzle Team•2y ago
sunshne2263

Help Needed: Adding Message Count and Filtering by UserId in DB Query

Hello everyone,

I'm working on a feature where I need to fetch data from two tables: companion and message. My objective is to retrieve a companion from the companion table, all related messages for that companion from the message table, and include a count of these messages. An additional requirement is that the messages should be filtered by userId. However, I'm encountering challenges with incorporating the message count and filtering in my database call.

Here's my current approach:

import { db } from "~/db"
import { companion, message } from "~/db/schema"

const companions = await db
    .select({
      id: companion.id,
      userId: companion.userId,
      userName: companion.userName,
      src: companion.src,
      name: companion.name,
      description: companion.description,
      instructions: companion.instructions,
      seed: companion.seed,
      createdAt: companion.createdAt,
      updatedAt: companion.updatedAt,
      categoryId: companion.categoryId,
      messageCount: count(message.id),
      message: message,
    })
    .from(companion)
    .leftJoin(
      message,
      and(eq(companion.id, message.companionId), eq(message.userId, userId)),
    )
    .where(eq(companion.id, params.chatId))
    .orderBy(asc(message.createdAt))


This code results in a database error. Without {message: message} in the query, the error disappears, but then I only receive the message count, not the actual messages. I need to figure out how to return both the individual messages (filtered by userId) and their total count.
Was this page helpful?