Theo's Typesafe Cult•2mo ago

Drizzle where query problems

I am using drizzle. In my below aPI I cant get my where request to correctly get me my restricted products. When i call the API, it says no restrictions found for product id 63905 which is wrong as I have the below row that DOES have that ID???? db obj example sent in next msg

import {
  integer,
  pgTable,
  serial,
  text,
  timestamp,
  jsonb,
  boolean,
} from "drizzle-orm/pg-core";

export const restrictionGroupTable = pgTable("restriction_group", {
  id: serial("id").primaryKey(),
  name: text("name").notNull().unique(),
  brands: jsonb("brands").notNull().default("[]"),
  products: jsonb("products").notNull().default("[]"),
  categories: jsonb("categories").notNull().default("[]"),
  zipcodes: jsonb("zipcodes").notNull().default("[]"),
  states: jsonb("states").notNull().default("[]"),
  enabled: boolean("enabled").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at")
    .notNull()
    .$onUpdate(() => new Date()),
});

export type InsertRestrictionGroup = typeof restrictionGroupTable.$inferInsert;
export type SelectRestrictionGroup = typeof restrictionGroupTable.$inferSelect;

import {
  integer,
  pgTable,
  serial,
  text,
  timestamp,
  jsonb,
  boolean,
} from "drizzle-orm/pg-core";

export const restrictionGroupTable = pgTable("restriction_group", {
  id: serial("id").primaryKey(),
  name: text("name").notNull().unique(),
  brands: jsonb("brands").notNull().default("[]"),
  products: jsonb("products").notNull().default("[]"),
  categories: jsonb("categories").notNull().default("[]"),
  zipcodes: jsonb("zipcodes").notNull().default("[]"),
  states: jsonb("states").notNull().default("[]"),
  enabled: boolean("enabled").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at")
    .notNull()
    .$onUpdate(() => new Date()),
});

export type InsertRestrictionGroup = typeof restrictionGroupTable.$inferInsert;
export type SelectRestrictionGroup = typeof restrictionGroupTable.$inferSelect;

// api/restrictions/[productId]/[brandId]/[categoryId]/route.ts

import { NextRequest, NextResponse } from "next/server";
import { db } from "@/db";
import { restrictionGroupTable } from "@/db/schema";
import { eq, sql, or, inArray } from "drizzle-orm";

export const revalidate = 3600;

export async function GET(
  request: NextRequest,
  {
    params,
  }: { params: { productId: string; brandId: string; categoryId: string } },
) {
  const { productId, brandId, categoryId } = params;

  try {
    console.log("Fetching restrictions for:", {
      productId,
      brandId,
      categoryId,
    });

    const restrictions = await db
      .select({
        name: restrictionGroupTable.name,
        brands: restrictionGroupTable.brands,
        categories: restrictionGroupTable.categories,
        products: restrictionGroupTable.products,
        zipcodes: restrictionGroupTable.zipcodes,
        states: restrictionGroupTable.states,
      })
      .from(restrictionGroupTable)
      .where(
        or(
          inArray(restrictionGroupTable.products, [sql${productId}::jsonb]),
          inArray(restrictionGroupTable.brands, [sql${brandId}::jsonb]),
          inArray(restrictionGroupTable.categories, [
            sql${categoryId}::jsonb,
          ]),
        ),
      )
      .execute();

    console.log("Restrictions fetched:", restrictions);

    if (restrictions.length === 0) {
      console.log(
        "No restrictions found for this product, brand, or category.",
      );
    }

    return NextResponse.json(restrictions);
  } catch (error) {
    console.error("Error fetching restrictions:", error);
    return NextResponse.json(
      { error: "Failed to fetch restrictions" },
      { status: 500 },
    );
  }
}

// api/restrictions/[productId]/[brandId]/[categoryId]/route.ts

import { NextRequest, NextResponse } from "next/server";
import { db } from "@/db";
import { restrictionGroupTable } from "@/db/schema";
import { eq, sql, or, inArray } from "drizzle-orm";

export const revalidate = 3600;

export async function GET(
  request: NextRequest,
  {
    params,
  }: { params: { productId: string; brandId: string; categoryId: string } },
) {
  const { productId, brandId, categoryId } = params;

  try {
    console.log("Fetching restrictions for:", {
      productId,
      brandId,
      categoryId,
    });

    const restrictions = await db
      .select({
        name: restrictionGroupTable.name,
        brands: restrictionGroupTable.brands,
        categories: restrictionGroupTable.categories,
        products: restrictionGroupTable.products,
        zipcodes: restrictionGroupTable.zipcodes,
        states: restrictionGroupTable.states,
      })
      .from(restrictionGroupTable)
      .where(
        or(
          inArray(restrictionGroupTable.products, [sql${productId}::jsonb]),
          inArray(restrictionGroupTable.brands, [sql${brandId}::jsonb]),
          inArray(restrictionGroupTable.categories, [
            sql${categoryId}::jsonb,
          ]),
        ),
      )
      .execute();

    console.log("Restrictions fetched:", restrictions);

    if (restrictions.length === 0) {
      console.log(
        "No restrictions found for this product, brand, or category.",
      );
    }

    return NextResponse.json(restrictions);
  } catch (error) {
    console.error("Error fetching restrictions:", error);
    return NextResponse.json(
      { error: "Failed to fetch restrictions" },
      { status: 500 },
    );
  }
}

12 Replies

oz•2mo ago

Neto•2mo ago

if you take the drizzle query and run on the database, does ir return the data?

oz•2mo ago

running

SELECT * FROM restriction_group
WHERE products @> '["63905"]'::jsonb;

SELECT * FROM restriction_group
WHERE products @> '["63905"]'::jsonb;

^ ran that query and returns nothing Is my schema simply flawed?

Neto•2mo ago

let me check

oz•2mo ago

I was babied using Prisma 😅

Neto•2mo ago

for products, most likely the "63905" should be 63905

Neto•2mo ago

usually for simple conversions postgres would ignore it but for jsonb operations you need correct types you can even drop the ::jsonb from the inArray calls, postgres does automatically the casting

Neto•2mo ago

oz•2mo ago

WOW your amazing. That was it. I feel very stupid for missing that thanks!

Neto•2mo ago

sql isnt the most fun thing but glad that helped you

oz•2mo ago

Haha true, your a legend really saved me

Gaming

Programming

Drizzle where query problems