asked on

How to count the number of rows in a table?

I am using sequelize in node.js. At the moment I can search multiple tables and produce the the results as data. I have done so using the following code:

router.get("/alldata", (req, res) => 

Promise.all([chemical_bays.findAll(), electricals.findAll(), fire_services.findAll(), instruments.findAll(), mechanicals.findAll(), microwaves.findAll(), tools.findAll(), water_cays.findAll(), buildings.findAll(), substations.findAll(), fences_gates_cctv.findAll(), steam_plant_other.findAll(), information_technology.findAll(), ])
.then((data) => {  res.render('gigs', {gigs: [...data[0], ...data[1], ...data[2], ...data[3], ...data[4], ...data[5], ...data[6], ...data[7], ...data[8], ...data[9], ...data[10], ...data[11], ...data[12]] }) }).catch(err => console.log(err)));

Open in new window

However, I wish now to count the results and have them displayed, however, if I use the aggregate attribute, "count", I am receiving an error. Here is the code I am trying to use:

router.get("/alldata", (req, res) => Promise.all([chemical_bays.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['chemical_bays.id'],
raw: true,
order: sequelize.literal('count DESC')}), electricals.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['electricals.id'],
raw: true,
order: sequelize.literal('count DESC')}), fire_services.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['fire_services.id'],
raw: true,
order: sequelize.literal('count DESC')}), instruments.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['instruments.id'],
raw: true,
order: sequelize.literal('count DESC')}), mechanicals.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['mechanicals.id'],
raw: true,
order: sequelize.literal('count DESC')}), microwaves.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['microwaves.id'],
raw: true,
order: sequelize.literal('count DESC')}), tools.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['tools.id'],
raw: true,
order: sequelize.literal('count DESC')}), water_cays.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['water_cays.id'],
raw: true,
order: sequelize.literal('count DESC')}), buildings.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['buildings.id'],
raw: true,
order: sequelize.literal('count DESC')}), substations.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['substations.id'],
raw: true,
order: sequelize.literal('count DESC')}), fences_gates_cctv.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['fences_gates_cctv.id'],
raw: true,
order: sequelize.literal('count DESC')}), steam_plant_other.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['steam_plant_other.id'],
raw: true,
order: sequelize.literal('count DESC')}), information_technology.findAll({attributes: ['id', [sequelize.fn('count', sequelize.col('id')), 'count']],
group : ['information_technology.id'],
raw: true,
order: sequelize.literal('count DESC')}), ])
.then((data) => {res.render('gigs', {
    //gigs:data[0],
    //gigs2:data[1] 
    gigs: [...data[0], ...data[1], ...data[2], ...data[3], ...data[4], ...data[5], ...data[6], ...data[7], ...data[8], ...data[9], ...data[10], ...data[11], ...data[12]]
   })   
  })
  .catch(err => console.log(err)));

Open in new window

Omar Martin

ASKER

I have deployed with Heroku and the error says, "Internal Server Error".

Julian Hansen

Don't be afraid of line breaks and formatting - there are no extra points for getting your code onto one line - and no performance perks either. Readability wins every time.
Also take a look at array.flat() if you are using Node 11 or higher.

Promise.all([
  chemical_bays.findAll(), 
  electricals.findAll(), 
  fire_services.findAll(), 
  instruments.findAll(), 
  mechanicals.findAll(), 
  microwaves.findAll(), 
  tools.findAll(), 
  water_cays.findAll(), 
  buildings.findAll(), 
  substations.findAll(), 
  fences_gates_cctv.findAll(), 
  steam_plant_other.findAll(), 
  information_technology.findAll(), 
])
  .then((data) => {  
     const items = data.flat(1); // Flatten to depth 1
     const totalItems = data.length; // Get the length of the data sent
     res.render('gigs', {gigs: items })
  })
  .catch(err => console.log(err)));

Open in new window

Omar Martin

ASKER

I seem to be getting all the data still (see photo) but I cannot get the count of how many rows I am getting.....

I used the following code...

.then((data) => {
const items = data.flat(1); // Flatten to depth 1
const totalItems = data.length; // Get the length of the data sent
res.render('gigs', {gigs: totalitems })
})

Julian Hansen

What does the data look like in the browser - can you post it here.

Omar Martin

ASKER

I did post it above in the photo attached in the previous message.

Julian Hansen

Please can you post the RAW JSON - I need to see what the data being sent to the browser looks like - not how it is rendered.

Omar Martin

ASKER

See above the photo of what is displayed in the console. Only the missing photos are displayed. I still don't see the total count.

Here is the code entered.

Promise.all([chemical_bays.findAll(), electricals.findAll(), fire_services.findAll(), instruments.findAll(), mechanicals.findAll(), microwaves.findAll(), tools.findAll(), water_cays.findAll(), buildings.findAll(), substations.findAll(), fences_gates_cctv.findAll(), steam_plant_other.findAll(), information_technology.findAll(), ])
.then((data) => {
const items = data.flat(1); // Flatten to depth 1
const totalItems = data.length; // Get the length of the data sent
res.render('gigs', {gigs: items })
})
.catch(err => console.log(err)));

Julian Hansen

I need to see the data that is returned from the server.

Omar Martin

ASKER

Where can I get this?......other than the output that is returned to the screen.....I recently deployed to Heroku so I'm new to this.

Julian Hansen

 .then((data) => {  
     console.log(JSON.stringify(data)); /* <===== ADD THIS */
     const items = data.flat(1); // Flatten to depth 1
     const totalItems = data.length; // Get the length of the data sent
     res.render('gigs', {gigs: totalitems })
  })

Open in new window

Add the line above and then copy the result from the console.

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