Group related items in relation query
Hey ! Im having a bit of trouble trying to query with a many-to-many relation
const proffesionals = await db
.select()
.from(proffesionalTable)
.where(eq(proffesionalTable.userId, userId))
.leftJoin(
proffesionalServiceTable,
eq(proffesionalServiceTable.proffesionalId, proffesionalTable.id)
)
.all();
my output:
[
{
Proffesional: {
id: 1,
userId: '17g2t59h',
firstName: 'martin',
lastName: 'Diaz',
availability: [Object]
},
ProffesionalService: null
},
{
Proffesional: {
id: 2,
userId: '17g2t59h',
firstName: 'asd',
lastName: 'asd',
availability: [Object]
},
ProffesionalService: null
},
{
Proffesional: {
id: 3,
userId: '17g2t59h',
firstName: 'testestes',
lastName: 'test',
availability: [Object]
},
ProffesionalService: { proffesionalId: 3, serviceId: 4 }
},
{
Proffesional: {
id: 3,
userId: '17g2t59h',
firstName: 'testestes',
lastName: 'test',
availability: [Object]
},
ProffesionalService: { proffesionalId: 3, serviceId: 5 }
}
]
What im trying to achieve:
[
{
Proffesional: {
id: 1,
userId: '17g2t59h',
firstName: 'martin',
lastName: 'Diaz',
availability: [Object]
},
ProffesionalService: []
},
{
Proffesional: {
id: 2,
userId: '17g2t59h',
firstName: 'asd',
lastName: 'asd',
availability: [Object]
},
ProffesionalService: []
},
{
Proffesional: {
id: 3,
userId: '17g2t59h',
firstName: 'testestes',
lastName: 'test',
availability: [Object]
},
ProffesionalService: [4,5]
}
]3 Replies
If annyone have a similar problem i solved this way:
const proffesionals = db
.select({
id: proffesionalTable.id,
firstName: proffesionalTable.firstName,
lastName: proffesionalTable.lastName,
availability: proffesionalTable.availability,
serviceId: proffesionalServiceTable.serviceId
})
.from(proffesionalTable)
.where(eq(proffesionalTable.userId, userId))
.leftJoin(
proffesionalServiceTable,
eq(proffesionalServiceTable.proffesionalId, proffesionalTable.id)
)
.as('proffesionals');
const sq = await db
.select({
id: proffesionals.id,
firstName: proffesionals.firstName,
lastName: proffesionals.lastName,
availability: proffesionals.availability,
servicesIds: sql<number[]>json_group_array(${proffesionals.serviceId})
})
.from(proffesionals)
.groupBy(proffesionals.id);@tincho do you have any idea as to how to omit the entries where ProffesionalService is empty
Use
innerJoin