How to remove left join in function Knex.JS?

April 26, 2022

How to remove left join integration?

await knex.select().from("dishes")
            .leftJoin("companies", function () {
                if (companyId) {
                    this.on("dishes.companyId", "=", "companies.id")
                } else {
                    // I want to remove left join if companyId is false
                    return false;
                }
            })

I’m got an error:

code: 'ER_PARSE_ERROR',
  sql: 'select * from `dishes` left join `companies`'
}

>Solution :

It’s unclear which part you’re having difficulty with. Your current error is caused by the fact that you are trying to create a left join without giving it a condition to join on.

I suggest you move your if (companyId) check outside the leftJoin() call so that two separate queries are created.

async function getDishes(companyId) {
   if (companyId) {
      return knex.select().from("dishes").
          leftJoin("companies", "dishes.companyId", "companies.id");
   }
   return knex.select().from("dishes");
}

Which could potentially be simplified to

async function getDishes(companyId) {
   const dishes = knex.select().from("dishes");
   return companyId 
       ? dishes.leftJoin("companies", "dishes.companyId", "companies.id");
       : dishes;
 }