CODEWITHSUNDEEP
knex.js
Sammy
Sammy

Reputation: 3697

Knex onIn for Columns

I need to reach the following SQL result using Knex:

select themes.id             as theme_id,
       themes.title          as theme_title,
       subthemes.id          as subtheme_id,
       subthemes.title       as subtheme_title,
       subthemes.parent_id   as subtheme_parent_id
from sections as themes
         left join sections as subthemes on subthemes.parent_id = themes.id
         left join section_questions on section_questions.section_id in (themes.id, subthemes.id)
where subthemes.parent_id is not null

The above is a simple self-join, but where I fail to use Knex is when using the following statement:

.leftJoin<SectionQuestion>('section_questions', (join) =>
  join.onIn('section_questions.section_id', ['themes.id', 'subthemes.id'])
)

The above column names are treated as values, as per the Knex API at least.

What would be the equivalent for columns?

Upvotes: 0

Views: 141

Answers (1)

felixmosh
felixmosh

Reputation: 35553

You can use the knex.raw in this case to make it a column name.

.leftJoin<SectionQuestion>('section_questions', (join) =>
  join.onIn('section_questions.section_id', [
    knex.raw('??', 'themes.id'),
    knex.raw('??', 'subthemes.id'),
  ])
);

Upvotes: 1

Related Questions