Where Clause In Joins

#1

Hello everyone,

I want to create an SQL statement with Query Builder like this;

SELECT *
FROM posts
LEFT JOIN post_titles
    ON post_titles.post_id = posts.id 
    AND post_titles.lang_code = 'EN'

I can write the join part like this;

let posts = Posts
    .query()
    .leftJoin('post_titles', function () {
        this
          .on('post_titles.post_id', 'posts.id')
          .andOn('post_titles.lang_code', 'EN')
    })

But the problem is Query Builder thinks that EN is a column of main table (posts).

I have checked knex.js library in order to understand what’s going on. We can pass a parameter to where clause like this;

knex.select('*')
    .from('users')
    .join('accounts', 'accounts.type', knex.raw('?', ['admin']))

But I couldn’t find the raw() method in leftJoin function. How can I implement my query?

0 Likes

#2

Have you defined a relationship between the Post model and PostTitle? And use a query such as…

Post.query()
    .with("PostTitle", ( builder ) => {
        builder
          .where('lang_code', 'EN')
    })
    .first();
1 Like