CODEWITHSUNDEEP
mysqlsqlruby-on-railsrubyactiverecord
martini-bonanza
martini-bonanza

Reputation: 1591

Using an index as an ActiveRecord attribute

I'm trying to model an bug tracker where each user has tickets with continuous ticket numbers:

User 1:

User 2:

By default, Rails gives each record a unique id, and I need another attribute, ticket_number, populated on createand depending on the user's ticket count.

I tried using Rails for that, with a before_create hook that does a user.tickets.count + 1, but this doesn't seem very safe since multiple tickets could be created at the same time by asynchronous workers for instance.

Can I leverage database indexes to take care of this ticket_number at the database level, just like it's happening for the id, and retrieve this ticket_number as an attribute?

Upvotes: 3

Views: 81

Answers (2)

xlembouras
xlembouras

Reputation: 8295

I think the safest way to go is to

  • add an extra attribute to your tickets ticket_number
  • add a unique constraint in the database layer for compound index user_id, ticket_number
  • add a uniqueness validator for the ticket_number scoped on user_id on your tickets to match the database layer
  • add a handler to construct the ticket_number and resolve the race conditions inside your model.

With this setup you can be sure that

  • rails will try to not allow invalid data,
  • the db won't accept two inconsistent user_id, ticket_number combinations if they get past rails
  • rails will handle the db exceptions gracefully and reassign the ticket_number value

You will need a

before_create :set_ticket_number which will calculate the correct ticket number (which will be assigned to the first record that wins on the race condition)

and a recalculate_number method to handle the records that couldn't be persisted because of the db uniqueness constraint (losers of race condition).

Upvotes: 1

midu
midu

Reputation: 1697

So, you might want to check-out Rails' counter_cache option on belongs_to, automates that behavior:

class Ticket
  belongs_to :user, counter_cache: true
end

# you need to add `tickets_count` to your `users` table (or `name_of_counter_count`)
class User
  has_many :tickets
end

You can then access #tickets_count from any instance of User. Rails makes sure to increment that number when you create a ticket and decrement it when you destroy it.

Careful though, it uses ActiveRecord callbacks, so if you skip them for some reason, the counter will get out of sync (you can reset them with #reset_counters(:counter_name)

There's also the counter_culture gem that will provide a more flexible solution.

As for race condition, I am not positive, but I think it's all done within the same transaction while the table is locked. To be verified.

Note: depending on your various validations and business logic, it may become very tricky to handle this only on the database level.

IMO: if that count is critical in your application, maybe it's worth querying it when you need it (SELECT COUNT(...))

Upvotes: 0

Related Questions