CODEWITHSUNDEEP
node.jspostgresqlknex.js
charlestseng
charlestseng

Reputation: 21

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

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')      
    })   
  })

Upvotes: 0

Views: 412

Answers (1)

Tobias Nickel
Tobias Nickel

Reputation: 492

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.

Upvotes: 1

Related Questions