DT
Drizzle Teammiigaarino

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
Ahmed
Ahmedā€¢281d ago
Do you still need help ?
hankandre
hankandreā€¢211d 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
Angelelzā€¢211d 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
l2ivalā€¢211d ago
@Angelelz With this using relations Can you add additional columns when using relations()?
Angelelz
Angelelzā€¢211d ago
I don't understand the question You can have as many relations as you want
l2ival
l2ivalā€¢211d 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
Angelelzā€¢211d ago
I would put the order column in the junction table Because the order will be unique per activity <-> workout
hankandre
hankandreā€¢211d 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
Angelelzā€¢211d 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
hankandreā€¢211d ago
Gotcha! I see where I went wrong. The relation on each is to the join table.
Angelelz
Angelelzā€¢211d ago
Drizzle was not able to infer the relation due to this error
hankandre
hankandreā€¢211d 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
JDā€¢199d 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
Angelelzā€¢199d 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
JDā€¢199d ago
RQB? Sorry I don't think I know what that means
Angelelz
Angelelzā€¢199d 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
JDā€¢199d 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
Angelelzā€¢199d 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
JDā€¢199d 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);
Want results from more Discord servers?
Add your server
More Posts
Inserting with RelationHi, What is the way of inserting a new record with a relationship in Drizzle. I understand that Prisql escaping on whereTrying to do the following: ``` where: sql`unaccent(${users.fullName}) ILIKE unaccent('%${input.queBroken typescript in selectWhatever I put inside the select function. it will generate this typescript error: I am using the laWhy is introspecting creating tables that didn't previously existMore context here: https://github.com/drizzle-team/drizzle-orm/issues/847Passing SQL functions to insertIs there any way to pass a SQL function like `gen_random_uuid()` to the `insert` function, for a speDrizzle-kit database push connection errorWhen trying to run `drizzle-kit push:mysql` to push my schema to Planetscale, I get the following erTypescript error when importing the planetscale-drizzle db instance.my db is hosted on planetscale and im trying to do the setup as explained in the drizzle docs but whMapped column name and relation with the original nameHi, I am taking over an old project and have found an issue, I'm not sure if I should file it as a bHow to properly do foreign keys in schema with PlanetScale?I'm aware that foreign key *constrains* aren't supported, but foreign keys themselves are. push: ``Typescript path alias not working properlyI'm new to Drizzle, but I'm not able to use the path alias on schema files. Using absolute paths onExpand query from ID using .select()Hi! I am trying to get the first name of a comment author, but only store the ID so the user can chaPGEnum -> Typescript EnumHey there. Is there any convenient way people have found to conveniently turn a pgEnum into a typescIs Drizzle Kit/Drizzle Studio possible with RDS Data API?I'm able to create the RDSDataClient for querying my DB using Drizzle ORM, but I don't see a way to How to apply migrations on postgres in a serverless enviroment?Hello, I want to apply postgres migrations to my vercel DB. I see that the push command does not woError when trying to generate a migration schemaanyone any ideas to what the problem is? It was working fine yesterday and now it throws an error whModelling self relationsI have a table `categories` with a parent fields: ``` { id: text("id").notNull().primaryKey(), .Issue running migrations to DBI am having an issue running migrations to Neon. I checked that everything is exporting const variabIt is possible to have prepared statements inside transactions?Is there a way to insert prepared queries inside a transaction ?type config findMany or findFirstHi, I've one question i don't find the way to type correctly findMany() Like: export async functionAny idea on how to pass a pool from postgres-pool to drizzle?Passing a single client works, but for better connection management it would help to be able to pass