Let's say I have this array of data:
const ids = [
'7d8206d2-74bc-4b90-a237-37f92486cde4',
'e594fe7f-d529-4a2f-ab24-ffc4e102268c',
'7d8206d2-74bc-4b90-a237-37f92486cde4'
]
As you can see, there are duplicates ids and when I want to update it like that:
await knex("products")
.increment("purchasesCount")
.whereIn("id", ids)
In my purchasesCount column I can see values (1, 1), but it should be (2, 1) because of duplicate ids. Is there a way to fix it?
The simplest solution is to issue one query per one purchase, as follows:
for (const id of ids) {
await knex("products").increment("purchasesCount").where({ id: id });
}
If this is impossible for some reason (e.g. performance), then you have 2 options:
.whereIn("id", idsWithThisCountOnly). So, in the example above you'd do 2 updates, one for all ids which were found only once in the array and one for all ids which were doubled. You'll probably need a Map of id => count for counting duplicates, and will need to invert it later into the form of count => idsWithThisCountOnly[].GROUP BY with COUNT when retrieving the data. This should offer better write performance (INSERTs are usually faster than UPDATEs), but at an increased storage size and slower reads.Option 2. may be preferrable if you already have a "transactions" or "sales" table with line-items and are accessing the summary data infrequently.