Query `with` returning no results?

Hi! Wondering if anyone can see what I'm doing wrong here... I've got a recipes table that had a relation with recipeTags which, in turn, is related to tags. I've got a query that's returning the correct recipes, but the recipeTags is empty: I'm looking at: https://orm.drizzle.team/docs/rqb#find-many
const foundRecipes = await db.query.recipes.findMany({
...rest,
where: eq(recipes.submittedBy, userId),
columns: {
id: true,
...
},
with: { recipeTags: { with: { tags: true } } },
orderBy: (recipes) => [desc(recipes.createdDate)],
});
const foundRecipes = await db.query.recipes.findMany({
...rest,
where: eq(recipes.submittedBy, userId),
columns: {
id: true,
...
},
with: { recipeTags: { with: { tags: true } } },
orderBy: (recipes) => [desc(recipes.createdDate)],
});
// schema.ts

export const recipes = sqliteTable('recipes', {
id: text('id').$default(() => createId()).primaryKey(),
...
});

export const recipesRelations = relations(recipes, ({ one, many }) => ({
recipeTags: many(recipeTags),
...
}));

export const recipeTags = sqliteTable('recipe_tags', {
recipeId: text('recipe_id'),
tagId: text('tag_id'),
...
}, (table) => ({
pk: primaryKey({ columns: [table.recipeId, table.tagId] }),
}));

export const recipeTagsRelations = relations(recipeTags, ({ one }) => ({
recipes: one(recipes, { fields: [recipeTags.recipeId], references: [recipes.id] }),
tags: one(tags, { fields: [recipeTags.tagId], references: [tags.id] }),
}));

export const tags = sqliteTable('tags', {
id: text('id').$default(() => createId()).primaryKey(),
name: text('name').notNull(),
isPrivate: integer('is_private', { mode: 'boolean' }).$default(() => false),
userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
createdDate: text('created_date').$default(() => toISO8601(new Date())),
updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
});

export const tagsRelations = relations(tags, ({ one, many }) => ({
recipeTags: many(recipeTags),
menuTags: many(menuTags),
menuCourseTags: many(menuCourseTags),
user: one(users, { fields: [tags.userId], references: [users.id] }),
}));
// schema.ts

export const recipes = sqliteTable('recipes', {
id: text('id').$default(() => createId()).primaryKey(),
...
});

export const recipesRelations = relations(recipes, ({ one, many }) => ({
recipeTags: many(recipeTags),
...
}));

export const recipeTags = sqliteTable('recipe_tags', {
recipeId: text('recipe_id'),
tagId: text('tag_id'),
...
}, (table) => ({
pk: primaryKey({ columns: [table.recipeId, table.tagId] }),
}));

export const recipeTagsRelations = relations(recipeTags, ({ one }) => ({
recipes: one(recipes, { fields: [recipeTags.recipeId], references: [recipes.id] }),
tags: one(tags, { fields: [recipeTags.tagId], references: [tags.id] }),
}));

export const tags = sqliteTable('tags', {
id: text('id').$default(() => createId()).primaryKey(),
name: text('name').notNull(),
isPrivate: integer('is_private', { mode: 'boolean' }).$default(() => false),
userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
createdDate: text('created_date').$default(() => toISO8601(new Date())),
updatedDate: text('updated_date').$default(() => toISO8601(new Date())),
});

export const tagsRelations = relations(tags, ({ one, many }) => ({
recipeTags: many(recipeTags),
menuTags: many(menuTags),
menuCourseTags: many(menuCourseTags),
user: one(users, { fields: [tags.userId], references: [users.id] }),
}));
Drizzle ORM - Query
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
2 Replies
stephen
stephenOP7mo ago
Here's me proving that the tag is associated.
sqlite> select * from recipes where title = 'Pumpkin Pie';
vswb2i9sbtcweiaj1gjmnqob||A delicious pumpkin pie recipe|0|["Preheat oven to 425 F.","Whisk pumpkin, sweetened condensed milk, eggs, spices and salt in medium bowl until smooth.","Pour into crust. Bake 15 minutes.","Reduce oven temperature to 350 F and continue baking 35 to 40 minutes or until knife inserted 1 inch from crust comes out clean.","Cool. Garnish as desired. Store leftovers covered in refrigerator."]||||NYTimes Cooking|https://cooking.nytimes.com/recipes/1015622-pumpkin-pie|bmktsg6ssujrt0yvhy6x8ha9|Pumpkin Pie||2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00

