Question 392821 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQLAlchemy: select only those parents which don't have children records

I have 2 tables: chat and chat_messages. I want to get only those chats which don’t have chat_messages from users (they may have messages from other sources like admins).

This code returns 0 records, I can’t figure out why:

message_count_subquery = db.session.query(
                ChatMessage.chat_id,
                func.count(ChatMessage.id).label('message_count')
            ).filter(ChatMessage.origin == 'user').group_by(ChatMessage.chat_id).subquery()

message_count_alias = aliased(message_count_subquery)

chats = db.session.query(Chat).join(
                    message_count_alias,
                    Chat.id == message_count_alias.c.chat_id
                ).filter(
                    (message_count_alias.c.message_count == None) | (message_count_alias.c.message_count == 0)
                ).all()

It’s working when I want the opposite, to find all the records with at least one message from a user with this version:

chats = db.session.query(Chat).join(EnumChatMessage).join(
                    message_count_alias,
                    Chat.id == message_count_alias.c.chat_id
                ).filter(
                    and_(message_count_alias.c.message_count > 0)
                ).all()
Tags:

2

Answers


  1. 0

    Probably need outerjoin instead of join.

    Login or Signup to reply.
  2. 0

    Code for @Ian Wilson‘s answer

    message_count_subquery = aliased(
    select(
        ChatMessage.chat_id,
        func.count(ChatMessage.id).label('message_count')
    ).filter(
        ChatMessage.origin == 'user'
    ).group_by(
        ChatMessage.chat_id
    ).subquery()
)

chats = db.session.query(Chat).outerjoin(
    message_count_alias,
    Chat.id == message_count_alias.c.chat_id
).filter(
    or_(message_count_alias.c.message_count.is_(None), message_count_alias.c.message_count == 0)
).all()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search