How to check if two columns references the same record?

Question

I have 3 tables. User, Task and Tags.

• A user have many tasks.
• A user have many tags.
• A task have many tags.

So for I get this columns:

• User: id; email
• Tag: id; user_id; name
• Task: id; user_id; description

To manage the fact that a task can have many tags, I added a 4th table, taged_tasks, that is a junction table between Tags and Tasks.

• This table have 3 columns : id, task_id, tag_id.

I want to add a constraint into my migrations, that checks that task_id and tag_id belongs to the same user.

But I don't find how to do that, is it even possible to implement that constraint in DB? Or is rails hook (like before_create) the only way to do this check? Because I don't want to have 'invalid' record into this taged_tasks table.

Answer 1

It's possible to validate this either at the database level or at the app level. There are multiple ways to do so: in the database, you could add a trigger; in the app, you could use a Rails validation or scope the relation only to tasks/tags of the same user.

With the Rails method, you could do it by defining an actual HABTM relation and scoping it:

class Tag < ApplicationRecord
  belongs_to :user
  has_and_belongs_to_many :tasks, ->(tag) { where(user_id: tag.user_id) }
end

class Task < ApplicationRecord
  belongs_to :user
  has_and_belongs_to_many :tags, ->(task) { where(user_id: task.user_id) }
end

This should prevent invalid relations from being created, but I'm not positive on how strong a guarantee it offers. A validation on the join table would be a little simpler:

class TagTask < ApplicationRecord
  belongs_to :task
  belongs_to :tag

  validate do
    tag.user == task.user
  end
end

The trigger approach is less Rails-y but offers a stronger guarantee that you won't end up with invalid data. This likely has some errors, but something like:

CREATE OR REPLACE FUNCTION check_same_user()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $function$
BEGIN

    if (NOT EXISTS (SELECT 1 FROM tags JOIN tasks USING(user_id) WHERE tag_id = new.tag_id AND task_id = new.task_id)) then
        raise exception 'task and tag belong to different users!';
    end if;
    
    return new;
    
END;
$function$
;

-- run the above trigger before every insert/update
CREATE TRIGGER ensure_same_user
  BEFORE INSERT OR UPDATE
  ON tagged_tasks
  EXECUTE PROCEDURE check_same_user();