ruby-on-rails - rails : create unique auto-incremental id based on sibling records

Question

我的rails项目中有3个模型，分别是User, Game, Match

用户可以在每个游戏中创建多个匹配项所以匹配的表结构就像

            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    |            |
+----+---------+---------+-------------+------------+

我想根据 user_id、game_id 和 match_type 值生成 match_name

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  |
+----+---------+---------+-------------+-------------+

如何在创建新记录期间在我的 Rails 模型中实现此自动增量值。任何帮助建议表示赞赏。

提前致谢。

Answer 1

我看到有两种方法可以解决这个问题:

• 数据库:触发器
• Rails:回调

触发器(假设是 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(); 

请注意，这未经测试。

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

同样，未经测试。

我更喜欢触发器解决方案，因为在通过纯 SQL 插入时可以跳过或完全忽略回调。

我还会添加“match_number”列而不是全名，然后在模型或装饰器或 View 助手(更灵活，I18n)中构造名称，但背后的逻辑保持不变。