How to loop cy.task according to the result

Question

I'm testing the CRUDs for a page in my application, where a registry requires an unique ID as a 5 digit number to be successfully created.

I would like to get a random available ID of that table to use in the tests, that can be achieved with the following sql query:

    cy.task(
      'sqlQuery',
      `select count(*) as "count" from clients where client_id = '${
        Math.floor(Math.random() * 90000) + 10000
      }'`
    )

The point is that the random number perhaps is not available to be used as the ID, so i would need to loop this same task until the generated number is available:

    cy.task(
      'sqlQuery',
      `select count(*) as "count" from clients where client_id = '${
        Math.floor(Math.random() * 90000) + 10000
      }'`
    ).then((result: any) => {
      if (result.rows[0].count === 0) {
        // My code to submit the form with the generated number
        // break the loop
      } else {
        // Repeat all of this (where i'm asking for help)
      }
    })

If count returns 0 it means it's there's no registry using it, otherwise it's not available.

I guess the final solution could be using some while loop, but I found this way would be more clear to you all know what i'm needing.

I tried some approachs like this, but just discovery that its impossible to assign a new value to a variable inside the .then block, so it seems like i did an endless loop.

    let available = false
    do {
      cy.task(
        'sqlQuery',
        `select count(*) as "count" from clients client_id = '${
          Math.floor(Math.random() * 90000) + 10000
        }'`
      ).then((result: any) => {
        if (result.rows[0].count === 0) {
          // Code to submit the form with the generated number
          available = true
        }
      })
    } while (available === false)

You can't get it from the `auto_increment` value? Something like `SHOW TABLE STATUS LIKE 'clients'`, or make a query from `information_schema` — Ingo Guilherme Both Eyng, Feb 02 '23 at 21:13
Right, this could be the solution for a single register step, but actually, in order to optimize time, I have 3 files with this kind of test running simoutaneously. Individualy they run ok using the `auto_increment` , but with the parallel run they'll all be doing registrations getting the same ID, and some will eventually fail later on the registration. — Leomelzer, Feb 02 '23 at 21:32

Bratt · Accepted Answer · 2023-02-02T23:03:54.013

2

Ask SQL what ids are present and check the random one against those?

Something like this:

cy.task('sqlQuery', 'select client_id from clients')
  .then((result: any) => {
    const ids: string[] = result.rows;

    function getNewId(trys = 0) {
      if (trys > 1000) throw new Error('failed')
      const newId = Math.floor(Math.random() * 90000) + 10000
      if (ids.includes(newId)) {
        return getNewId(++trys)
      }
      return newId
    }

    const newId = getNewId()
    cy.wrap(newId).as('newId')
  })

Update

Changed to a recursive javascript function, as I found when testing .should() it does not re-evaluate Math.floor(Math.random() * 90000) + 10000

It's been a while since I did some SQL, but maybe this correlated subquery is more efficient

SELECT last + 1 as newId FROM (
  SELECT MAX(id) as last FROM clients
)

edited Feb 02 '23 at 23:03

answered Feb 02 '23 at 21:43

Bratt

38
4

Thanks for your answer. `Ids` array will contain all the already used IDS, which means it's not possible to create new registries with them. Actually I want the reverse, to get an available ID. This could be achieved with one more array declaration, but I think it could be not so performatic since ID can be up to 5 digit number, do you have any idea to this? – Leomelzer Feb 02 '23 at 22:17
Ok, I saw in your SQL `clients client_id = '${random...`. You can make a function, either javascript or with Cypress `.should()`. I will add above. – Bratt Feb 02 '23 at 22:24
Thanks a lot, this was pretty close to a soluction, but it was still returning unavailable ids, not sure but i think it's because ids at this point was not an array with only the actual id values, but an object with the columnname and value. I just altered the condition to use some accessing the id value instead, and its solved :) just going to improve the typage later. thanks again, was very useful. – Leomelzer Feb 03 '23 at 15:33

Ingo Guilherme Both Eyng · Answer 2 · 2023-02-02T23:23:48.830

You can use a query like this to get 3 unused IDs for your tests

MySQL:

SELECT a.id+1 as 'ids'
  FROM clients a
  WHERE 
    NOT EXISTS (SELECT * FROM clients b WHERE a.id+1 = b.id) AND
    a.id+1 < 100000
  ORDER BY a.id
  LIMIT 3

Oracle 11:

SELECT a.id+1 as ids
  FROM clients a
  WHERE
    NOT EXISTS (SELECT * FROM clients b WHERE a.id+1 = b.id) AND
    a.id+1 < 10000 AND
    rownum <= 3
  ORDER BY a.id;

Oracle 12:

SELECT a.id+1 as ids
  FROM clients a
  WHERE
    NOT EXISTS (SELECT * FROM clients b WHERE a.id+1 = b.id) AND
    a.id+1 < 10000
  ORDER BY a.id
  FETCH FIRST 3 ROWS ONLY;

This will search for unused IDs checking always the next ID, limiting it to 3 rows (you can limit the amount you need) to improve performance. And only available IDs, under 100000 (has only 5 digits)

For example:

Your table has de IDs (1, 3, 5, 6, 7, 9, 10)

This query will check if the IDs (2, 4, 6, 8, 10, 11) exist in the table. If it doesnt exists, it will return. So this example will return (2, 4, 8)

How to loop cy.task according to the result

2 Answers2