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

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

Gaming

Programming