CODEWITHSUNDEEP
javascriptknex.js
Jakub
Jakub

Reputation: 2709

How to tranform a SQL query to a KnexJS function with a joining function

I'm trying to convert the following query in PSQL to KnexJS

SELECT
    sum("current")
FROM (
    SELECT
        "study_id",
        "site_id",
        "status",
        max("day") AS "day"
    FROM
        "candidates"
    WHERE
        "study_id" in('TBX1')
        AND "status" in('INCOMPLETE')
    GROUP BY
        "study_id",
        "site_id",
        "status") AS "latest"
    INNER JOIN "candidates" ON "latest"."day" = "candidates"."day"
        AND "latest"."study_id" = "candidates"."study_id"
        AND ("latest"."site_id" = "candidates"."site_id" or 
            ("latest"."site_id" is null and "candidates"."site_id" is null))
        AND "latest"."status" = "candidates"."status";

I was able to get until this point but I'm missing the part of the query where I compare with NULL and I don't know how to add that to my Knex function

const group = [columns.studyId, columns.siteId, columns.status];
    const filterFn = this.filter;
    const x = this.tx
      .sum(selectColumn)
      .from(function subQuery() {
        this.select(group).max(columns.day, { as: 'day' }).from(tableName).groupBy(group).as('latest');
        filterFn.call({ builder: this }, filter);
      })
      .join(tableName, function joinOn() {
        [columns.day, ...group].map(column =>
          this.on(`latest.${column}`, `${tableName}.${column}`).on(
            this.tx.raw(`("latest"."site_id" = "candidates"."site_id" or
           ("latest"."site_id" is null and "candidates"."site_id" is null))`)
          )
        );
      })

The join method is creating my joins but without the NULL comparison I miss this part from the original query

 AND ("latest"."site_id" = "candidates"."site_id" or 
     ("latest"."site_id" is null and "candidates"."site_id" is null))

Cannot understand how to add that last part to complete my query function

Upvotes: 1

Views: 52

Answers (1)

Wodlo
Wodlo

Reputation: 937

The tricky bit looks to be because you only want the null part of the join for the site_id column but currently it is included inside the map function call so will apply each time through the loop. One way to handle it would be to add an if statement for the special case needed for site_id. i.e.

const group = [columns.studyId, columns.siteId, columns.status];
    const filterFn = this.filter;
    const x = this.tx
      .sum(selectColumn)
      .from(function subQuery() {
        this.select(group).max(columns.day, { as: 'day' }).from(tableName).groupBy(group).as('latest');
        filterFn.call({ builder: this }, filter);
      })
      .join(tableName, function () {
        [columns.day, ...group].map(column => {
            if (column === 'site_id') {
                this.on(function () {
                    this.on(`latest.${column}`, `${tableName}.${column}`)
                        .orOn(function () {
                            this.onNull(`latest.${column}`)
                                .onNull(`${tableName}.${column}`);
                        });
                });
            } else {
                this.on(`latest.${column}`, `${tableName}.${column}`);
            }
        });
    })

I was not able to re-create your case entirely because I don't have access to your filterFn. But without that the sql I get from the above using console.log(x.toString()) ends up being

select sum(`current`) 
from (
    select 
        `study_id`, 
        `site_id`, 
        `status`, 
        max(`day`) as `day` 
    from 
        `candidates` 
    group by `study_id`, 
    `site_id`, 
    `status`) as `latest` 
    inner join `candidates` on `latest`.`day` = `candidates`.`day` 
    and `latest`.`study_id` = `candidates`.`study_id` 
    and (`latest`.`site_id` = `candidates`.`site_id` or 
        (`latest`.`site_id` is null and `candidates`.`site_id` is null)) 
    and `latest`.`status` = `candidates`.`status`

Which looks like what you are after (assuming your filterFn adds in the required where clause)

Upvotes: 1

Related Questions