Theo's Typesafe Cult•5mo 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

ozOP•5mo ago

Neto•5mo ago

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

ozOP•5mo 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•5mo ago

let me check

ozOP•5mo ago

I was babied using Prisma 😅

Neto•5mo ago

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

Neto•5mo 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•5mo ago

ozOP•5mo ago

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

Neto•5mo ago

sql isnt the most fun thing but glad that helped you

ozOP•5mo ago

Haha true, your a legend really saved me

Gaming

Programming

Drizzle where query problems