// 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);