Drizzle Team•2w ago

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?

1 Reply

Maastonakki•2w ago

Actually got this working with this:

const anotherContractor = await db.query.contractors.findMany({
  limit: 1,
  where: eq(contractors.id, 364),
  extras: {
    rating: sql<number>`
COALESCE((
  SELECT ROUND(AVG("job_job"."rating"), 2) 
  FROM ${jobs} 
  WHERE "job_job"."contractor_id" = ${contractors.id}
), 0)`.as("rating"),
  },
  with: {
    user: {
      columns: {
        id: true,
        email: true,
        username: true,
        slug: true,
      },
    },
    languages: {
      with: {
        language: true,
      },
    },
    jobs: {
      columns: { id: true },
    },
  },
});

const anotherContractor = await db.query.contractors.findMany({
  limit: 1,
  where: eq(contractors.id, 364),
  extras: {
    rating: sql<number>`
COALESCE((
  SELECT ROUND(AVG("job_job"."rating"), 2) 
  FROM ${jobs} 
  WHERE "job_job"."contractor_id" = ${contractors.id}
), 0)`.as("rating"),
  },
  with: {
    user: {
      columns: {
        id: true,
        email: true,
        username: true,
        slug: true,
      },
    },
    languages: {
      with: {
        language: true,
      },
    },
    jobs: {
      columns: { id: true },
    },
  },
});

The interesting part is, that if I try to use ${jobs.rating} and ${jobs.contractorId} in COALESCE, those get resolved for table contractors

Gaming

Programming

Calculating average of joined table column