Check data exist or not when inserting multiple data in array with knex.js

Question

For now, I insert the product first and insert multiple product tags in array.
But the product tags will be duplicate without validating the tag is existed or not.

How could I check tag is existed or not when I insert the tags?

Here is the code I used:

let product = {
  product: {
    name: 'techtech',
    tagline: '描述',
    thumbnail: 'images/products/techtech/thumbnail.png'
  },
  tags: [
    { name: 'Android', slug: 'android' },
    { name: 'MacOS', slug: 'macos' },
    { name: 'Windows', slug: 'windows' },
    { name: 'Linux', slug: 'linux' }
  ],
  links: [ { url: 'techtech.cc' } ],
  covers: [
    { url: 'images/products/techtech/cover_0.png', index: 0 },
    { url: 'images/products/techtech/cover_1.png', index: 1 }
  ]
}


 // insert product
return db.transaction(trx => {
  return trx('products')
  .insert(product.product)
  .returning('id')
  // insert product_tags
  .then(productIDs => {
    return trx(productTags)
    .insert(product.tags)
    .returning('id')
    // insert product_producttag_mapping
    .then(productTagIDs => {
      let productTags = productTagIDs.map((productTagID) => { 
        let productTag = {
          product_id: productID,
          producttag_id: productTagID
        }
        return productTag;
      });
      return trx('product_producttag_mapping')
      .insert(productTags)
      .returning('id')      
    })   
  })

Tobias Nickel · Accepted Answer · 2020-08-23 03:18:00Z

Be careful, when you insert the product, your variable is productIDs, but you are creating productTag useing productId. You might want to add const productId = productIds[0].

When creating the productTag, you also need to use .id, like the following. Because knex is returning you objects with the property.

    let productTag = {
      product_id: productID.id,
      producttag_id: productTagID.id
    }

but to your actual question: What about making the tag name unique? This would give you a nice error on insert. Without transaction you could just ignore that error. Postgres and Mysql have a nice feature ON CONFLICT DO NOTHING. I am not sure if the databases would return the existing id when they detect a duplication. So I think you do not get around reading the existing tags. Find them by their name. With an index on that column, it should be reasonable fast.

pro tip: when rewriting your code using async/await, the small mistakes become more obvious and you can avoid the callback hell. You will less likely be tempted to avoid an extra query.

Collectives™ on Stack Overflow

Check data exist or not when inserting multiple data in array with knex.js

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related