"There is not enough information to infer relation" with many to many query

Hey there, I'm encountering the following error message when trying to do a many to many query:

ERR error: There is not enough information to infer relation "locations.tags"

The query:

const locationsWithTags = (await db.query.locations.findMany({
    orderBy: (locations, { asc }) => [asc(locations.name)],
    with: {
      tags: true,
    },
  }))

const locationsWithTags = (await db.query.locations.findMany({
    orderBy: (locations, { asc }) => [asc(locations.name)],
    with: {
      tags: true,
    },
  }))

The schema:

export const locations = sqliteTable('locations', {
  id: text()
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text().notNull(),
  slug: text().notNull().unique(),
  country: text().notNull().default('us'),
});

export const locationsRelations = relations(locations, ({ one, many }) => ({
  tags: many(locationsToTags),
}));

export const tags = sqliteTable('tags', {
  id: text()
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text({ mode: 'json' }).$type<Record<string, string>>().notNull(),
  slug: text().notNull().unique(),
});

export const tagsRelations = relations(locations, ({ many }) => ({
  locations: many(locationsToTags),
}));

export const locationsToTags = sqliteTable(
  'locations_to_tags',
  {
    locationId: text('location_id')
      .notNull()
      .references(() => locations.id),
    tagId: text('tag_id')
      .notNull()
      .references(() => tags.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.locationId, t.tagId] }),
  }),
);

export const locationsToTagsRelations = relations(
  locationsToTags,
  ({ many }) => ({
    tags: many(tags),
    locations: many(locations),
  }),
);

export const locations = sqliteTable('locations', {
  id: text()
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text().notNull(),
  slug: text().notNull().unique(),
  country: text().notNull().default('us'),
});

export const locationsRelations = relations(locations, ({ one, many }) => ({
  tags: many(locationsToTags),
}));

export const tags = sqliteTable('tags', {
  id: text()
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text({ mode: 'json' }).$type<Record<string, string>>().notNull(),
  slug: text().notNull().unique(),
});

export const tagsRelations = relations(locations, ({ many }) => ({
  locations: many(locationsToTags),
}));

export const locationsToTags = sqliteTable(
  'locations_to_tags',
  {
    locationId: text('location_id')
      .notNull()
      .references(() => locations.id),
    tagId: text('tag_id')
      .notNull()
      .references(() => tags.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.locationId, t.tagId] }),
  }),
);

export const locationsToTagsRelations = relations(
  locationsToTags,
  ({ many }) => ({
    tags: many(tags),
    locations: many(locations),
  }),
);

Not sure what I'm doing wrong here. I tried a few variations of the findMany query and naming relations differently but nothing worked so far. Any idea what's wrong? Thanks!

2 Replies

scape•4d ago

First, your schema is wrong. The intermediate table (the one, that links two tables for many-to-many relationship) can only have one to one reference with the ones it links. In your casez locationsToTagsRelations will have one(tags) & one(locations). Then, if you want to get all the locations with tags, you can use joins / with in findMany on your intermediate table

OmesepelepeOP•4d ago

That last locationsToTagsRelations relation bit was initially missing and I added it because I read that relations have to be made on both sides. I missed that I defined it as many in there instead of one. Simply changing it to the correct relation type worked right away, didn't have to change the query. Here's the updated code for reference:

export const locationsToTagsRelations = relations(
  locationsToTags,
  ({ one }) => ({
    tag: one(tags, {
      fields: [locationsToTags.tagId],
      references: [tags.id],
    }),
    location: one(locations, {
      fields: [locationsToTags.locationId],
      references: [locations.id],
    }),
  }),
);

export const locationsToTagsRelations = relations(
  locationsToTags,
  ({ one }) => ({
    tag: one(tags, {
      fields: [locationsToTags.tagId],
      references: [tags.id],
    }),
    location: one(locations, {
      fields: [locationsToTags.locationId],
      references: [locations.id],
    }),
  }),
);

Thank you for the help!

Gaming

Programming

"There is not enough information to infer relation" with many to many query