DT
Drizzle Team•14mo 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))
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.
3 Replies
Luxaritas
Luxaritas•14mo ago
Drizzle (and SQL in general) doesn’t let you pass a table to select all fields as an array You’ll need to use something like JSON_AGG/JSON_ARRAYAGG/JSON_OBJECTAGG
sunshne2263
sunshne2263OP•14mo ago
it appears if i use 
db.select()
  .from(companion)
  .leftJoin(
      message,
      and(eq(companion.id, message.companionId), eq(message.userId, userId)),
    )
  .where(eq(companion.id, params.chatId))
  .orderBy(asc(message.createdAt))
db.select()
  .from(companion)
  .leftJoin(
      message,
      and(eq(companion.id, message.companionId), eq(message.userId, userId)),
    )
  .where(eq(companion.id, params.chatId))
  .orderBy(asc(message.createdAt))
I get the messages, I was just wondering if there was a way to include the count?
Luxaritas
Luxaritas•14mo ago
Well in that case, you have one row per message instead of one row per user Which means you’re returning a lot of duplicate data (the companion info is returned multiple times) If you don’t care about that, you can do it that way, though you need to aggregate by user yourself, and count in JS

Did you find this page helpful?