How to maintain an auto increment field but per category

Phoenix Forum Questions / Help
,
1

I have a table with documents with separate categories and I want to have a number column which auto increments after adding a new document to a specified category

id |category | number
-----------------------------
1| cat_a      | 1
2 | cat_a     | 2
3 | cat_a     | 3
4 | cat_b     | 1

and now when I insert an document with cat_b i want to auto assign it to number: 2
I partially solved it by running max(number) where(category: "cat_b") before inserting but this is not an atomic operation - this way I still can have duplicates.
I can have 2 seperate tables but when if I depend on 2 columns (category and user)??
I was thinking about creating a trigger in the database but first I want to consult your opinion.

2

Its hard to figure out what you are asking, but if you wanted to query another column, in the table, like user:

   Select max(number) where "category"='cat_b' and "User"='thisguy'
3

I had the same requirement at $work :grinning:

You can avoid duplicates by 1) using a transaction and 2) the row lock option (see https://hexdocs.pm/ecto/Ecto.Query.html).

HTH!

4

This Ecto.Multi looks promising. Thank for help