Maastonakki Posts - Answer Overflow

Maastonakki

Explore posts from servers

•Created by Maastonakki on 10/12/2024 in #help

Calculating average of joined table column

I'm trying to achieve select/query, where DB would automatically calculate average rating for my contractor. Here are my two attempts, but both have some issues, when I'm also trying to fetch relations for contractor.

// Gives error: PostgresError: column contractors.rating does not exist
const anotherContractor = await db.query.contractors.findMany({
  limit: 1,
  where: eq(contractors.id, 364),
  extras: {
    averageRating: sql`COALESCE(ROUND(AVG(${jobs.rating}), 2),0)`.as(
      "averageRating"
    ),
  },
  with: {
    user: {
      columns: {
        id: true,
        email: true,
        username: true,
        slug: true,
      },
    },
    languages: true,
    jobs: {
      columns: { rating: true },
    },
  },
});

// Gives error: PostgresError: column contractors.rating does not exist
const anotherContractor = await db.query.contractors.findMany({
  limit: 1,
  where: eq(contractors.id, 364),
  extras: {
    averageRating: sql`COALESCE(ROUND(AVG(${jobs.rating}), 2),0)`.as(
      "averageRating"
    ),
  },
  with: {
    user: {
      columns: {
        id: true,
        email: true,
        username: true,
        slug: true,
      },
    },
    languages: true,
    jobs: {
      columns: { rating: true },
    },
  },
});

Another try with select:

// This does work, but languages field contains only single language, instead of array
// I believe this would be the easiest to get working, but just can't figure out how.
const dbContractors = await db
  .select({
    ...getTableColumns(contractors),
    firstName: users.firstName,
    lastName: users.lastName,
    flags: users.flags,
    user: {
      id: users.id,
      email: users.email,
      username: users.username,
      slug: users.slug,
    },
    languages,
    rating: sql`COALESCE(ROUND(AVG(${jobs.rating}), 2),0)`.as("rating"),
  })
  .from(contractors)
  .leftJoin(users, eq(contractors.userId, users.id))
  .leftJoin(
    contractorLanguages,
    eq(contractors.id, contractorLanguages.contractorId),
  )
  .leftJoin(languages, eq(contractorLanguages.languageId, languages.id))
  .leftJoin(jobs, eq(jobs.contractorId, contractors.id))
  .groupBy(
    contractors.id,
    users.id,
    users.firstName,
    users.lastName,
    users.flags,
    languages.id,
  )
  .where(eq(contractors.id, 364))
  .orderBy(asc(users.lastName))
  .limit(1);

// This does work, but languages field contains only single language, instead of array
// I believe this would be the easiest to get working, but just can't figure out how.
const dbContractors = await db
  .select({
    ...getTableColumns(contractors),
    firstName: users.firstName,
    lastName: users.lastName,
    flags: users.flags,
    user: {
      id: users.id,
      email: users.email,
      username: users.username,
      slug: users.slug,
    },
    languages,
    rating: sql`COALESCE(ROUND(AVG(${jobs.rating}), 2),0)`.as("rating"),
  })
  .from(contractors)
  .leftJoin(users, eq(contractors.userId, users.id))
  .leftJoin(
    contractorLanguages,
    eq(contractors.id, contractorLanguages.contractorId),
  )
  .leftJoin(languages, eq(contractorLanguages.languageId, languages.id))
  .leftJoin(jobs, eq(jobs.contractorId, contractors.id))
  .groupBy(
    contractors.id,
    users.id,
    users.firstName,
    users.lastName,
    users.flags,
    languages.id,
  )
  .where(eq(contractors.id, 364))
  .orderBy(asc(users.lastName))
  .limit(1);

I believe there should be some kind of json_agg, or json_create_array methods?

3 replies

TTCTheo's Typesafe Cult

•Created by Maastonakki on 6/21/2024 in #questions

How T3Stack env.js actually works?

