query where condition on foreign key relation

i have a table with a foreign key (one relation) to another table:
export const mapRegions = sqliteTable(
'map_regions',
{
id: integer('id').primaryKey(),
mapId: integer('map_id')
.notNull()
.references(() => maps.id, { onDelete: 'cascade' }),
regionId: integer('region_id')
.notNull()
.references(() => regions.id, { onDelete: 'cascade' })
},
(t) => ({
mapRegionUnique: uniqueIndex('map_region_unique').on(t.mapId, t.regionId)
})
);

export const mapRegionsRelations = relations(mapRegions, ({ one }) => ({
map: one(maps, { fields: [mapRegions.mapId], references: [maps.id] }),
region: one(regions, { fields: [mapRegions.regionId], references: [regions.id] })
}));
export const mapRegions = sqliteTable(
'map_regions',
{
id: integer('id').primaryKey(),
mapId: integer('map_id')
.notNull()
.references(() => maps.id, { onDelete: 'cascade' }),
regionId: integer('region_id')
.notNull()
.references(() => regions.id, { onDelete: 'cascade' })
},
(t) => ({
mapRegionUnique: uniqueIndex('map_region_unique').on(t.mapId, t.regionId)
})
);

export const mapRegionsRelations = relations(mapRegions, ({ one }) => ({
map: one(maps, { fields: [mapRegions.mapId], references: [maps.id] }),
region: one(regions, { fields: [mapRegions.regionId], references: [regions.id] })
}));
in a query i want to get a mapRegions + where map satisfies specific conditions, something like this:
await db.query.mapRegions.findMany({
with: {map: {where: and(eq(maps.isPublished, true), eq(maps.isCommunity, true))}}
})
await db.query.mapRegions.findMany({
with: {map: {where: and(eq(maps.isPublished, true), eq(maps.isCommunity, true))}}
})
however, typescipt is unhappy about the where and the conditions seem to not be applied can someone please advice me on how to do it correctly?
1 Reply
plurk
plurkOP19h ago
it is fairly easy to make this query using raw sql or select, but in my case i need to use drizzle query
Want results from more Discord servers?
Add your server