Drizzle Team•7mo ago

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

Gaming

Programming

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