Drizzle Team•2y ago

Many to many - Planetscale

Anyone here has an example of declaring many to many relationship when using Planetscale? https://orm.drizzle.team/docs/rqb#many-to-many When using Planetscale this section doesn't work at all

Relational queries – DrizzleORM

Drizzle ORM | %s

19 Replies

Ahmed•16mo ago

Do you still need help ?

hankandre•14mo ago

I think I could actually use help here. I'd rather avoid doing things manually via a bunch of table joins, if I can avoid it.

Angelelz•14mo ago

What doesn't work in the many to many example in the docs? The important part for planetscale is to not use foreign keys. The example in the docs is not using foreign keys

l2ival•14mo ago

@Angelelz With this using relations Can you add additional columns when using relations()?

Angelelz•14mo ago

I don't understand the question You can have as many relations as you want

l2ival•14mo ago

Say i have a Workout with multiple activities, but i want to define an order on the relation so an activity is always in the same spot on the workout. My first thought was to add the column order on the "junction table" but maybe it should be on the activity table itself? And thought would it be possible to define on the relations okay disregard i think i got it, was confused move between the docs You need both the junction table and the relations, i just was looking at the highlighted portion in the doc

Angelelz•14mo ago

I would put the order column in the junction table Because the order will be unique per activity <-> workout

hankandre•14mo ago

I have a junction table and the relations setup, like the example, but in the junction table, usersToGroups, is referencing foreign keys. I can give my specific example in a couple hours. I’m AFK right now. Here's an example from a schema I wrote out that has a many-to-many relationship of books and authors:

import {
  text,
  bigint,
  varchar,
  serial,
  primaryKey,
} from 'drizzle-orm/mysql-core';
import { mysqlTable } from '../config';
import { createId } from '~/utils/create-nanoid';
import { relations } from 'drizzle-orm';

export const books = mysqlTable('books', {
  id: serial('id').primaryKey(),
  publicId: varchar('public_id', { length: 12 })
    .unique()
    .notNull()
    .$defaultFn(createId),
  title: text('title').notNull(),
});

export const bookRelations = relations(books, ({ many }) => ({
  authors: many(authors),
}));

export const authors = mysqlTable('authors', {
  id: serial('id').primaryKey(),
  publicId: varchar('public_id', { length: 12 })
    .unique()
    .notNull()
    .$defaultFn(createId),
  name: text('name').notNull(),
  biography: text('biography'),
});

export const authorRelations = relations(authors, ({ many }) => ({
  books: many(books),
}));

export const authorsToBooks = mysqlTable(
  'authors_to_books',
  {
    bookId: bigint('book_id', { mode: 'number' })
      .notNull()
      .references(() => books.id),
    authorId: bigint('author_id', { mode: 'number' })
      .notNull()
      .references(() => authors.id),
  },
  (t) => ({
    pk: primaryKey(t.bookId, t.authorId),
  }),
);

export const authorsToBooksRelations = relations(authorsToBooks, ({ one }) => ({
  book: one(books, {
    fields: [authorsToBooks.bookId],
    references: [books.id],
  }),
  author: one(authors, {
    fields: [authorsToBooks.authorId],
    references: [authors.id],
  }),
}));

import {
  text,
  bigint,
  varchar,
  serial,
  primaryKey,
} from 'drizzle-orm/mysql-core';
import { mysqlTable } from '../config';
import { createId } from '~/utils/create-nanoid';
import { relations } from 'drizzle-orm';

export const books = mysqlTable('books', {
  id: serial('id').primaryKey(),
  publicId: varchar('public_id', { length: 12 })
    .unique()
    .notNull()
    .$defaultFn(createId),
  title: text('title').notNull(),
});

export const bookRelations = relations(books, ({ many }) => ({
  authors: many(authors),
}));

export const authors = mysqlTable('authors', {
  id: serial('id').primaryKey(),
  publicId: varchar('public_id', { length: 12 })
    .unique()
    .notNull()
    .$defaultFn(createId),
  name: text('name').notNull(),
  biography: text('biography'),
});

export const authorRelations = relations(authors, ({ many }) => ({
  books: many(books),
}));

export const authorsToBooks = mysqlTable(
  'authors_to_books',
  {
    bookId: bigint('book_id', { mode: 'number' })
      .notNull()
      .references(() => books.id),
    authorId: bigint('author_id', { mode: 'number' })
      .notNull()
      .references(() => authors.id),
  },
  (t) => ({
    pk: primaryKey(t.bookId, t.authorId),
  }),
);

export const authorsToBooksRelations = relations(authorsToBooks, ({ one }) => ({
  book: one(books, {
    fields: [authorsToBooks.bookId],
    references: [books.id],
  }),
  author: one(authors, {
    fields: [authorsToBooks.authorId],
    references: [authors.id],
  }),
}));

When I attempt to push that schema I get this message from PlanetScale:

Error: VT10001: foreign key constraints are not allowed

Error: VT10001: foreign key constraints are not allowed

Presumably that's because of the references(() => books.id) and references(() => authors.id) usage. When I remove that, like it says you can do in the docs, I'm uncertain how I can write my query. Doing this results in an error (using tRPC) when attempting to launch Drizzle Studio:

