群炼金术的问题

Question

我对SqlAlchemy和一个groupBy子句有问题。请参阅下面的SqlAlchemy查询。你知道吗

我有一个SqlAlchemy查询，其中包含一个group\ U by子句，它引发了一个异常，'（cx_Oracle.DatabaseError错误)ORA-00979:不是“按表达式分组”。但是，当我得到SqlAlachemy查询生成的SQL并手动运行它时，查询工作正常。你知道吗

我不知道如何找出groupby子句有什么问题。有没有人对我如何继续调试这个问题并找出我能做些什么来解决这个问题有什么建议、想法和建议？你知道吗

提前谢谢， 道格

# create shorthand aliases
b = db.aliased(Batch)
bs = db.aliased(BatchingStatus)
bp = db.aliased(BatchPress)
bst = db.aliased(BatchState)
bit = db.aliased(BatchItem)
bin = db.aliased(BatchInput)
bpri = db.aliased(BatchPriority)
lcu = db.aliased(LCUser)
s = db.aliased(SubBatch)
w = db.aliased(WorkType)
ptw = db.aliased(LCProductToWorkType)
ctp = db.aliased(LCCategoryToProduct)
c = db.aliased(LCCategory)

# for correlated subquery
subq = (
    db.session.query(ctp.product_name)
        .join(c, c.category_id == ctp.category_id)
        .filter(func.lower(c.category_path) == category)
        .filter(ctp.active == 1)
)
# start of problem query
q = db.session.query(
    b.batch_signature.label('batch_signature'),
    b.batch_num,
    b.created_date.label('created_date'),
    bst.code.label('batch_state'),
    func.min(bin.promise_date).label('due_out'),
    bs.job_status,
    bp.press_id.label('press_id'),
    bp.description.label('press_description'),
    bp.code.label('press_code'),
    bp.active.label('press_active'),
    func.listagg(bin.item_id, ',').within_group(bin.item_id).label('subbatches'),
    bs.item_count.label('item_count'),
    bs.product.label('product'),
    bpri.code.label('priority'),
    ptw.display_format.label('product_display_format'),
    c.display_name.label('category_display_name'),
    lcu.coalesce_first_name,
    lcu.coalesce_last_name,
    lcu.coalesce_email,
) \
    .join(bs, (bs.batch_signature == b.batch_signature) & (bs.press_id == b.press_id)) \
    .join(bp, bp.press_id == b.press_id) \
    .join(bst, bst.state_id == b.state_id) \
    .join(bit, bit.batch_id == b.batch_id) \
    .join(bin, bin.batch_input_id == bit.batch_input_id) \
    .join(bpri, bpri.priority_id == bin.priority_id) \
    .join(lcu, lcu.username == bs.actor) \
    .join(s, s.subbatchno == func.to_char(bin.item_id)) \
    .join(w, w.worktypeenum == s.worktypeenum) \
    .join(ptw, ptw.worktypeenum == w.worktypeenum) \
    .join(ctp, ctp.category_to_product_id == ptw.category_to_product_id) \
    .join(c, c.category_id == ctp.category_id) \
    .filter(bs.product.in_(subq)) \
    .filter(b.state_id <= 200) \
    .group_by(
        b.batch_signature,
        b.batch_num,
        b.created_date,
        bst.code,
        bs.job_status,
        bp.press_id,
        bp.description,
        bp.code,
        bp.active,
        bs.item_count,
        bs.product,
        bpri.code,
        ptw.display_format,
        c.display_name,
        lcu.coalesce_first_name,
        lcu.coalesce_last_name,
        lcu.coalesce_email,
    ) \
    .order_by('batch_signature', 'batch_num', 'created_date')
try:
    retval = q.all()
except Exception as e:
    print e

上面没有显示模型，其中一些模型有@hybrid\u property/@.expression方法，比如lcu.coalesce\u名字\u列，试图隐藏@功能合并我认为是代码导致了组的问题。你知道吗