Drizzle Team•5mo ago

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•5mo 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•5mo 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.

Gaming

Programming

Query `with` returning no results?