CODEWITHSUNDEEP
ruby-on-railsrubydatabase
Jaydev Shiroya
Jaydev Shiroya

Reputation: 57

Rails: create unique auto-incremental id based on sibling records

I have three models in my rails project, namely User, Game, Match

user can create many matches on each game so table structure for matches is like

            table name: game_matches

+----+---------+---------+-------------+------------+
| id | user_id | game_id | match_type  | match_name |
+----+---------+---------+-------------+------------+
|  1 |       1 |       1 | practice    |            |
|  2 |       3 |       2 | challenge   |            |
|  3 |       1 |       1 | practice    |            |
|  4 |       3 |       2 | challenge   |            |
|  5 |       1 |       1 | challenge   |            |
|  6 |       3 |       2 | practice    |            |
+----+---------+---------+-------------+------------+

i want to generate match_name based on user_id, game_id and match_type values

for example match_name should be create like below


+----+---------+---------+-------------+-------------+
| id | user_id | game_id | match_type  | match_name  |
+----+---------+---------+-------------+-------------+
|  1 |       1 |       1 | practice    | Practice 1  |
|  2 |       3 |       2 | challenge   | Challenge 1 |
|  3 |       1 |       1 | practice    | Practice 2  |
|  4 |       3 |       2 | challenge   | Challenge 2 |
|  5 |       1 |       1 | challenge   | Challenge 1 |
|  6 |       3 |       2 | practice    | Practice 1  |
+----+---------+---------+-------------+-------------+

How can i achieve this auto incremental value in my rails model during new record creation. Any help suggestions appreciated.

Thanks in advance.

Upvotes: 2

Views: 343

Answers (2)

Pascal
Pascal

Reputation: 8637

I see two ways you can solve this:

  • DB: trigger
  • Rails: callback

Trigger (assuming Postgres):

  DROP TRIGGER IF EXISTS trigger_add_match_name ON customers;
  DROP FUNCTION IF EXISTS function_add_match_name();
  CREATE FUNCTION function_add_match_name()
  RETURNS trigger AS $$
  BEGIN
    NEW.match_name := (
      SELECT
      CONCAT(game_matches.match_type, ' ', COALESCE(count(*), 0))
      FROM game_matches
      WHERE game_matches.user_id = NEW.user_id AND game_matches.match_type = NEW.match_type
    );
    RETURN NEW;
  END
  $$ LANGUAGE 'plpgsql';

  CREATE TRIGGER trigger_add_match_name
  BEFORE INSERT ON game_matches
  FOR EACH ROW
  EXECUTE PROCEDURE function_add_match_name();

Please note that this is not tested.

Rails

class GameMatch
  before_create :assign_match_name

  private

  def assign_match_name
   number = GameMatch.where(user_id: user_id, match_type: match_type).count || 0
   name = "#{match_type} #{number + 1}"
   self.match_name = name
  end
end

Again, untested.

I'd prefer the trigger solution since callbacks can be skipped or ommited altogether when inserting via pure SQL.

Also I'd add "match_number" column instead of the full name and then construct the name within the Model or a Decorator or a view Helper (more flexible, I18n) but the logic behind stays the same.

Upvotes: 6

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

You should retrieve the last match_name for these user and game, split it, increase the counter and join back with a space. Unfortunately, SQL does not provide SPLIT function, so somewhat like below would be a good start:

SELECT match_name
FROM match_name
WHERE user_id = 3
  AND game_id = 2
ORDER BY id DESC
LIMIT 1

I would actually better create a match_number column of type INT to keep the number by type and produce a name by concatenation the type with this number.

Upvotes: 0

Related Questions