Check if all entries exist in table

Question

Good Day,

I am using Sequelize mysql in nodeJs.

My problem is that, given an array of IDs such as [4,5,6,7,8]

And a table T where each row holds a ID. say, Table T:

ID

4

5

7

8

How do I search if all the ID in the array exist in the table?

Do i do a for loop and call find on every loop? Is this done asynchronously or synchronously? How do i wrap it in a promise if i want to do that?

I am not sure what is the proper way for searching if all IDs given an array exist in the table.

Thanks

Akina · Answer 1 · 2020-03-25T05:05:12.687

2

SELECT COUNT(DISTINCT id) = 5       -- the values amount in the array
FROM sourcetable
WHERE FIND_IN_SET(id, '4,5,6,7,8')  -- the array as CSV literal

This query will return 1 record with one field. Value 1 means that all array elements are present, 0 - that at least one element is absent.

Array must NOT contain duplicates.

edited Mar 25 '20 at 05:05

answered Mar 25 '20 at 04:45

Akina

39,301
5
14
25

score 1 · Answer 2 · answered Mar 25 '20 at 04:50

1

foreach loop will not work so go with for loop example here following

  const ids =  [4,5,6,7,8];
  for (const id of ids) {
    db.T
      .findOne({
        where: { id: id }
      }).then()
  }

answered Mar 25 '20 at 04:50

Adeel Nawaz

398
2
10

Do i need to wrap it in a promise or something to do with promises? – Ask the cat Mar 25 '20 at 04:55
promise and async function both will work just foreach loop is not async – Adeel Nawaz Mar 25 '20 at 05:00
1

i asked same this question few days ago you check that one as well https://stackoverflow.com/questions/60706243/how-to-use-promise-with-nested-foreach?noredirect=1#comment107405806_60706243 – Adeel Nawaz Mar 25 '20 at 05:01

score 0 · Answer 3 · answered Oct 26 '21 at 15:15

I think you can easily reach this with a findAll or a count. You provide the ids and ask to return them from DB if the number of items match you should be fine

model.findAll({
  where: {
    Id: {
      [Op.or]: [4,5,6,7,8]
    }
  }
});

You can do the same with count if you don't need the objects back

Check if all entries exist in table

3 Answers3