outerjoin.py

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

Base = declarative_base()

atob = Table(
    "atob",
    Base.metadata,
    Column("aid", ForeignKey("a.id"), primary_key=True),
    Column("bid", ForeignKey("b.id"), primary_key=True),
)


class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    data = Column(String)
    bs = relationship("B", secondary=atob)


class B(Base):
    __tablename__ = "b"
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey("a.id"))
    data = Column(String)


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

s.add(A(bs=[B()]))
s.commit()

"""
SELECT a.id AS a_id, a.data AS a_data
FROM a
WHERE EXISTS (SELECT 1
FROM atob, b
WHERE a.id = atob.aid AND b.id = atob.bid AND b.data = ?)
"""
print(s.query(A).filter(A.bs.any(B.data == "foo")))


# mix outerjoin with any, could work like this, but I think this is the
# same result as above
"""
SELECT a.id AS a_id, a.data AS a_data
FROM a LEFT OUTER JOIN atob ON a.id = atob.aid
WHERE EXISTS (SELECT 1
FROM b
WHERE a.id = atob.aid AND b.id = atob.bid AND b.data = ?)
"""
print(
    s.query(A)
    .outerjoin(atob)
    .filter(A.bs.any(B.data == "foo").correlate(atob))
)

search for A that has no B:

s = Session(e)

s.add(A(data="I have one b", bs=[B()]))
s.add(A(data="I have no bs", bs=[]))
s.commit()

print(s.query(A.data).filter(~A.bs.any()).all())

output:

SELECT a.data AS a_data 
FROM a 
WHERE NOT (EXISTS (SELECT 1 
FROM atob, b 
WHERE a.id = atob.aid AND b.id = atob.bid))
2019-10-20 22:00:41,423 INFO sqlalchemy.engine.base.Engine ()
[('I have no bs',)]