Drizzle Team•7mo ago

many-to-many and querying

Hey, I just started using Drizzle and I would appreciate it if someone could chime in and tell me that I'm using it right. I read the documentation and looked at examples, and I'm positive I replicated it correctly. It also works fine. I just would like to confirm, that I can continue to build my application like this. I'm tracking transactions. Every transactions can have many tags, hence the many-to-many relation:

export const transactions = createTable("transaction", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  amount: integer("amount").notNull(),
  date: timestamp("timestamp", { mode: "date" }),
  description: text("description"),
  reference: text("reference"),
});

export const tags = createTable("tags", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  label: text("label").notNull(),
});

export const transactionTags = createTable(
  "transaction_tags",
  {
    transactionId: varchar("transaction_id", { length: 255 })
      .notNull()
      .references(() => transactions.id),
    tagId: varchar("tag_id", { length: 255 })
      .notNull()
      .references(() => tags.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.transactionId, t.tagId] }),
  }),
);

export const transactionRelations = relations(transactions, ({ many }) => ({
  transactionTags: many(transactionTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  transactionTags: many(transactionTags),
}));

export const transactionTagsRelations = relations(
  transactionTags,
  ({ one }) => ({
    transaction: one(transactions, {
      fields: [transactionTags.transactionId],
      references: [transactions.id],
    }),
    tag: one(tags, {
      fields: [transactionTags.tagId],
      references: [tags.id],
    }),
  }),
);

export const transactions = createTable("transaction", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  amount: integer("amount").notNull(),
  date: timestamp("timestamp", { mode: "date" }),
  description: text("description"),
  reference: text("reference"),
});

export const tags = createTable("tags", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  label: text("label").notNull(),
});

export const transactionTags = createTable(
  "transaction_tags",
  {
    transactionId: varchar("transaction_id", { length: 255 })
      .notNull()
      .references(() => transactions.id),
    tagId: varchar("tag_id", { length: 255 })
      .notNull()
      .references(() => tags.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.transactionId, t.tagId] }),
  }),
);

export const transactionRelations = relations(transactions, ({ many }) => ({
  transactionTags: many(transactionTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  transactionTags: many(transactionTags),
}));

export const transactionTagsRelations = relations(
  transactionTags,
  ({ one }) => ({
    transaction: one(transactions, {
      fields: [transactionTags.transactionId],
      references: [transactions.id],
    }),
    tag: one(tags, {
      fields: [transactionTags.tagId],
      references: [tags.id],
    }),
  }),
);

3 Replies

floppydiskOP•7mo ago

I'm getting my data like this:

    const transactions = await ctx.db.query.transactions.findMany({
      with: {
        transactionTags: {
          with: {
            tag: true,
          },
        },
      },
    });

    const transactions = await ctx.db.query.transactions.findMany({
      with: {
        transactionTags: {
          with: {
            tag: true,
          },
        },
      },
    });

This results in:

  "transactions": [
    {
      "id": "1",
      "amount": -100,
      "date": null,
      "description": null,
      "reference": null,
      "transactionTags": [
        {
          "transactionId": "1",
          "tagId": "1",
          "tag": {
            "id": "1",
            "label": "WOW"
          }
        }
      ]
    }
  ]

  "transactions": [
    {
      "id": "1",
      "amount": -100,
      "date": null,
      "description": null,
      "reference": null,
      "transactionTags": [
        {
          "transactionId": "1",
          "tagId": "1",
          "tag": {
            "id": "1",
            "label": "WOW"
          }
        }
      ]
    }
  ]

I now intend to reformat my transactionTags array using JS. Is that okay-ish? 😄

Sillvva•7mo ago

Yes. Though, keep in mind that the relational query api v2 will address this

Sillvva•7mo ago

https://github.com/drizzle-team/drizzle-orm/discussions/2316

GitHub

Relational API v2 · drizzle-team drizzle-orm · Discussion #2316

We've launched Drizzle Relational Queries exactly a year ago and it's time to ship a fundamental upgrade. We've gathered a massive amount of valuable feedback from the community and goi...

Gaming

Programming

many-to-many and querying