DT
Drizzle Team•9mo ago
Trader Launchpad

How to structure posts with categories using drizzle and postgres (supabase)

I have been working on how to set up businesses, categories, and businessToCategories (join) tables and properly querying so that I can pass an array of category ids and only get back the related posts. I am trying to use the query api so that my return types are consistent. I should be able to ask for all posts with a category id of [1,2] and it return just those posts. Currently I am able to retrieve all businesses with their businessToCategories id and then finally the nested category name and icon. How can i filter via the initial where clause referencing a relation? I have the three tables defined in schema and pushed live: 
export const businessEntitys = createTable("businessEntity", {
  id: serial("id").primaryKey(),
  uuid: uuid("uuid").notNull(),
  name: varchar("name", { length: 256 }),
  domain: varchar("domain", { length: 256 }),
  profileImage: varchar("profileImage", { length: 256 }),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  createdById: varchar("createdById", { length: 255 }),
  updatedAt: timestamp("updatedAt"),
});

export const businessEntityRelations = relations(
  businessEntitys,
  ({ many }) => ({
    reviews: many(reviews),
    businessEntityCategories: many(businessEntityCategories),
  }),
);
export const businessEntitys = createTable("businessEntity", {
  id: serial("id").primaryKey(),
  uuid: uuid("uuid").notNull(),
  name: varchar("name", { length: 256 }),
  domain: varchar("domain", { length: 256 }),
  profileImage: varchar("profileImage", { length: 256 }),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  createdById: varchar("createdById", { length: 255 }),
  updatedAt: timestamp("updatedAt"),
});

export const businessEntityRelations = relations(
  businessEntitys,
  ({ many }) => ({
    reviews: many(reviews),
    businessEntityCategories: many(businessEntityCategories),
  }),
);
export const categories = createTable("categories", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 256 }),
  desc: varchar("desc", { length: 256 }),
  icon: varchar("icon", { length: 256 }),
  slug: varchar("slug", { length: 256 }),
  createdById: varchar("createdById", { length: 255 }),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  updatedAt: timestamp("updatedAt"),
});

export const categoriesRelations = relations(categories, ({ many }) => ({
  businessEntityCategories: many(businessEntityCategories),
}));
export const categories = createTable("categories", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 256 }),
  desc: varchar("desc", { length: 256 }),
  icon: varchar("icon", { length: 256 }),
  slug: varchar("slug", { length: 256 }),
  createdById: varchar("createdById", { length: 255 }),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
  updatedAt: timestamp("updatedAt"),
});

export const categoriesRelations = relations(categories, ({ many }) => ({
  businessEntityCategories: many(businessEntityCategories),
}));
5 Replies
Trader Launchpad
Trader LaunchpadOP•9mo ago
export const businessEntityCategories = createTable(
  "businessEntityCategories",
  {
    businessEntityId: integer("businessEntityId").references(
      () => businessEntitys.id,
      { onDelete: "cascade" },
    ),
    categoryId: integer("categoryId").references(() => categories.id, {
      onDelete: "cascade",
    }),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.businessEntityId, table.categoryId] }),
    };
  },
);

export const businessEntityCategoriesRelations = relations(
  businessEntityCategories,
  ({ one }) => ({
    business: one(businessEntitys, {
      fields: [businessEntityCategories.businessEntityId],
      references: [businessEntitys.id],
    }),
    categories: one(categories, {
      fields: [businessEntityCategories.categoryId],
      references: [categories.id],
    }),
  }),
);
export const businessEntityCategories = createTable(
  "businessEntityCategories",
  {
    businessEntityId: integer("businessEntityId").references(
      () => businessEntitys.id,
      { onDelete: "cascade" },
    ),
    categoryId: integer("categoryId").references(() => categories.id, {
      onDelete: "cascade",
    }),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.businessEntityId, table.categoryId] }),
    };
  },
);

