Question

0

73

Vistas

Chunk update using knex.js and MySQL

I am trying to learn how to use knex.js and as part of my learning I want to make some migration on my db, I want to update one of my columns. I am trying to do this with chunks (I know it is not the best solution, it's only for learning purposes). I am using MySQL. In my db I have a table that contains "email" column some of the rows end with ".com". I am trying to update this column by omitting the ".com". In my code, I select the relevant columns and I am using dash lib to chunk all my data. My chunked data looks like this:

my db : (https://ibb.co/c8kKtcb)

chunk no.1

[

RowDataPacket {id: 1, email: dan@gmail.com},

RowDataPacket {id: 2, email: john@gmail},

RowDataPacket {id: 3, email: steph@gmail},

]

chunk no.2

[

RowDataPacket {id: 4, email: eric@gmail},

RowDataPacket {id: 5, email: tommy@gmail.com},

RowDataPacket {id: 6, email: bill@gmail.com},

]

chunk no.3

[

RowDataPacket {id: 7, email: loe@gmail},

RowDataPacket {id: 8, email: helen@gmail.com},

RowDataPacket {id: 9, email: niky@gmail.com},

]

…

This is my code, I tried to do the update but I figure that I am doing something wrong since its list end .update() method work with key value. Can I manipulate my data?

exports.up = async knex => {
  
    const users = await knex("usersWeb")
      .select("id", "email")
      .where("email", "like", "%.com");
  
    const userChunks = _.chunk(users, 3);
    let chunckNumber = 1;
    for (const chunk of userChunks) {
      console.log("Chunck number: ", chunckNumber);
  
      const k = await knex("usersWeb")
        .whereIn(columnId, chunk.map(item => item.id))
        .update("email": ); // this line :(
    }
    chunckNumber++;

}

exports.down = async knex => {};

7 months ago · Juan Pablo Isaza

1 Respuestas

Responde la pregunta

0

Responde la pregunta

Encuentra empleos remotos

Accepted Answer · 2022-07-04T00:46:43.191Z

I solve it after some time and I forgot to share my solution, so here is it:


exports.up = async knex => {
    const users = await knex("usersWeb")
      .select("id")
      .where("email", "like", "%.com");
  
    const userChunks = _.chunk(users, 3);
    let chunckNumber = 1;
    for (const chunk of userChunks) {
      console.log("Chunck number: ", chunckNumber);
      const tupleOfAllEmailIds = "(" + chunk.map(item => `${item.id}`).join(", ") + ")";
  
      
      const query = `UPDATE "usersWeb" 
        SET "email" = REPLACE("email", ".com", "")
        WHERE id IN ${tupleOfAllEmailIds}
       `;
 
      await knex.raw(query);

    }
    chunckNumber++;

}

exports.down = async knex => {};