Newbie question on combining where builders and using it as a query block in any other variable

Question

How can I use .where and .andWhere query builders (which I used in companies below) as a block, I mean something like a combined query builder(if possible) in another variable?

const companies = await db('company')
  .join('address as a1', 'a1.id', 'company.address_id')
  .select('company.name', 'company.membership', 'a1.name as address')
  .where((builder) => {
    if (membership === 'all') {
      builder.whereNotNull('company.membership');
    } else {
      const data = membership === 'member' ? true : false;
      builder.where('company.membership', data);
    }
  })
  .andWhere((builder) => {
    if (location === 'all') {
      builder.whereNotNull('company.id');
    } else {
      builder.whereIn('company.address_id', function () {
        this.select('id').from('address').where('id', address_id);
      });
    }
  });

const count = await db('company')
  .count('company.address_id')
  .join('address as a1', 'a1.id', 'company.address_id')
  .where((builder) => {
    if (membership === 'all') {
      builder.whereNotNull('company.membership');
    } else {
      const data = membership === 'member' ? true : false;
      builder.where('company.membership', data);
    }
  })
  .andWhere((builder) => {
    if (location === 'all') {
      builder.whereNotNull('company.id');
    } else {
      builder.whereIn('company.address_id', function () {
        this.select('id').from('address').where('id', address_id);
      });
    }
  });

I need something like below for count variable, not to repeat the code block.

const count = await db('company')
  .count('company.address_id')
  .join('address as a1', 'a1.id', 'company.address_id')
  .COMBINED QUERY of where and andWhere in companies <-----------

Is there a way for this in Knex?

Answer 1

You have few options:

1. reusing the where block by function.

const whereBuilder = (builder) => {
  if (membership === 'all') {
    builder.whereNotNull('company.membership');
  } else {
    const data = membership === 'member' ? true : false;
    builder.where('company.membership', data);
  }

  if (location === 'all') {
    builder.whereNotNull('company.id');
  } else {
    builder.whereIn('company.address_id', function () {
      this.select('id').from('address').where('id', address_id);
    });
  }
};

const companies = await db('company')
  .join('address as a1', 'a1.id', 'company.address_id')
  .select('company.name', 'company.membership', 'a1.name as address')
  .where(whereBuilder);

const count = await db('company')
  .count('company.address_id')
  .join('address as a1', 'a1.id', 'company.address_id')
  .where(whereBuilder);

2. Reusing the entire query

// --------------V pay attention that there is no await
const baseQuery = db('company')
  .join('address as a1', 'a1.id', 'company.address_id')
  .where((builder) => {
    if (membership === 'all') {
      builder.whereNotNull('company.membership');
    } else {
      const data = membership === 'member' ? true : false;
      builder.where('company.membership', data);
    }
  })
  .andWhere((builder) => {
    if (location === 'all') {
      builder.whereNotNull('company.id');
    } else {
      builder.whereIn('company.address_id', function () {
        this.select('id').from('address').where('id', address_id);
      });
    }
  })

const companies = await baseQuery.clone()
  .select('company.name', 'company.membership', 'a1.name as address');

const count = await baseQuery.clone().count('company.address_id')