title: Sqlalchemy: One to Many relationship combined with Many to Many relationship
body: <p>I've got a User and Group table with a many to many relationship</p>
  <pre><code>_usergroup_table = db.Table('usergroup_table', db.metadata,
      db.Column('user_id',  db.Integer, db.ForeignKey('user.id')),
      db.Column('group_id', db.Integer, db.ForeignKey('group.id')))
  
  class User(db.Model):
      &quot;&quot;&quot;Handles the usernames, passwords and the login status&quot;&quot;&quot;
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(60), nullable=False, unique=True)
  
  class Group(db.Model):
      &quot;&quot;&quot;Used for unix-style access control.&quot;&quot;&quot;
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(60), nullable=False)
      users = db.relationship('User', secondary=_usergroup_table,
                              backref='groups')
  </code></pre>
  <p>Now i'd like to add a primary group to the user class. Of course I could just add a group_id column and a relationship to the Group class, but this has drawbacks. I'd like to get all groups when calling User.group, including primary_group. The primary group should always be part of the groups relationship.</p>
  <h1>Edit:</h1>
  <p>It seems the way to go is the association object</p>
  <pre><code>class User(db.Model, UserMixin):
      &quot;&quot;&quot;Handles the usernames, passwords and the login status&quot;&quot;&quot;
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(60), nullable=False, unique=True)
  
      primary_group = db.relationship(UserGroup,
          primaryjoin=&quot;and_(User.id==UserGroup.user_id,UserGroup.primary==True)&quot;)
  
  class Group(db.Model):
      &quot;&quot;&quot;Used for unix-style access control.&quot;&quot;&quot;
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(60), nullable=False)
  
  class UserGroup(db.Model):
      user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
      group_id = db.Column(db.Integer, db.ForeignKey('group.id'))
      active = db.Column(db.Boolean, default=False)
  
      user = db.relationship(User, backref='groups', primaryjoin=(user_id==User.id))
      group = db.relationship(Group, backref='users', primaryjoin=(group_id==Group.id))
  </code></pre>
  <p>I could simplify this with the AssociationProxy, but how do I force only a single primary group per user?</p>