How to use inner join with rqb?

Hello there! Having the following schema
export const proyectos = mysqlTable('proyecto', {
codigo: int('codigo').primaryKey()
});

export const plataformas = mysqlTable(
'plataforma',
{
codigo: int('codigo').notNull(),
plataforma: int('plataforma').notNull()
},
table => {
return {
pk: primaryKey({ columns: [table.codigo, table.plataforma] })
};
}
);

export const plataformaMantenedor = mysqlTable('plataforma_mantenedor', {
id: int('id').primaryKey(),
nombre: varchar('nombre', { length: 25 }).notNull()
});

export const proyectosRelations = relations(proyectos, ({ many }) => ({
plataformas: many(plataformas)
}));

export const plataformaRelations = relations(plataformas, ({ one }) => ({
proyectos: one(proyectos, {
fields: [plataformas.codigo],
references: [proyectos.codigo]
}),
plataformaMantenedor: one(plataformaMantenedor, {
fields: [plataformas.plataforma],
references: [plataformaMantenedor.id]
})
}));
export const proyectos = mysqlTable('proyecto', {
codigo: int('codigo').primaryKey()
});

export const plataformas = mysqlTable(
'plataforma',
{
codigo: int('codigo').notNull(),
plataforma: int('plataforma').notNull()
},
table => {
return {
pk: primaryKey({ columns: [table.codigo, table.plataforma] })
};
}
);

export const plataformaMantenedor = mysqlTable('plataforma_mantenedor', {
id: int('id').primaryKey(),
nombre: varchar('nombre', { length: 25 }).notNull()
});

export const proyectosRelations = relations(proyectos, ({ many }) => ({
plataformas: many(plataformas)
}));

export const plataformaRelations = relations(plataformas, ({ one }) => ({
proyectos: one(proyectos, {
fields: [plataformas.codigo],
references: [proyectos.codigo]
}),
plataformaMantenedor: one(plataformaMantenedor, {
fields: [plataformas.plataforma],
references: [plataformaMantenedor.id]
})
}));
This query gives me all the "proyectos" when I actually want only those that match "plataformas" with id (aka plataforma) "[10,12]" because drizzle is using LEFT JOIN to join the tables.
let s = this.db.query.proyectos
.findMany({
with: {
plataformas: {
with: {
plataformaMantenedor: true
},
where: _ => inArray(plataformas.plataforma, [10, 12])
}
},

limit: 25,
offset: 0
})
let s = this.db.query.proyectos
.findMany({
with: {
plataformas: {
with: {
plataformaMantenedor: true
},
where: _ => inArray(plataformas.plataforma, [10, 12])
}
},

limit: 25,
offset: 0
})
Is there a way to use "inner join" to filter all the rows with the inner where? I'm still learning drizzle so I may be making a mistake anywhere else. I hope I made myself clear enough (english is not my main language) Thanks in advance!
4 Replies
ericmartinezr
ericmartinezrOP13mo ago
Any idea about this one? Is my schema the problem or something else? Is it a bug? Something not yet covered by the drizzle? Am I misusing it?
Angelelz
Angelelz13mo ago
This can be done by filtering by the nested relation Unfortunately, filtering by the nested relation was removed in v0.28 But You could achieve a similar result with a subquery
let s = this.db.query.proyectos
.findMany({
with: {
plataformas: {
with: {
plataformaMantenedor: true
},
where: _ => inArray(plataformas.plataforma, [10, 12])
}
},
where: inArray(projectos.codigo, db.select({ id: plataformas.codigo }).from(plataformas).where(inArray(plataformas.plataforma, [10, 12])),
limit: 25,
offset: 0
})
let s = this.db.query.proyectos
.findMany({
with: {
plataformas: {
with: {
plataformaMantenedor: true
},
where: _ => inArray(plataformas.plataforma, [10, 12])
}
},
where: inArray(projectos.codigo, db.select({ id: plataformas.codigo }).from(plataformas).where(inArray(plataformas.plataforma, [10, 12])),
limit: 25,
offset: 0
})
I haven't tested this, so your milleage might vary, but it shopuld point you in the right direction
ericmartinezr
ericmartinezrOP13mo ago
Thanks a lot Angelelz!!
Angelelz
Angelelz13mo ago
No problem By the way, this query should be fine for small enough tables If you get to a performance bottleneck, you are going to drop to the CRUD API and maybe use a CTE
Want results from more Discord servers?
Add your server