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
Here's me proving that the tag is associated.
When I console log the
(the reason the IDs don't match is that I create a fresh database for every test.
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
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: []
}
]
}
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.