export const businessEntityCategoriesRelations = relations(
  businessEntityCategories,
  ({ one }) => ({
    business: one(businessEntitys, {
      fields: [businessEntityCategories.businessEntityId],
      references: [businessEntitys.id],
    }),
    categories: one(categories, {
      fields: [businessEntityCategories.categoryId],
      references: [categories.id],
    }),
  }),
);
My Trpc query: 
getAll: publicProcedure
    .input(
      z.object({
        limit: z.number().min(1).max(100).default(20).optional(),
        cursor: z.number().nullish(), // <-- "cursor" needs to exist, but can be any type
        direction: z
          .enum(["forward", "backward"])
          .default("forward")
          .optional(), // optional, useful for bi-directional query
        categoryId: z.number().optional(),
        categories: z.array(z.number()).optional(),
      }),
    )
    .query(async ({ ctx, input }) => {
      const limit = input.limit ?? 50;
      const items = await ctx.db.query.businessEntitys.findMany({
        
        where: input.cursor ? gt(businessEntitys.id, input.cursor) : undefined,
        with: {
          reviews: true,
          businessEntityCategories: {
            with: {
              categories: true,
            },
          },
        },
        limit: limit,
        orderBy: asc(businessEntitys.id),
      });

      let nextCursor: typeof input.cursor | undefined = undefined;
      if (items.length > limit) {
        const nextItem = items.pop();
        nextCursor = nextItem!.id;
      }
      return {
        items,
        nextCursor,
      };
    }),
getAll: publicProcedure
    .input(
      z.object({
        limit: z.number().min(1).max(100).default(20).optional(),
        cursor: z.number().nullish(), // <-- "cursor" needs to exist, but can be any type
        direction: z
          .enum(["forward", "backward"])
          .default("forward")
          .optional(), // optional, useful for bi-directional query
        categoryId: z.number().optional(),
        categories: z.array(z.number()).optional(),
      }),
    )
    .query(async ({ ctx, input }) => {
      const limit = input.limit ?? 50;
      const items = await ctx.db.query.businessEntitys.findMany({
        
        where: input.cursor ? gt(businessEntitys.id, input.cursor) : undefined,
        with: {
          reviews: true,
          businessEntityCategories: {
            with: {
              categories: true,
            },
          },
        },
        limit: limit,
        orderBy: asc(businessEntitys.id),
      });

      let nextCursor: typeof input.cursor | undefined = undefined;
      if (items.length > limit) {
        const nextItem = items.pop();
        nextCursor = nextItem!.id;
      }
      return {
        items,
        nextCursor,
      };
    }),
Sillvva
Sillvva•9mo ago
Would this work? 
db.query.businessEntitys.findMany({
  with: {
    businessEntityCategories: {
      with: {
        categories: true,
      },
    },
  },
  where: and(
    gt(businessEntitys.id, input.cursor || 0),
    exists(
      db
        .select()
        .from(businessEntityCategories)
        .where(
          and(
            eq(
              businessEntityCategories.businessEntityId,
              businessEntitys.id,
            ),
            inArray(businessEntityCategories.categoryId, [1, 2]),
          ),
        ),
    ),
  ),
  limit: limit,
  orderBy: asc(businessEntitys.id),
});
db.query.businessEntitys.findMany({
  with: {
    businessEntityCategories: {
      with: {
        categories: true,
      },
    },
  },
  where: and(
    gt(businessEntitys.id, input.cursor || 0),
    exists(
      db
        .select()
        .from(businessEntityCategories)
        .where(
          and(
            eq(
              businessEntityCategories.businessEntityId,
              businessEntitys.id,
            ),
            inArray(businessEntityCategories.categoryId, [1, 2]),
          ),
        ),
    ),
  ),
  limit: limit,
  orderBy: asc(businessEntitys.id),
});
Trader Launchpad
Trader LaunchpadOP•9mo ago
I will test and let you know asap @Sillvva I get error: TRPCClientError: invalid reference to FROM-clause entry for table "create-t3-app_businessEntity
Sillvva
Sillvva•9mo ago
Hmm. I'm not familiar with that error. Try changing the where clause to a function. Then reference the businessEntities table from the function params. 
+ where: (be, { and, gt, exists, eq, inArray }) => and(
    gt(businessEntitys.id, input.cursor || 0),
    exists(
      db
        .select()
        .from(businessEntityCategories)
        .where(
          and(
            eq(
              businessEntityCategories.businessEntityId,
+             be.id,
            ),
            inArray(businessEntityCategories.categoryId, [1, 2]),
          ),
        ),
    ),
  ),
+ where: (be, { and, gt, exists, eq, inArray }) => and(
    gt(businessEntitys.id, input.cursor || 0),
    exists(
      db
        .select()
        .from(businessEntityCategories)
        .where(
          and(
            eq(
              businessEntityCategories.businessEntityId,
+             be.id,
            ),
            inArray(businessEntityCategories.categoryId, [1, 2]),
          ),
        ),
    ),
  ),
Not sure if that will change anything in the resulting query. But I guess it's worth a shot. This is how I did it in my app.
Trader Launchpad
Trader LaunchpadOP•9mo ago
Ah that works most of the way. I think i can figure it out from here! THANK YOU

Did you find this page helpful?