sunshne2263 Posts - Answer Overflow

sunshne2263

•Created by sunshne2263 on 12/25/2023 in #help

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.

7 replies

DTDrizzle Team

•Created by sunshne2263 on 12/23/2023 in #help

Is FULLTEXT Search Possible in MySQL via FULLTEXT Index?

Hello everyone, I am currently working on a project using Drizzle ORM with a MySQL database, and I'm looking to implement full-text search capabilities. While I am aware that MySQL supports FULLTEXT indexes (as detailed in their documentation: https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html), I am uncertain about the integration of these features with Drizzle ORM. My questions are as follows: 1. Does Drizzle ORM support the creation of FULLTEXT indexes in line with MySQL's native capabilities? How can I define a FULLTEXT index on a column in my entity model using Drizzle ORM? 2. If Drizzle ORM does not directly support FULLTEXT indexes, what are the recommended best practices or workarounds for implementing full-text search functionality with MySQL in a Drizzle ORM setup? Any insights, code examples, or references to documentation would be immensely helpful. Additionally, I'm open to suggestions for alternative methods within the Drizzle ORM and MySQL framework if direct support for FULLTEXT indexes is not available. Thank you for your assistance!

5 replies

Gaming

Programming