CODEWITHSUNDEEP
sqlalchemy
elifiner
elifiner

Reputation: 7575

SQLAlchemy distinct related objects

I have a simple messaging system with Conversation, User and Message objects defined like so:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

class Conversation(db.Model):
    id = db.Column(db.Integer, primary_key=True)

class Message(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String)
    author = db.relationship('User')
    conversation = db.relationship('Conversation', backref=db.backref('messages', lazy='dynamic'))

I would like to get all the Users that participate in a conversations.

It's easy for me in SQL:

select distinct users.*
from users inner join messages on messages.author_id = users.id
where messages.conversation_id = 1;

But I can't figure out how do that with SQLAlchemy's syntax so I get back objects and not just fields. Ideally I would like to implement it as a get_authors method on Conversation.

Upvotes: 2

Views: 2754

Answers (1)

zzzeek
zzzeek

Reputation: 75137

session.query(User).distinct().\
    join(Message, Message.author_id == User.id).\
    filter(Message.conversation_id == 1)

Upvotes: 5

Related Questions