SQLAlchemy LIKE ALL ORM analog

Question

I need to find documents that satisfy the entire list of passed parameters. I did it using raw query, but for my project specs, raw query can't be used, and I should use ORM.

Raw query is:

SELECT * 
  FROM outbox_document 
  WHERE document_summary like all(array['%par1%', '%par2%', '%par3%']);

It's works well, but I can't find an ORM analog for LIKE ALL. Please help!

Answer 1

You can use sqlalchemy.all_.

OutBoxDocument.document_summary.like(all_(["%par1%", "%par2%", "%par3%"]))

This generates the following query

SELECT outbox_document.id, outbox_document.document_summary 
FROM outbox_document 
WHERE outbox_document.document_summary LIKE ALL (%(param_1)s)

Complete code

from sqlalchemy import create_engine, select, all_
from sqlalchemy.orm import Mapped, DeclarativeBase, mapped_column, Session

engine = create_engine("postgresql+psycopg://some_connection_string")

class Base(DeclarativeBase):
    pass

class OutBoxDocument(Base):
    __tablename__ = "outbox_document"
    id: Mapped[int] = mapped_column(primary_key=True)
    document_summary: Mapped[str]

Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(OutBoxDocument(document_summary="par1 par2"))
    session.add(OutBoxDocument(document_summary="par1 par2 par3"))
    session.add(OutBoxDocument(document_summary="par1 par3"))
    session.commit()

with Session(engine) as session:
    statement = select(OutBoxDocument).where(OutBoxDocument.document_summary.like(all_(["%par1%", "%par2%", "%par3%"])))
    result = session.scalars(statement).all()
    print(result)

The flask sqlalchemy version would be db.session.scalars(statement).all()