Searching multiple tables at the same time in a database

Omar Martin
Omar Martin used Ask the Experts™
on
I am trying to search the database column called "equipment" for multiple table models using sequelize. However, I am not receiving any data even though the fields are full. I am attempting to use the "Promise".  

However, if I simply call the model ( //boilerHouse.findAll({ where: { equipment_type: { [Op.in]: [req.params.name] } } }).then((data) => { ), I can search one table but not all.

router.get("/equipmentInfo/:name", (req, res) =>

 Promise.all([boilerHouse.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), compressorHouse.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), engineHouse33.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), fireHouse.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), hfoSeparator.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), reverseOsmosis.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }), tankFarm33.findAll({ where: { equipment_area: { [Op.in]: [req.params.name] } } }) ])
.then((data) => {

  //boilerHouse.findAll({ where: { equipment_type: { [Op.in]: [req.params.name] } } }).then((data) => {
   
    res.render('gigs', {
      gigs:data,
         
     //data[0] is response from tableA find
     // data[1] is from tableB
      })   
    }).catch(err => console.log(err)));
    

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What do you get when you console.log(data) in the then()?
Omar MartinBridgemaker

Author

Commented:
I changed the code a bit to this:

router.get("/search", (req, res) => {
const { term } = req.query;

const boilerhousefind = new Promise((resolve,reject) => {
        resolve([boiler_houses.findAll({ where: { equipment_area: { [Op.like]: '%' + term + '%'  }}})])
  })
const compressorhousefind = new Promise((resolve,reject) => {
        resolve([compressor_houses.findAll({ where: { equipment_area: { [Op.like]: '%' + term + '%'}}})])
  })
Promise.all([
  boilerhousefind,
  compressorhousefind
]).then((data) => {

  console.log(data);
  res.render('gigs', {
    gigs: [...data[1]]
   })   
  })
  .catch(err => console.log(err));
})

Open in new window


Here is the output if I console.log it:

[ [ Promise [Object] {
      _bitField: 0,
      _fulfillmentHandler0: undefined,
      _rejectionHandler0: undefined,
      _promise0: undefined,
      _receiver0: undefined } ],
  [ Promise [Object] {
      _bitField: 0,
      _fulfillmentHandler0: undefined,
      _rejectionHandler0: undefined,
      _promise0: undefined,
      _receiver0: undefined } ] ]
Executing (default): SELECT `id`, `equipment_area`, `equipment_type`, `photo_image`, `manufacturer`, `model_number`, `serial_number`, `service`, `capacity`, `severity`, `general_comments`, `createdAt`, `updatedAt` FROM `boiler_houses` AS `boiler_houses` WHERE `boiler_houses`.`equipment_area` LIKE '%Auxilliary Boiler House%';
Executing (default): SELECT `id`, `equipment_area`, `equipment_type`, `photo_image`, `manufacturer`, `model_number`, `serial_number`, `service`, `capacity`, `severity`, `general_comments`, `createdAt`, `updatedAt` FROM `compressor_houses` AS `compressor_houses` WHERE `compressor_houses`.`equipment_area` LIKE '%Auxilliary Boiler House%';
Omar MartinBridgemaker

Author

Commented:
This is the only search route I can get to work.....searching one table at a time but I am not able to search more than one table at a time:


router.get("/search", (req, res) => {
const { term } = req.query;

boiler_houses.findAll({ where: { equipment_type: { [Op.like]: '%' + term + '%'  } } }).then(gigs=>
res.render('gigs', {
 gigs})).catch(err => console.log(err));
   });

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
That is not correct - you are immediately resolving your promises to be an array containing the promise returned from the findAll which suggests you are not 100% on top of Promises and how they resolve.

Just do this (don't wrap your findAll promises in another promise)

router.get("/search", (req, res) => {
const { term } = req.query;

const promises = [];
promises.push(boiler_houses.findAll({ where: { equipment_area: { [Op.like]: '%' + term + '%'  }}}));
promises.push(compressor_houses.findAll({ where: { equipment_area: { [Op.like]: '%' + term + '%'}}}));
  
Promise.all(promises).then((data) => {
  console.log(data);
  res.render('gigs', {
    gigs: [...data[1]]
   })   
  })
  .catch(err => console.log(err));
})

Open in new window

findAll returns a promise - so simply capture those and pass them to the Promise in an array and let Promise.all deal with their resolution.
Omar MartinBridgemaker

Author

Commented:
Thank you Julian......it works.
Omar MartinBridgemaker

Author

Commented:
Thank you Julian....I can't thank you enough......thank you, thank you, thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial