sqlalchemy 使用 CTE WITH 语法

来源：https://stackoverflow.com/questions/31620469/sqlalchemy-select-with-clause-statement-pgsql

q1 = s.query(distinct(Appl.refid), Appl).\
        filter(Appl.lastname.ilike('Williamson%')).\
        filter(Appl.firstname.ilike('d%')).\
        group_by(Appl).\
        order_by(Appl.refid, Appl.appldate.desc()).cte('distinct_query')

q2 = s.query(q1).order_by(q1.c.lastname, q1.c.firstname)
q2.all()

在 q2 的中，可以使用 session.query(func.max(q1.c.lastname)).scalar() 这样的语法。

使用 CTE with 进行向量搜索

from sqlalchemy.sql import text

def vector_search(body, v):
    s = db.session.query(models.FamousSay, models.FamousSay.vector.cosine_distance(v).label("d"))
    a = s.cte("a") # with a as ( ... )
    q = db.session.query(a).where(text(f"d <= {body.thresh}")).order_by("d").limit(body.limit)

    objs = q.all()
    data = []
    for obj in objs:
        data.append({
            "id": obj.id,
            "text": obj.text,
            "cid": obj.cid,
            "bid": obj.bid,
            "distance": obj.d
            })
    return data