CODEWITHSUNDEEP
javascriptsqlknex.js
Alex Gusev
Alex Gusev

Reputation: 1864

Knex.js: join 'select' with 'where' clause

I try to create knex query for this SQL query:

SELECT
    *
FROM
    ad AS a
LEFT JOIN ad_file AS af ON
    af.ad_id = a.id
    AND af.file_id = (
    SELECT
        MIN(file_id)
    FROM
        ad_file AS afmin
    WHERE
        afmin .ad_id = a.id )

This is my JS code:

const trx = await knex.transaction();

const query = trx({a: "ad"});
const fileMin = trx({afm: "ad_file"});
fileMin.min("file_id");
fileMin.where("afm.ad_id", knex.raw("`a`.`id`"));

query.leftOuterJoin({af: "ad_file"}, function () {
    this.on("a.id", "af.ad_id");
    this.on("af.file_id", fileMin);
});

but I have wrong SQL query at the end:

select
    *
from
    `ad` as `a`
left outer join `ad_file` as `af` on
    `a`.`id` = `af`.`ad_id`
    and `af`.`file_id` =
select
    min(`file_id`)
from
    `ad_file` as `afm`
where
    `afm`.`ad_id` = `a`.`id`

How can I add brackets to SELECT MIN(file_id) FROM ... WHERE?

Upvotes: 0

Views: 540

Answers (1)

Alex Gusev
Alex Gusev

Reputation: 1864

this is my own solution:

query.leftOuterJoin({af: "ad_file"}, function () {
    this.on("a.id", "af.ad_id");
    this.on("af.file_id", trx.raw("?", [fileMin]));
});

Upvotes: 1

Related Questions