How to query a one to many/many to many relationship in Flask SQL Alchemy?
<p>These are two database models that are important in my problem.  </p>

<p><b>I have established a one to many relationship (a Conversation can have multiple Messages)  </b>
There is also a many to many relationship established between User and Conversation.  </p>

<p><b>After obtaining two User objects, say <i>user1</i> and <i>user2</i>, I need to find the conversation that contains both users, if it exists. After getting a conversation object, say <i>current_convo</i>, I also need to query all the messages in that conversation. How could these two queries be done?</b></p>

<pre><code>class Conversation(db.Model):
    __tablename__ = 'conversation'

    id = db.Column('id', db.Integer, primary_key=True)
    users = db.relationship("User", secondary=relationship_table)
    messages = db.relationship("Message", backref="conversation", lazy="dynamic")


class Message(db.Model):
    __tablename__ = 'message'

    id = db.Column('id', db.Integer, primary_key=True)
    message = db.Column('message', db.String)
    timestamp = db.Column('timestamp', db.String)
    sender = db.Column('sender', db.String)
    conversation_id = db.Column(db.Integer, db.ForeignKey('conversation.id'))

class User(db.Model, UserMixin):
    __tablename__ = 'user'

    id = db.Column('id', db.Integer, primary_key=True)
    username = db.Column('username', db.String(100), unique=True, index=True)
    password = db.Column('password', db.String(100))
    email = db.Column('email', db.String(100), unique=True, index=True)
    authenticated = db.Column('authenticated', db.Boolean, default=False)
</code></pre>

<p>The best way I know to do this is to use SQLAlchemy's <a href="http://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.contains" rel="nofollow">contains</a>. </p>

<pre><code>Conversation.query.filter(
    Conversation.users.contains(user1),
    Conversation.users.contains(user2)
)
</code></pre>

python flask sqlalchemy