dynamic subquery?

Is it possible to make dynamic subquery? I need to filter subquery by some value and cant figure out how to make it.

    let sq = db
      .select({
        lineId: documentLine.id,
        name: productVariant.fullName,
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
      .limit(limit)
      .offset(offset)
      .where(eq(documentLine.documentId, 1))
      .as("line");

    const dynamicQuery = sq.$dynamic();
    if (documentId) {
      dynamicQuery.where(eq(documentLine.documentId, +documentId));
    }

    let sq = db
      .select({
        lineId: documentLine.id,
        name: productVariant.fullName,
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
      .limit(limit)
      .offset(offset)
      .where(eq(documentLine.documentId, 1))
      .as("line");

    const dynamicQuery = sq.$dynamic();
    if (documentId) {
      dynamicQuery.where(eq(documentLine.documentId, +documentId));
    }

7 Replies

tzezarOP•11mo ago

error message i get:

{
    "errors": [
        "sq.$dynamic is not a function"
    ]
}

{
    "errors": [
        "sq.$dynamic is not a function"
    ]
}

I tried adding alias later, but it also does not work:

    const sq = q.$dynamic();
    sq.as("line");

    const sq = q.$dynamic();
    sq.as("line");

{
    "errors": [
        "UNDEFINED_VALUE: Undefined values are not allowed"
    ]
}

{
    "errors": [
        "UNDEFINED_VALUE: Undefined values are not allowed"
    ]
}

Angelelz•11mo ago

After you use .as() it's an aliased subquery. The $dynamic() method is not available anymore Also, this is not going to do what you think it'll do

tzezarOP•11mo ago

is it possible to somehow add alias with .as() later, from dunamic query? I tried doing it above with no success. why is that? it translates almost 1:1 to

explain ANALYZE
select
    "line"."id",
    "line"."full_name",
    "document_subline"."id",
    "document_subline"."document_line_id",
    "document_subline"."quantity_in_base_unit",
    "document_subline"."quantity_in_package_size",
    "document_subline"."package_size_id",
    "document_subline"."net_price",
    "document_subline"."net_value"
from
    (
    select
        "document_line"."id",
        "product_variant"."full_name"
    from
        "document_line"
    left join "product_variant" on
        "product_variant"."id" = "document_line"."product_variant_id"
    where
        document_line.document_id = $1) "line"
left join "document_subline" on
    "document_subline"."document_line_id" = "line"."id"

explain ANALYZE
select
    "line"."id",
    "line"."full_name",
    "document_subline"."id",
    "document_subline"."document_line_id",
    "document_subline"."quantity_in_base_unit",
    "document_subline"."quantity_in_package_size",
    "document_subline"."package_size_id",
    "document_subline"."net_price",
    "document_subline"."net_value"
from
    (
    select
        "document_line"."id",
        "product_variant"."full_name"
    from
        "document_line"
    left join "product_variant" on
        "product_variant"."id" = "document_line"."product_variant_id"
    where
        document_line.document_id = $1) "line"
left join "document_subline" on
    "document_subline"."document_line_id" = "line"."id"

which is fine, but I need to apply some conditional filtering to subquery

Angelelz•11mo ago

You can't call methods more that once If you call where a second time, it the first filters will get overriden

tzezarOP•11mo ago

oh you are right, i will try to construct filtering array before making subquery

Angelelz•11mo ago

Here is what you should do:

const filters = [eq(documentLine.documentId, 1), documentId ? eq(documentLine.documentId, +documentId) : undefined]

let sq = db
      .select({
        lineId: documentLine.id,
        name: productVariant.fullName,
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
      .limit(limit)
      .offset(offset)
      .where(and(...filters))
      .as("line");

const filters = [eq(documentLine.documentId, 1), documentId ? eq(documentLine.documentId, +documentId) : undefined]

let sq = db
      .select({
        lineId: documentLine.id,
        name: productVariant.fullName,
      })
      .from(documentLine)
      .leftJoin(
        productVariant,
        eq(productVariant.id, documentLine.productVariantId)
      )
      .limit(limit)
      .offset(offset)
      .where(and(...filters))
      .as("line");

Yes

tzezarOP•11mo ago

ok, i tested it and it works, thanks a lot, i was stuck for a while

Gaming

Programming

dynamic subquery?