subquery inside inIfValue

[mkupriichuk]

Hello We can use subquery inside in:

const subquery = connection
            .selectFrom(saProductCategoryTable)
            .where(saProductCategoryTable.saCategoryId.equalsIfValue(categoryId))
            .selectOneColumn(saProductCategoryTable.saProductId)

connection
      .selectFrom(saProductTable)
      .innerJoin(basketAnalysisQuery)
      .on(basketAnalysisQuery.productId.equals(saProductTable.id))
      .where(basketAnalysisQuery.value.greaterOrEquals(1))
      .and(basketAnalysisQuery.productId.in(subquery)
      );

But what if we need to use .and(basketAnalysisQuery.productId.in... only if categoryId !== undefined:

It is possible to do this with inIfValue?

connection
      .selectFrom(saProductTable)
      .innerJoin(basketAnalysisQuery)
      .on(basketAnalysisQuery.productId.equals(saProductTable.id))
      .where(basketAnalysisQuery.value.greaterOrEquals(1))
      .and(basketAnalysisQuery.productId.inIfValue(categoryId ? subquery : null)
      );

Because now i get error like this:

The type "GroupByOrderByExecutableSelectExpression<DB<"Connection">, SaProductCategoryTable, NumberValueSource<TABLE<DB<DB<"Connection">, "SaProductCategoryTable">, "required"> & PrimaryKeyColumn, number, NoTableOrViewRequiredView<...>, never>" is missing the following properties from the "number[]" type: length, pop, push, concat and 29 more.

Or maybe it has to be done in some other way?

Thanks

[juanluispaz]

Hi,

What you need to use is Ignorable boolean expression; in that way, your query will look like:

connection
      .selectFrom(saProductTable)
      .innerJoin(basketAnalysisQuery)
      .on(basketAnalysisQuery.productId.equals(saProductTable.id))
      .where(basketAnalysisQuery.value.greaterOrEquals(1))
      .and(basketAnalysisQuery.productId.in(subquery).onlyWhen(categoryId !== undefined))
      );

Let me know if that works for you.

[mkupriichuk]

Yes. works nice, thanks