DEV: Move assignments from custom fields to a table (#169)

DEV: Move assignments from custom fields to a table (#169)

diff --git a/app/controllers/discourse_assign/assign_controller.rb b/app/controllers/discourse_assign/assign_controller.rb
index 612a3b2..98b1406 100644
--- a/app/controllers/discourse_assign/assign_controller.rb
+++ b/app/controllers/discourse_assign/assign_controller.rb
@@ -11,12 +11,11 @@ module DiscourseAssign
         .where('users.id <> ?', current_user.id)
         .joins(<<~SQL
           JOIN(
-                SELECT value::integer user_id, MAX(created_at) last_assigned
-                FROM topic_custom_fields
-                WHERE name = 'assigned_to_id'
-                GROUP BY value::integer
-                HAVING COUNT(*) < #{SiteSetting.max_assigned_topics}
-              ) as X ON X.user_id = users.id
+            SELECT assigned_to_id user_id, MAX(created_at) last_assigned
+            FROM assignments
+            GROUP BY assigned_to_id
+            HAVING COUNT(*) < #{SiteSetting.max_assigned_topics}
+          ) as X ON X.user_id = users.id
         SQL
         )
         .assign_allowed
@@ -33,18 +32,18 @@ module DiscourseAssign
       topic_id = params.require(:topic_id).to_i
       topic = Topic.find(topic_id)
 
-      assigned = TopicCustomField.where(
-        "topic_id = :topic_id AND name = 'assigned_to_id' AND value IS NOT NULL",
-        topic_id: topic_id
-      ).pluck(:value)
+      assigned_id = Assignment
+        .where(topic_id: topic_id)
+        .where.not(assigned_to_id: nil)
+        .pluck_first(:assigned_to_id)
 
-      if assigned && user_id = assigned[0]
-        extras = nil
-        if user = User.where(id: user_id).first
+      if assigned_id
+        if user = User.where(id: assigned_id).first
           extras = {
             assigned_to: serialize_data(user, BasicUserSerializer, root: false)
           }
         end
+
         return render_json_error(I18n.t('discourse_assign.already_claimed'), extras: extras)
       end
 
@@ -91,26 +90,28 @@ module DiscourseAssign
       topics = Topic
         .includes(:tags)
         .includes(:user)
-        .joins("JOIN topic_custom_fields tcf ON topics.id = tcf.topic_id AND tcf.name = 'assigned_to_id' AND tcf.value IS NOT NULL")
-        .order('tcf.value::integer, topics.bumped_at desc')
+        .joins("JOIN assignments a ON a.topic_id = topics.id AND a.assigned_to_id IS NOT NULL")
+        .order("a.assigned_to_id, topics.bumped_at desc")
         .offset(offset)
         .limit(limit)
 
       Topic.preload_custom_fields(topics, TopicList.preloaded_custom_fields)
 
+      assignments = Assignment.where(topic: topics).pluck(:topic_id, :assigned_to_id).to_h
+
       users = User
-        .where("users.id IN (SELECT value::int FROM topic_custom_fields WHERE name = 'assigned_to_id' AND topic_id IN (?))", topics.map(&:id))
-        .joins('join user_emails on user_emails.user_id = users.id AND user_emails.primary')
+        .where("users.id IN (?)", assignments.values.uniq)
+        .joins("join user_emails on user_emails.user_id = users.id AND user_emails.primary")
         .select(UserLookup.lookup_columns)
         .to_a
 
       User.preload_custom_fields(users, User.allowed_user_custom_fields(guardian))
 
-      users = users.to_h { |u| [u.id, u] }
-      topics.each do |t|
-        if id = t.custom_fields[TopicAssigner::ASSIGNED_TO_ID]
-          t.preload_assigned_to_user(users[id.to_i])
-        end
+      users_map = users.index_by(&:id)
+
+      topics.each do |topic|
+        user_id = assignments[topic.id]
+        topic.preload_assigned_to_user(users_map[user_id]) if user_id
       end
 
       render json: { topics: serialize_data(topics, AssignedTopicSerializer) }
@@ -129,26 +130,30 @@ module DiscourseAssign
       guardian.ensure_can_see_group_members!(group)
 
       members = User
-        .joins("LEFT OUTER JOIN group_users g on users.id=g.user_id")
-        .joins("LEFT OUTER JOIN topic_custom_fields tcf ON tcf.value::int = users.id")
-        .joins("LEFT OUTER JOIN topics t ON t.id = tcf.topic_id")
-        .where("tcf.name = 'assigned_to_id' AND g.group_id=? AND (users.id > 0) AND t.deleted_at IS NULL", group.id)
+        .joins("LEFT OUTER JOIN group_users g ON g.user_id = users.id")
+        .joins("LEFT OUTER JOIN assignments a ON a.assigned_to_id = users.id")
+        .joins("LEFT OUTER JOIN topics t ON t.id = a.topic_id")
+        .where("g.group_id = ? AND users.id > 0 AND t.deleted_at IS NULL", group.id)
+        .where("a.assigned_to_id IS NOT NULL")
         .order('COUNT(users.id) DESC')
         .group('users.id')
         .select('users.*, COUNT(users.id) as "assignments_count"')
-
-        members = members.where("users.name ILIKE :pattern OR users.username_lower ILIKE :pattern", pattern: "%#{params[:filter]}%") if params[:filter]
-
-      members = members
         .limit(limit)
         .offset(offset)
 
-      assignment_count = Topic.joins("JOIN topic_custom_fields tcf ON topics.id = tcf.topic_id AND tcf.name = 'assigned_to_id' AND tcf.value IS NOT NULL")
-        .where("tcf.value IN (SELECT group_users.user_id::varchar(255) FROM group_users WHERE (group_id IN (SELECT id FROM groups WHERE name = ?)))", group.name)
+      if params[:filter]
+        members = members.where(<<~SQL, pattern: "%#{params[:filter]}%")
+          users.name ILIKE :pattern OR users.username_lower ILIKE :pattern
+        SQL
+      end
+
+      assignment_count = Topic
+        .joins("JOIN assignments a ON a.topic_id = topics.id AND a.assigned_to_id IS NOT NULL")
+        .where("a.assigned_to_id IN (SELECT group_users.user_id FROM group_users WHERE (group_id IN (SELECT id FROM groups WHERE name = ?)))", group.name)
         .where("topics.deleted_at IS NULL")
         .count
 
-      render json: { members: serialize_data(members, GroupUserAssignedSerializer), "assignment_count" => assignment_count }
+      render json: { members: serialize_data(members, GroupUserAssignedSerializer), assignment_count: assignment_count }
     end
 
     private
diff --git a/app/models/assignment.rb b/app/models/assignment.rb
new file mode 100644
index 0000000..936c1cc
--- /dev/null
+++ b/app/models/assignment.rb
@@ -0,0 +1,5 @@
+# frozen_string_literal: true
+
+class Assignment < ActiveRecord::Base
+  belongs_to :topic
+end
diff --git a/db/migrate/20210627100932_create_assignments.rb b/db/migrate/20210627100932_create_assignments.rb
new file mode 100644
index 0000000..0eb7144
--- /dev/null
+++ b/db/migrate/20210627100932_create_assignments.rb
@@ -0,0 +1,15 @@
+# frozen_string_literal: true
+
+class CreateAssignments < ActiveRecord::Migration[6.1]
+  def change
+    create_table :assignments do |t|
+      t.integer :topic_id, null: false
+      t.integer :assigned_to_id, null: false
+      t.integer :assigned_by_user_id, null: false
+
+      t.timestamps
+    end
+
+    add_index :assignments, :topic_id, unique: true
+  end
+end
diff --git a/db/post_migrate/20210709101534_move_assignments_from_custom_fields_to_a_table.rb b/db/post_migrate/20210709101534_move_assignments_from_custom_fields_to_a_table.rb
new file mode 100644
index 0000000..d2db7ae
--- /dev/null
+++ b/db/post_migrate/20210709101534_move_assignments_from_custom_fields_to_a_table.rb
@@ -0,0 +1,22 @@
+# frozen_string_literal: true
+
+class MoveAssignmentsFromCustomFieldsToATable < ActiveRecord::Migration[6.1]
+  def up
+    execute <<~SQL
+      INSERT INTO assignments (topic_id, assigned_by_user_id, assigned_to_id, created_at, updated_at)
+      SELECT (
+        SELECT value::integer assigned_to_id
+        FROM topic_custom_fields tcf1
+        WHERE tcf1.name = 'assigned_to_id' AND tcf1.topic_id = tcf2.topic_id
+      ), value::integer assgined_by_id, topic_id, created_at, updated_at
+      FROM topic_custom_fields tcf2
+      WHERE name = 'assigned_by_id'
+      ORDER BY created_at DESC
+      ON CONFLICT DO NOTHING
+    SQL
+  end
+
+  def down
+    raise ActiveRecord::IrreversibleMigration
+  end
+end
diff --git a/jobs/scheduled/enqueue_reminders.rb b/jobs/scheduled/enqueue_reminders.rb

[... diff too long, it was truncated ...]

GitHub sha: 470dd939aa15a1d32e068d7de18c717067b3aecc

This commit appears in #169 which was approved by ZogStriP. It was merged by CvX.