Query Sqlalchemy from parent and child filter by child

Question

I'am using FastAPI and Sqlalchemy. I'm working on a system message of a social media. I want all messages by conversation only using the user_id.

I have this response, as you can see there there are two times the conversation with id_conversation 1. I would like both messages to be in the same dictionary :

[
  {
    "Conversation": {
      "id_post": 2,
      "id_conversation": 1
    },
    "Message": {
      "id_conversation": 1,
      "id_post": 2,
      "id_receiver": 2,
      "date_insert": "2023-12-12T16:38:35.703764",
      "id_sender": 1,
      "id_message": 1,
      "message_text": "Hello",
      "is_read": false
    }
  },
  {
    "Conversation": {
      "id_post": 2,
      "id_conversation": 1
    },
    "Message": {
      "id_conversation": 1,
      "id_post": 2,
      "id_receiver": 2,
      "date_insert": "2023-12-12T16:39:10.112846",
      "id_sender": 1,
      "id_message": 2,
      "message_text": "are u here ?",
      "is_read": false
    }
  },
  {
    "Conversation": {
      "id_post": 1,
      "id_conversation": 3
    },
    "Message": {
      "id_conversation": 3,
      "id_post": 1,
      "id_receiver": 2,
      "date_insert": "2023-12-12T16:40:53.849667",
      "id_sender": 3,
      "id_message": 4,
      "message_text": "Hey man",
      "is_read": false
    }
  }
]

There is my models.py :

class Conversation(Base):
    __tablename__ = "conversations"

    id_conversation = Column(Integer, primary_key=True, index=True)
    id_post = Column(Integer, ForeignKey("posts.id_post", ondelete="cascade"),nullable=False)
    messages = relationship('Message', back_populates='conversation')


    class Config:
        orm_mode = True

class Message(Base):
    __tablename__ = "messages"

    id_message =  Column(Integer, primary_key=True, index=True)
    id_conversation = Column(Integer, ForeignKey("conversations.id_conversation", ondelete="cascade"),nullable=False)
    id_post = Column(Integer, ForeignKey("posts.id_post", ondelete="cascade"),nullable=False)
    id_sender = Column(Integer, ForeignKey("users.id_user", ondelete="cascade"),nullable=False)
    id_receiver = Column(Integer, ForeignKey("users.id_user", ondelete="cascade"),nullable=False)
    message_text = Column(String(1000), nullable=False)
    date_insert = Column(DateTime, nullable=False, default=func.now())
    is_read = Column(Boolean, nullable=False, default=False)
    conversation = relationship('Conversation', back_populates='messages')


    class Config:
        orm_mode = True

Now the query :

def get_all_messages(db: Session, id_user: int):
    all_messages = db.query(models.Conversation, models.Message
                            ).join(models.Message, models.Conversation.id_conversation == models.Message.id_conversation
                                   ).filter(or_(models.Message.id_sender == id_user, 
                                                models.Message.id_receiver == id_user)
                                            ).all()

I tried : .group_by(models.Conversation.id_conversation), .group_by(models.Conversation) but the error : column "messages.id_message" must appear in the GROUP BY

Answer 1

I found this way and it's working

db.query(models.Conversation
                            ).options(joinedload(models.Conversation.messages)
                                      ).join(models.Message, models.Conversation.id_conversation == models.Message.id_conversation
                                             ).filter(or_(models.Message.id_receiver == id_user, models.Message.id_sender == id_user)
                                               ).all()