sql alchemy subquery join

Question

Hey guys i having trouble to convert this psql query into an sqlalchemy statement. I have tested the query in postgresql and its still working but i cant convert them into sqlalchemy syntax. Of course i can execute raw sql with sqlalchemy but my whole project is using the sqlalchemy syntax, i don´t want to "break" with this now.

    select
inbox_id,
inboxparts_unseen_count,
inboxparts_ignored,
inbox_last_sent_appuser_id,
message_msg,
message_created,
message_deleted

from appuser

join inboxparts
on inboxparts_appuser_id = appuser_id

join inbox
on inbox_id = inboxparts_inbox_id

left join message on
message_id = inbox_last_message_id

join profile
on profile_appuser_id = (
    select 
        inboxparts_appuser_id
    from inboxparts
    where 
        inboxparts_appuser_id != 435
    and
        inboxparts_inbox_id = inbox_id
    )

where appuser_id = 435

I have started with this stmt:

stmt = (
            select(
                InboxParticipants.inboxparts_unseen_count,
                InboxParticipants.inboxparts_ignored,
                Inbox.inbox_id,
                Inbox.inbox_last_sent_appuser_id,
                Message.message_msg,
                Message.message_created,
                Message.message_deleted,
            )
            .select_from(AppuserModel)
            .join(
                InboxParticipants,
                InboxParticipants.inboxparts_appuser_id
                == AppuserModel.appuser_id,
            )
            .join(
                Inbox, Inbox.inbox_id == InboxParticipants.inboxparts_inbox_id
            )
            .join(
                Message,
                Message.message_id == Inbox.inbox_last_message_id,
                isouter=True,
            )
            .join(
                Profile,
                Profile.profile_appuser_id
                == (
                    select(InboxParticipants.inboxparts_appuser_id)
                    .select_from(InboxParticipants)
                    .where(
                        and_(
                            InboxParticipants.inboxparts_appuser_id
                            != appuser_id,
                            InboxParticipants.inboxparts_inbox_id
                            == Inbox.inbox_id,
                        )
                    ),
                ),
            )
            .where(
               AppuserModel.appuser_id == appuser_id
            )
        )

The "tricky" part is the on profile_appuser_id = ... followed by a subquery. Thanks a lot for recommendations

Answer 1

I think you write it in wrong level

how about this?

stmt = (
            select(
                InboxParticipants.inboxparts_unseen_count,
                InboxParticipants.inboxparts_ignored,
                Inbox.inbox_id,
                Inbox.inbox_last_sent_appuser_id,
                Message.message_msg,
                Message.message_created,
                Message.message_deleted,
            )
            .select_from(
                select(
                    AppuserModel
                )
                .join(
                    InboxParticipants,
                    InboxParticipants.inboxparts_appuser_id
                    == AppuserModel.appuser_id,
                )
                .join(
                    Inbox, Inbox.inbox_id == InboxParticipants.inboxparts_inbox_id
                )
                .join(
                    Message,
                    Message.message_id == Inbox.inbox_last_message_id,
                    isouter=True,
                )
                .join(
                    Profile,
                    Profile.profile_appuser_id
                    == (
                        select(InboxParticipants.inboxparts_appuser_id)
                        .select_from(InboxParticipants)
                        .where(
                            and_(
                                InboxParticipants.inboxparts_appuser_id
                                != appuser_id,
                                InboxParticipants.inboxparts_inbox_id
                                == Inbox.inbox_id,
                            )
                        ),
                    ),
                )
                .where(
                   AppuserModel.appuser_id == appuser_id
                )
            )
        )