Dr Use Posts - Answer Overflow

Dr Use

Explore posts from servers

SSolidJS TSDThe Swift Den DTDrizzle Team

•Created by Dr Use on 4/3/2025 in #help

Complex filter on query.

Hello everyone, here's the query :

db.query.ordersTable.findMany({
      with: {
        products: true,
        user: {
          columns: {
            password: false,
          },
        },
      },
      where: and(
        eq(ordersTable.draft, false),
        or(
          query === "" ? sql`TRUE` : undefined,
          eq(ordersTable.id, Number(query) || 0),
          ilike(sql`productsOrderTable.name`, `%${query}%`),
        ),
      ),
  )

db.query.ordersTable.findMany({
      with: {
        products: true,
        user: {
          columns: {
            password: false,
          },
        },
      },
      where: and(
        eq(ordersTable.draft, false),
        or(
          query === "" ? sql`TRUE` : undefined,
          eq(ordersTable.id, Number(query) || 0),
          ilike(sql`productsOrderTable.name`, `%${query}%`),
        ),
      ),
  )

I get the error : missing FROM-clause entry for table "productsordertable" I can't find a way to filter the orders according to the user or the products. For exemple I want the orders where the user name is "john". Or I want the orders where some of the products name contains "foo". PS : productsOrderTable has a column order_id as a foreign key I can only filter according to the ordersTable direct columns

5 replies

DTDrizzle Team

•Created by Dr Use on 1/10/2025 in #help

define where query outside

I dont understand why this code works :

const whereQuery = exists(
    db
      .select({ id: sql`1` })
      .from(productsOrderTable)
      .where(
        and(
          eq(productsOrderTable.orderId, ordersTable.id),
          ilike(productsOrderTable.name, "%" + query + "%"),
        ),
      ),
  )

  const [orders, ordersLength] = await Promise.all([
    db.query.ordersTable.findMany({
      limit: ordersByPage,
      offset: (page - 1) * ordersByPage,
      with: {
        products: true,
        user: {
          columns: {
            password: false,
          },
        },
      },
      where: (ordersTable) => exists(
        db
          .select({ id: sql`1` })
          .from(productsOrderTable)
          .where(
            and(
              eq(productsOrderTable.orderId, ordersTable.id),
              ilike(productsOrderTable.name, "%" + query + "%"),
            ),
          ),
      ), ### only changed this part of the code
      orderBy: (ordersTable, { desc }) => desc(ordersTable.date),
    }),
    db
      .select({ count: count() })
      .from(ordersTable)
      .where(whereQuery),
  ]);

const whereQuery = exists(
    db
      .select({ id: sql`1` })
      .from(productsOrderTable)
      .where(
        and(
          eq(productsOrderTable.orderId, ordersTable.id),
          ilike(productsOrderTable.name, "%" + query + "%"),
        ),
      ),
  )

  const [orders, ordersLength] = await Promise.all([
    db.query.ordersTable.findMany({
      limit: ordersByPage,
      offset: (page - 1) * ordersByPage,
      with: {
        products: true,
        user: {
          columns: {
            password: false,
          },
        },
      },
      where: (ordersTable) => exists(
        db
          .select({ id: sql`1` })
          .from(productsOrderTable)
          .where(
            and(
              eq(productsOrderTable.orderId, ordersTable.id),
              ilike(productsOrderTable.name, "%" + query + "%"),
            ),
          ),
      ), ### only changed this part of the code
      orderBy: (ordersTable, { desc }) => desc(ordersTable.date),
    }),
    db
      .select({ count: count() })
      .from(ordersTable)
      .where(whereQuery),
  ]);

But not this one :

const whereQuery = exists(
    db
      .select({ id: sql`1` })
      .from(productsOrderTable)
      .where(
        and(
          eq(productsOrderTable.orderId, ordersTable.id),
          ilike(productsOrderTable.name, "%" + query + "%"),
        ),
      ),
  )

  const [orders, ordersLength] = await Promise.all([
    db.query.ordersTable.findMany({
      limit: ordersByPage,
      offset: (page - 1) * ordersByPage,
      with: {
        products: true,
        user: {
          columns: {
            password: false,
          },
        },
      },
      where: whereQuery, ### only changed this part of the code
      orderBy: (ordersTable, { desc }) => desc(ordersTable.date),
    }),
    db
      .select({ count: count() })
      .from(ordersTable)
      .where(whereQuery),
  ]);

const whereQuery = exists(
    db
      .select({ id: sql`1` })
      .from(productsOrderTable)
      .where(
        and(
          eq(productsOrderTable.orderId, ordersTable.id),
          ilike(productsOrderTable.name, "%" + query + "%"),
        ),
      ),
  )

  const [orders, ordersLength] = await Promise.all([
    db.query.ordersTable.findMany({
      limit: ordersByPage,
      offset: (page - 1) * ordersByPage,
      with: {
        products: true,
        user: {
          columns: {
            password: false,
          },
        },
      },
      where: whereQuery, ### only changed this part of the code
      orderBy: (ordersTable, { desc }) => desc(ordersTable.date),
    }),
    db
      .select({ count: count() })
      .from(ordersTable)
      .where(whereQuery),
  ]);

2 replies

Gaming

Programming