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.
Another try with select:
I believe there should be some kind of
// 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_aggjson_agg, or json_create_arrayjson_create_array methods?