I don't know why we use Sequelize(MySQL) findAndCountAll method. Because it sends two queries and I think it's wasteful.

Assume we have a Test table like below.

And call findAndCountAll and findAll method like below.

const result1 = await Test.findAndCountAll() --> {count: 2, rows: [{id: 1, name: 'a'}, {id: 2, name: 'b'}]}
const result2 = await Test.findAll() --> [{id: 1, name: 'a'}, {id: 2, name: 'b'}]

We can get count from result2 by call length (result2.length).

The findAndCountAll method sends the query twice, and the findAll method sends the query once.

-- findAndCountAll
SELECT count(*) FROM Test;
SELECT id, name FROM Test;

-- findAll
SELECT id, name FROM Test;

I think if we have so many rows in the Test table, this makes a large load(2 times than findAll) on the database.

That's why I thought findAndCountAll method is wasteful.

Is there a case what is findAndCountAll performs better than findAll and length? Or is there a reason why we use findAndCountAll method?

CodePudding user response：

The whole purpose of findANdCountAll is to use along with limit and offset options to organise server-side paging.
After you indicate both options (I strongly recommend adding the order option with a field or several fields so you will get predictable results of each page of records) you will see the main difference between the two SQL queries executed. The count query will be without LIMIT and OFFSET options to find out the total number of records considering all conditions and sorting in the query. At the same time, the select query will be WITH LIMIT and OFFSET options to get an indicated "page" of records.

-- findAndCountAll
SELECT count(*)
FROM Test WHERE name like '%test%';

SELECT id, name
FROM Test
WHERE name like '%test%'
LIMIT 10
OFFSET 0
ORDER BY name, id;

So here, for instance, you might get the total number of records equals to 100 and only 10 first records in the second select query. That way you will know how many records you have and how many pages as well to be able to indicate the correct LIMIT and OFFSET options to get desired page with records.

Of course, if you don't plan to use such pagination and don't indicate offset and limit options then you don't need to use findAndCountAll. It's enough to use findAll and check length prop.