knex two mysql query synchronize using promise

Question

Promise.all(sendData.franchisee.map(row => {

    return  knex('designer.settings').select('value').where({setting_key : 'PRICING_TIER'})
            .then(pricing_tier => {

                row.pricing_tier = pricing_tier[0].value;

                knex('designer.pricing_tier').select('tier_title').where({id : row.pricing_tier})
                .then(tier_title =>{

                    row.tier_title = tier_title[0].tier_title;
                    return row;
                })

            });

})).then(response => {
    cb(sendData);     
});

Hear it two query in promise 'designer.settings' and 'designer.pricing_tier'. when execute 'designer.settings' i got that result in row after execute 'designer.pricing_tier' but that output not get in row. row.tier_title = tier_title[0].tier_title not in final sendData. How sync both query in one promise?

Answer 1

Not sure if the query actually does exact same thing, but this merely demonstrates the basic idea how to do the above query correctly with knex.

Effectively the same thing with joining the pricing_tier to prevent need for 2 separate queries.

Promise.all(
  sendData.franchisee.map(row => {
    return knex('pricing_tier') 
      .withSchema('designer') // <-- selecting schema correctly in knex
      // this join could have been done as a subquery in select too...
      .join('settings', 'settings.setting_key', knex.raw('?', ['PRICING_TIER'])) 
      .select('settings.value', 'pricing_tier.tier_title')
      .where('pricing_tier.id', row.pricing_tier)
      .then(rows => {
        row.pricing_tier = rows[0].value;
        row.tier_title = rows[0].tier_title;
        return row;
      });
  })
).then(response => {
  cb(sendData); // <--- generally bad idea to mix promises and callbacks
});

Resulting SQL is like this:

select 
  `settings`.`value`, 
  `pricing_tier`.`tier_title` 
from `designer`.`pricing_tier` 
inner join `designer`.`settings` on `settings`.`setting_key` = 'PRICING_TIER' 
where `pricing_tier`.`id` = 3219