title: SQLAlchemy Association Proxy - Preventing Duplicate Entries
body: <p>I'm using an <a href="http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html" rel="nofollow noreferrer">Association Proxy</a> to set up an indirect many-to-many relationship where a <code>Submission</code> can have multiple <code>Role</code>s, and a <code>Role</code> can be associated with multiple <code>Submission</code>s. Note that I'm also using <code>flask-sqlalchemy</code>.</p>
  
  <pre><code>class Role(db.Model):
      __tablename__ = 'role'
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.Text)
  
      def __init__(self, name):
          self.name = name
  
  class Submission(db.Model):
      __tablename__ = 'submission'
      id = db.Column(db.Integer, primary_key=True)
  
      submission_to_role = relationship("SubmissionToRole", cascade="all, delete-orphan")
      roles = association_proxy('submission_to_role', 'role')
  
  
  class SubmissionToRole(db.Model):
      __tablename__ = 'submission_to_role'
  
      submission_id = db.Column(db.Integer, db.ForeignKey('submission.id', ondelete='CASCADE'), primary_key=True)
      role_id = db.Column(db.Integer, db.ForeignKey('role.id', ondelete='CASCADE'), primary_key=True)
      submission = relationship(Submission)
      role = relationship(Role)
  
      def __init__(self, role):
          self.role = role
  </code></pre>
  
  <p>I need behaviour like this:</p>
  
  <pre><code>submission = Submission()
  role_a = Role('Backend Developer')
  role_b = Role('Frontend Developer')
  db.session.add(submission)
  db.session.add(role_a)
  db.session.add(role_b)
  db.session.commit()
  
  submission.roles.append(role_a) # submission.roles length should become 1
  submission.roles.append(role_b) # submission.roles length should become 2
  submission.roles.append(role_a) # submission.roles length should remain 2, since I already associated role_a previously.
  </code></pre>
  
  <p>But instead, when I associate an already associated <code>Role</code>, SQLAlchemy  creates a duplicate Association Object.</p>
  
  <hr>
  
  <p>How can I accomplish my desired behaviour? I wrote a function for <code>Submission</code> that gets around it</p>
  
  <pre><code>def associate_role(self, role):
          assoc_model = SubmissionToRole.query.filter(
              SubmissionToRole.submission_id == self.id
          ).filter(
              SubmissionToRole.role_id == role.id
          ).first()
          if not assoc_model:
              self.roles.append(role)
  </code></pre>
  
  <p>but want something that's cleaner and easily reusable. Is there anything native to SQLAlchemy that I can use?</p>