Combine drizzle where and with statement to return elements based on a specific condition

[riculum]

I'm trying to construct a query where I need to filter products based on a specific condition related to the collection name. Here's a simplified version of my code:

async function getProductsByCollection(collection: string) {
  return db.query.products.findMany({
    with: {
      collection: true,
      category: true,
      articles: {
        with: {
          size: true,
          color: true,
        },
      },
    },
    where: and(
      // Here's where I want to add a condition based on the collection name
      // Something like: eq(products.collection.name, "something")
    ),
    limit: 10,
  })
}

[Angelelz]

Filtering for by nested conditions is not supported in the RQB. But you can workaround it using a subquery:

async function getProductsByCollection(collection: string) {
  return db.query.products.findMany({
    with: {
      collection: true,
      category: true,
      articles: {
        with: {
          size: true,
          color: true,
        },
      },
    },
    where: and(
      // Here's where I want to add a condition based on the collection name
      // Something like: eq(products.collection.name, "something")
      inArray(products.id, db.select({ productId: collections.productId }).from(collections).where(eq(collection.name, "something")))
    ),
    limit: 10,
  })
}

I'm assuming a lot of your database structure because I don't know it, but hopefully you get the idea.