sqlite> select * from recipe_tags where recipe_id = 'vswb2i9sbtcweiaj1gjmnqob';
vswb2i9sbtcweiaj1gjmnqob|g1b9ahshzfsbs1kgtxq9u1ib|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
vswb2i9sbtcweiaj1gjmnqob|huysxg747uz46cisjk6ujboi|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
vswb2i9sbtcweiaj1gjmnqob|opyfmuutya73gbcfd8wjmcfw|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
vswb2i9sbtcweiaj1gjmnqob|qrg0o1r1i558tcbvo9q7eisd|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
vswb2i9sbtcweiaj1gjmnqob|vfhq9iw3gi09464otnzymvr9|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00

sqlite> select * from tags where id = 'g1b9ahshzfsbs1kgtxq9u1ib';
g1b9ahshzfsbs1kgtxq9u1ib|thanksgiving|0||2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
sqlite> select * from recipes where title = 'Pumpkin Pie';
vswb2i9sbtcweiaj1gjmnqob||A delicious pumpkin pie recipe|0|["Preheat oven to 425 F.","Whisk pumpkin, sweetened condensed milk, eggs, spices and salt in medium bowl until smooth.","Pour into crust. Bake 15 minutes.","Reduce oven temperature to 350 F and continue baking 35 to 40 minutes or until knife inserted 1 inch from crust comes out clean.","Cool. Garnish as desired. Store leftovers covered in refrigerator."]||||NYTimes Cooking|https://cooking.nytimes.com/recipes/1015622-pumpkin-pie|bmktsg6ssujrt0yvhy6x8ha9|Pumpkin Pie||2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00

sqlite> select * from recipe_tags where recipe_id = 'vswb2i9sbtcweiaj1gjmnqob';
vswb2i9sbtcweiaj1gjmnqob|g1b9ahshzfsbs1kgtxq9u1ib|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
vswb2i9sbtcweiaj1gjmnqob|huysxg747uz46cisjk6ujboi|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
vswb2i9sbtcweiaj1gjmnqob|opyfmuutya73gbcfd8wjmcfw|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
vswb2i9sbtcweiaj1gjmnqob|qrg0o1r1i558tcbvo9q7eisd|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
vswb2i9sbtcweiaj1gjmnqob|vfhq9iw3gi09464otnzymvr9|2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00

sqlite> select * from tags where id = 'g1b9ahshzfsbs1kgtxq9u1ib';
g1b9ahshzfsbs1kgtxq9u1ib|thanksgiving|0||2024-04-14T16:26:27-05:00|2024-04-14T16:26:27-05:00
When I console log the foundRecipes, however, I see:
{
foundRecipes: [
{
id: 'xlo9mpndtshrghoj21fxrp2p',
title: 'Pumpkin Pie',
description: 'A delicious pumpkin pie recipe',
author: null,
recipeTags: []
}
]
}
{
foundRecipes: [
{
id: 'xlo9mpndtshrghoj21fxrp2p',
title: 'Pumpkin Pie',
description: 'A delicious pumpkin pie recipe',
author: null,
recipeTags: []
}
]
}
(the reason the IDs don't match is that I create a fresh database for every test.
hazamashoken
hazamashoken7mo ago
try running https://orm.drizzle.team/drizzle-studio/overview and check if there's entities in the recipeTags table ?
Drizzle ORM - Meet Drizzle Studio
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Want results from more Discord servers?
Add your server