asked on

Searching multiple tables at the same time in a database

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

Julian Hansen

What do you get when you console.log(data) in the then()?

Omar Martin

ASKER

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 Martin

ASKER

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

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Omar Martin

ASKER

Thank you Julian......it works.

Omar Martin

ASKER

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