DT
Drizzle Team•4w ago
Dr Use

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
2 Replies
Dr Use
Dr UseOP•2w ago
Help please
TOSL
TOSL•2w ago
can you make a drizzle run example and share it? likely the solution here is to not use the relational api and use the sql-like api relational api is limited in many ways

Did you find this page helpful?