Question:
I’m using Drizzle ORM version 0.30.10, and I’m working with a many-to-many relationship in my database schema. Currently, I receive the related records as objects, but I want to receive them in an array format instead. Below is my schema and query setup:
Schema:

export const actionTable = mysqlTable("action", {
 id: int("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 255 }).notNull(),
  description: varchar("description", { length: 255 }),
  userId: varchar("user_id", { length: 255 })
    .notNull()
    .references(() => userTable.id, { onDelete: "cascade" }),
  modifiedAt: datetime("modified_at").default(new Date()),
  createdAt: datetime("created_at").default(new Date()),
});

export const actionCategoryTable = mysqlTable("action_category", {
  id: int("id").primaryKey().autoincrement(),
  name: varchar("name", { length: 255 }).notNull(),
  description: varchar("description", { length: 255 }),
});

export const actionJoinActionCategoryTable = mysqlTable(
  "action_j_category",
  {
    actionId: int("action_id")
      .notNull()
      .references(() => actionTable.id, { onDelete: "cascade" }),
    actionCategoryId: int("category_id")
      .notNull()
      .references(() => actionCategoryTable.id),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.actionId, table.actionCategoryId] }),
    };
  }
);

Query:

async actionsByUser(userId: string) {
  const actions = await this._db.query.actionTable.findMany({
    with: {
      user: true,
      categories: {
        with: {
          category: true,
        },
      },
    },
    where: (actions, { eq }) => eq(actions.userId, userId),
    orderBy: (actions, { desc }) => [desc(actions.createdAt)],
  });

  // actions.forEach((action) => {
  //   action.categories = action.categories.map((category) => ({
  //     ...category.category,
  //   }));
  // });
  return actions;
}

Current Output:

[
  {
    "id": 1,
    "name": "Action 1",
    "description": "Description 1",
    "userId": "user_1",
    "categories": [
      {
        "actionId": 1,
        "actionCategoryId": 2,
        "category": {
          "id": 2,
          "name": "Category 2",
          "description": "Description 2"
        }
      }
    ]
  }
]

Desired Output:

[
  {
    "id": 1,
    "name": "Action 1",
    "description": "Description 1",
    "userId": "user_1",
    "categories": [
      {
        "id": 2,
        "name": "Category 2",
        "description": "Description 2"
      }
    ]
  }
]