rfrancociavaldini Posts - Answer Overflow

rfrancociavaldini

•Created by rfrancociavaldini on 9/16/2023 in #help

Relations include query

For example If I have the following schema:

export const sales = mysqlTable("Sales", {
    id: int("id").autoincrement().notNull(),
    startingDate: datetime("starting_date", { mode: 'string'}).notNull(),
    expirationDate: datetime("expiration_date", { mode: 'string'}),
    bundleId: int("BundleId").references(() => bundles.id, { onDelete: "set null", onUpdate: "cascade" } ),
},
(table) => {
    return {
        bundleId: index("BundleId").on(table.bundleId),
        salesId: primaryKey(table.id),
    }
});

export const bundles = mysqlTable("Bundles", {
    id: int("id").autoincrement().notNull(),
    amount: float("amount").notNull(),
    name: varchar("name", { length: 255 }),
},
(table) => {
    return {
        bundlesId: primaryKey(table.id),
    }
});

I'm trying the following query and it throws me an error:

await db.query.sales.findFirst({
    where: eq(sales.businessUserId, id),
    orderBy: desc(sales.id),
    with: {
        bundles: true
    },
  });

The error is undefined is not an object (evaluating 'relation.referencedTable'). How can I get a Sale object with it's Bundle complete object instead of only the BundleId? To avoid having to do another query.

7 replies

DTDrizzle Team

•Created by rfrancociavaldini on 9/15/2023 in #help

Drizzle pagination with count

Hi, is there a way to have a paginated query that also count all posible results based on a condition? I have the following currently:

const result = await db.query.businessUsers
          .findMany({
            orderBy: businessUsers.id,
            limit: limit,
            offset: offset,
            where: or(
              like(businessUsers.id,

%${text}%

),
              like(businessUsers.name,

%${text}%

),
              like(businessUsers.subdomain,

%${text}%

)
            ),
          })

const count = await db
      .select({ count: sql<number>

count(*)

 })
      .from(businessUsers)
      .where(
        or(
          like(businessUsers.id,

%${text}%

),
          like(businessUsers.name,

%${text}%

),
          like(businessUsers.subdomain,

%${text}%

)
        )
      )

Is there a way to do this counting without having to repeat the whole query again?

4 replies

DTDrizzle Team

•Created by rfrancociavaldini on 9/13/2023 in #help

MySQL Table Schemas

I need to generate the following structure of tables:

1. Sales
2.Sales
....

Currently if I use what the docs say regarding the configuration of schemas, when I run drizzle-kit generate:mysql I get the following sql file: CREATE DATABASE 127

;
--> statement-breakpoint
CREATE TABLE

127.sales

 int AUTO_INCREMENT NOT NULL,

double

 double DEFAULT 0,
    CONSTRAINT

sales_id PRIMARY KEY(id

)
);

What I'm looking for is not to create a database for each tenant but only create their set of tables. This is my schema definition:

import { mysqlTable, int, double, mysqlSchema } from "drizzle-orm/mysql-core";

export const mySchema = mysqlSchema("dynamic_value_here")

export const sales = mySchema.table("sales", {
  id: int("id").primaryKey().autoincrement(),
  total_price_dlrs: double("double").default(0),
});
export type Sale = typeof sales.$inferSelect;
export type NewSale = typeof sales.$inferInsert;

Could someone please help me and point me in the right direction? Thanks in advance

12 replies

Gaming

Programming