export const bookRouter = createTRPCRouter({
  one: protectedProcedure.query(({ ctx: { db } }) => {
    return db.query.books.findFirst({
      where: eq(books.id, 1),
      with: {
        authors: true,
      },
    });
  }),
});

export const bookRouter = createTRPCRouter({
  one: protectedProcedure.query(({ ctx: { db } }) => {
    return db.query.books.findFirst({
      where: eq(books.id, 1),
      with: {
        authors: true,
      },
    });
  }),
});

Error: There is not enough information to infer relation "authors.books"

Error: There is not enough information to infer relation "authors.books"

Angelelz•14mo ago

This is correct Your problem is an error in your relation definition For a Many to Many, you need another table in the middle: Author <-> AuthorsToBooks <-> Books Your booksRelations is referencing the authors directly:

export const bookRelations = relations(books, ({ many }) => ({
  authors: many(authors),
}));

export const bookRelations = relations(books, ({ many }) => ({
  authors: many(authors),
}));

Same as the authorsRelations:

export const authorRelations = relations(authors, ({ many }) => ({
  books: many(books),
}));

export const authorRelations = relations(authors, ({ many }) => ({
  books: many(books),
}));

You need to make booksRelations reference authorsToBooks and authorsRelations reference AuthorstoBooks

hankandre•14mo ago

Gotcha! I see where I went wrong. The relation on each is to the join table.

Angelelz•14mo ago

Drizzle was not able to infer the relation due to this error

hankandre•14mo ago

Oh my gosh. I knew it was something idiotic and I just overlooked it in the docs. 🤦‍♂️ Thank you so much, @Angelelz ! I'm not the OP, so I can't close this, but I'm golden now. Thanks for all the help everyone! Sorry I didn't look at the highlights in the docs hard enough.

JD•14mo ago

On this topic, I have my two tables created, along with a junction table with the ids. I have successfully used the db.query() syntax so know the tables work, but can't figure out how to get data using the db.select() syntax, and can't seem to find any examples that include how to actually use the response.
I got this far using drizzle docs:

export const selectAllActiveGroups = await db
  .select()
  .from(groupsToAttendeeTypes)
  .leftJoin(groups, eq(groupsToAttendeeTypes.groupId, groups.id))
  .leftJoin(
    attendeeTypes,
    eq(groupsToAttendeeTypes.attendeeTypeId, attendeeTypes.id)
  )
  .where(
    and(
      isNull(groups.deletedAt),
      eq(groups.active, true),
      eq(attendeeTypes.active, true)
    )
  );

export const selectAllActiveGroups = await db
  .select()
  .from(groupsToAttendeeTypes)
  .leftJoin(groups, eq(groupsToAttendeeTypes.groupId, groups.id))
  .leftJoin(
    attendeeTypes,
    eq(groupsToAttendeeTypes.attendeeTypeId, attendeeTypes.id)
  )
  .where(
    and(
      isNull(groups.deletedAt),
      eq(groups.active, true),
      eq(attendeeTypes.active, true)
    )
  );

But because its coming from the junction table, the data is no longer in a useful format, and I am a bit lost. I was hoping to stick to the SQL style as much as possible so I better learn it, so any guidance would be appreciated here!

Angelelz•14mo ago

I suggest you use the RQB, print the query to the console so you can try to replicate it using the crud API

JD•14mo ago

RQB? Sorry I don't think I know what that means

Angelelz•14mo ago

Relational query builders https://orm.drizzle.team/docs/rqb

Drizzle Queries - DrizzleORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind

JD•14mo ago

ahhh. So my attempt to learn how to do it this way is more trouble than worth then? 😅 I was starting to lean this way thanks

Angelelz•14mo ago

No, I mean, you are getting the data you need with that query It's just not formatted in a nice way You could aggregate it with JS/TS if you wanted to

JD•14mo ago

sorry, must have misread. I think I just assumed this was a frequent enough use case, it would already be a "solved problem" without needing to manually restructure all the data. for anyone curious, I was able to achieve closer to what I wanted using the magic sql function and GROUP_CONCAT:

export const queryAllGroups4 = await db
  .select({
    groupId: groups.id,
    groupName: groups.name,
    attendeeTypes: sql<string>`GROUP_CONCAT(${attendeeTypes.name})`,
  })
  .from(groups)
  .leftJoin(groupsToAttendeeTypes, eq(groups.id, groupsToAttendeeTypes.groupId))
  .leftJoin(
    attendeeTypes,
    eq(attendeeTypes.id, groupsToAttendeeTypes.attendeeTypeId)
  )
  .groupBy(groups.id);

export const queryAllGroups4 = await db
  .select({
    groupId: groups.id,
    groupName: groups.name,
    attendeeTypes: sql<string>`GROUP_CONCAT(${attendeeTypes.name})`,
  })
  .from(groups)
  .leftJoin(groupsToAttendeeTypes, eq(groups.id, groupsToAttendeeTypes.groupId))
  .leftJoin(
    attendeeTypes,
    eq(attendeeTypes.id, groupsToAttendeeTypes.attendeeTypeId)
  )
  .groupBy(groups.id);

Gaming

Programming

Many to many - Planetscale