I have 2 tables/entities: Cases and Comments. A Case can have many Comments; a Comment can belong to one Case. I am trying to query Cases with Comments, as in:

  const caseData = await db.query.cases.findFirst({
    where: eq(Cases.id, id),
    with: {
      client: {
        with: {
          contacts: {
            with: {
              contact: true,
            },
          },
        },
      },
      property: {
        with: {
          contacts: {
            with: {
              contact: true,
            },
          },
        },
      },
      defendant: true,
      opposingCounsel: true,
      comments: true,
    },
  });

I am getting the error There is not enough information to infer relation "casesments".

I have followed the Drizzle documentation for one-to-many relations and SakuraDev's tutorial, which closely follows the Drizzle documentation. I have tried various solutions mentioned on S.O. I have tried using join tables (which seems ridiculous and isn't mentioned as required in the docs). I have tried adding relationName to both relations definitions (I've tried using both 'comments' and 'cases' here).

I am expecting this to send back a Case which includes an array with any associated Comments.

My Case table is:

export const Cases = pgTable(
  "cases",
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    createdAt: timestamp({ withTimezone: true }).defaultNow(),
    updatedAt: timestamp({ withTimezone: true })
    .defaultNow()
    .$onUpdate(() => new Date()),

    [ . . . a bunch of other columns/fields . . . ]

  },
  (table) => ({ ...baseIndexes(table) })
);

The Case relations are:

export const CasesRelations = relations(Cases, ({ one, many }) => ({
  defendant: one(Defendants, {
    fields: [Cases.defendantId],
    references: [Defendants.id],
  }),
  property: one(Properties, {
    fields: [Cases.propertyId],
    references: [Properties.id],
  }),
  client: one(Clients, {
    fields: [Cases.clientId],
    references: [Clients.id],
  }),
  opposingCounsel: one(Contacts, {
    fields: [Cases.opposingCounselId],
    references: [Contacts.id],
  }),
  comments: many(Comments),
}));

My Comments table is:

export const Comments = pgTable(
  "comments",
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    createdAt: timestamp({ withTimezone: true }).defaultNow(),
    updatedAt: timestamp({ withTimezone: true })
    .defaultNow()
    .$onUpdate(() => new Date()),

    caseId: integer().references(() => Cases.id),
    description: text(),
    type: text(),
  },
  (table) => ({ ...baseIndexes(table) })
);

The Comments relations are:

export const CommentsRelations = relations(Comments, ({ one }) => ({
  case: one(Cases, {
    fields: [Comments.caseId],
    references: [Cases.id],
  }),
}));

Related Case controllers:

caseRoutes.get("", async (req: Request, res: Response) => {
  const where: (SQL<unknown> | undefined)[] = [];
  [ . . . a bunch of values pulled off of the request query . . . ]

  [ . . . push those values to where . . . ]

  const search = req.query.search as string;
  if (search) {
    for (const term of search.split(" ")) {
      where.push(
        or(
          ilike(Cases.caseNumber, `%${term}%`),
          ilike(Clients.name, `%${term}%`),
          ilike(Defendants.firstName, `%${term}%`),
          ilike(Defendants.middleName, `%${term}%`),
          ilike(Defendants.lastName, `%${term}%`),
          ilike(Defendants.businessName, `%${term}%`),
          ilike(Defendants.address1, `%${term}%`),
          ilike(Defendants.address2, `%${term}%`),
          ilike(Defendants.city, `%${term}%`),
          ilike(Defendants.zip, `%${term}%`),
          ilike(Properties.name, `%${term}%`)
        )
      );
    }
  }

  const searchQuery = db
    .select({ id: Cases.id })
    .from(Cases)
    .leftJoin(Properties, eq(Cases.propertyId, Properties.id))
    .leftJoin(Clients, eq(Cases.clientId, Clients.id))
    .leftJoin(Defendants, eq(Cases.defendantId, Defendants.id))
    .leftJoin(Comments, eq(Cases.id, Comments.caseId))
    .where(and(...where));

  const casesConfig: DBQueryConfig = {
    limit,
    offset,
    orderBy: [desc(Cases.createdAt), desc(Cases.id)],
    where: inArray(Cases.id, searchQuery),
    with: {
      client: true,
      property: true,
      defendant: true,
      comments: true,
    },
  };

  const count = await db.$count(Cases, inArray(Cases.id, searchQuery));
  const cases = await db.query.cases.findMany(casesConfig);

  res.json({ cases, count });
});

caseRoutes.get("/:id", async (req: Request, res: Response) => {
  const id = Number(req.params.id);
  const decryptValues = req.query.decrypt === "true";

  if (!id) {
    res.status(404).json({ error: "Case not found" });
    return;
  }

  const caseData = await db.query.cases.findFirst({
    where: eq(Cases.id, id),
    with: {
      client: {
        with: {
          contacts: {
            with: {
              contact: true,
            },
          },
        },
      },
      property: {
        with: {
          contacts: {
            with: {
              contact: true,
            },
          },
        },
      },
      defendant: true,
      opposingCounsel: true,
      comments: true,
    },
  });

  if (decryptValues && caseData?.additionalDefendants?.length) {
    for (const additionalDefendant of caseData.additionalDefendants) {
      if (additionalDefendant.ssn) {
        additionalDefendant.ssn = await decrypt(additionalDefendant.ssn);
      }
    }
  }

  res.json(caseData);
});