I'm trying to figure out what kind of black magic that createEnv function does behind the curtains, when I'm running my application. The reason is that I'm having seed.ts at the root of my project, which I'm using to seed the database, by calling tsx seed.ts. Problem is, that if I import env.js into that seeder-file, it does not have env variables available. Same thing when I'm trying to call Encryption.encrypt() method from one of my lib-files, to encrypt some data, and that lib-file imports env.js, I get error that environmental variable are not set. Should I somehow load the .env file manually on tsx call, or what might I be missing?

6 replies

DTDrizzle Team

•Created by Maastonakki on 6/18/2024 in #help

How to make all relations be deleted when user is removed?

Hello! I'm trying to get my head around for table relations, and just can't seem to get this figured out. I've table for users, and user sessions. When I'm trying to delete a user, postgres throws error, that delete violates foreign key constraint on sessions. What might be proper way to define this relations? Below is my schema.

export const users = createTable(
  "user",
  {
    id: serial("id").primaryKey(),
    email: varchar("email", { length: 256 }).unique().notNull(),
    password: varchar("password", { length: 256 }).notNull(),
    role: userRoleEnum("role").notNull(),
    firstName: varchar("first_name", { length: 256 }),
    lastName: varchar("last_name", { length: 256 }),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updated_at"),
    deletedAt: timestamp("deleted_at"),
    passwordResetToken: varchar("password_reset_token", {
      length: 256,
    }),
    passwordResetTokenExpiresAt: timestamp("password_reset_token_expires_at", {
      withTimezone: true,
      mode: "date",
    }),
    allowLogin: boolean("allow_login").default(true).notNull(),
  },
  (table) => ({
    emailIndex: index("email_idx").on(table.email),
  }),
);

export const sessions = createTable("session", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  userId: integer("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  expiresAt: timestamp("expires_at", {
    withTimezone: true,
    mode: "date",
  }).notNull(),
});

export const users = createTable(
  "user",
  {
    id: serial("id").primaryKey(),
    email: varchar("email", { length: 256 }).unique().notNull(),
    password: varchar("password", { length: 256 }).notNull(),
    role: userRoleEnum("role").notNull(),
    firstName: varchar("first_name", { length: 256 }),
    lastName: varchar("last_name", { length: 256 }),
    createdAt: timestamp("created_at")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    updatedAt: timestamp("updated_at"),
    deletedAt: timestamp("deleted_at"),
    passwordResetToken: varchar("password_reset_token", {
      length: 256,
    }),
    passwordResetTokenExpiresAt: timestamp("password_reset_token_expires_at", {
      withTimezone: true,
      mode: "date",
    }),
    allowLogin: boolean("allow_login").default(true).notNull(),
  },
  (table) => ({
    emailIndex: index("email_idx").on(table.email),
  }),
);

export const sessions = createTable("session", {
  id: varchar("id", {
    length: 255,
  }).primaryKey(),
  userId: integer("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  expiresAt: timestamp("expires_at", {
    withTimezone: true,
    mode: "date",
  }).notNull(),
});

1 replies

TTCTheo's Typesafe Cult

•Created by Maastonakki on 5/16/2024 in #questions

Database seeding with latest T3 Stack (/w Drizzle) and Faker

Hello! I've been now working a project based on latest Theo's The Modern React Tutorial (https://www.youtube.com/watch?v=d5x0JCZbAJs) Project that I'm working requires migration from existing system to new one, after it is ready. And before that I would like to use a lot of seeded data in database, for testing UI/UX and other functionalities. Issues that I have run into, are that @faker-js/faker isn't able to provide proper types for typescript, and what I found from their documentation, for Typescript support tsconfig.json should have moduleResolution: "node", while on T3 stack it's set as Bundler. moduleResolution goes out of my understanding, and just blindly changing that to node, obviously seems to break rest of application. The second thing is with initialising Drizzle connection to Vercel Postgres. I've checked tutorial mentioned previously here on https://dev.to/anasrin/seeding-database-with-drizzle-orm-fga but in my opinion, that feels so much extra hustle (half a dozen extra packages), for just getting db seeding done, while all db-connections are done so elegantly in the stack. I would imagine that many others are also looking for faked data for their development workflow, could there maybe be demand for great YT tutorial on this?

2 replies

Gaming

Programming