Dynamic Where query not working

[csulit]

The conditional inside template is literally not working as expected.

const dealPipeline = await sql.query`
      SELECT
        dl.DealId AS deal_id, 
        amount, 
        amount_in_home_currency, 
        closed_lost_reason, 
        closed_won_reason, 
        dl.closedate AS closed_date, 
        dl.days_to_close, 
        deal_currency_code,
        dealname AS deal_name,
        c.CompanyName AS company_name,
        dps.StageLabel AS deal_stage_label,
        dealtype AS deal_type,
        service,
        service_term,
        dl.contarct_lenght as contract_length,
        commencement_date,
        dl.hs_all_owner_ids,
        o.hubspot_owner_id,
        o.email AS owner_email,
        hs_is_closed,
        hs_is_closed_won,
        dl.hs_lastmodifieddate AS hs_last_modified_date,
        dl.pipeline AS pipeline_id,
        dp.PipelineLabel AS deal_pipeline_label,
        dl.createdate AS created_date 
      FROM Deal dl
      INNER JOIN DealPipelineStage dps ON dps.DealStage = dl.dealstage
      INNER JOIN DealPipeline dp ON dp.PipelineId = dl.pipeline
      INNER JOIN DealCompanyAssociations dcs ON dcs.DealId = dl.DealId
      INNER JOIN Company c ON c.companyId = dcs.companyId
      INNER JOIN Owner o ON o.hubspot_owner_id = dl.hubspot_owner_id
      WHERE (YEAR(dl.createdate) = ${currentYear} OR YEAR(dl.createdate) = ${previousYear})
      ${zodValidate.data?.pipeline_id ? `AND dl.pipeline = '18289468'` : ``}
      ORDER BY dl.createdate DESC
      OFFSET ${offset} ROWS
      FETCH NEXT ${pageSize} ROWS ONLY
    `;

but when removing the ${zodValidate.data?.pipeline_id ? `AND dl.pipeline = '18289468'` : ``} works no issue

error: Invalid usage of the option NEXT in the FETCH statement.

[dhensby]

that's because you're passing a parameter as AND dl.pipeline = '18289468', so this looks to be working exactly as expected.

Anything passed to the template literal as a variable (ie: inside a ${}) will be parameterised by the query function.

[csulit]

that's because you're passing a parameter as AND dl.pipeline = '18289468', so this looks to be working exactly as expected.

Anything passed to the template literal as a variable (ie: inside a ${}) will be parameterised by the query function.

what is the best approach for this kind of use case?

let pipelineCondition = "";

    if (zodValidate.data?.pipeline_id) {
      pipelineCondition = `AND dl.pipeline = '${zodValidate.data.pipeline_id}'`;
    }

    const dealPipeline = await sql.query`
      SELECT
        dl.DealId AS deal_id, 
        amount, 
        amount_in_home_currency, 
        closed_lost_reason, 
        closed_won_reason, 
        dl.closedate AS closed_date, 
        dl.days_to_close, 
        deal_currency_code,
        dealname AS deal_name,
        c.CompanyName AS company_name,
        dps.StageLabel AS deal_stage_label,
        dealtype AS deal_type,
        service,
        service_term,
        dl.contarct_lenght as contract_length,
        commencement_date,
        dl.hs_all_owner_ids,
        o.hubspot_owner_id,
        o.email AS owner_email,
        hs_is_closed,
        hs_is_closed_won,
        dl.hs_lastmodifieddate AS hs_last_modified_date,
        dl.pipeline AS pipeline_id,
        dp.PipelineLabel AS deal_pipeline_label,
        dl.createdate AS created_date 
      FROM Deal dl
      INNER JOIN DealPipelineStage dps ON dps.DealStage = dl.dealstage
      INNER JOIN DealPipeline dp ON dp.PipelineId = dl.pipeline
      INNER JOIN DealCompanyAssociations dcs ON dcs.DealId = dl.DealId
      INNER JOIN Company c ON c.companyId = dcs.companyId
      INNER JOIN Owner o ON o.hubspot_owner_id = dl.hubspot_owner_id
      WHERE (YEAR(dl.createdate) = ${currentYear} OR YEAR(dl.createdate) = ${previousYear})
      ${pipelineCondition}
      ORDER BY dl.createdate DESC
      OFFSET ${offset} ROWS
      FETCH NEXT ${pageSize} ROWS ONLY;
    `;

getting this error

{
    "success": false,
    "statusCode": 500,
    "data": null,
    "message": "Invalid usage of the option NEXT in the FETCH statement."
}

[dhensby]

Well, this isn't designed to be a query builder, which is how you're using it. The best way to do this within the library would be to define the parameters manually. See docs