如何在 SQLAlchemy JOIN 中返回两个表的结果? [英] How do I return results from both tables in a SQLAlchemy JOIN?

问题描述

我在 ORM 中定义了两个表:

I have two tables defined in my ORM as:

Base = declarative_base()

class GeneralLedger(Base):
  __tablename__ = 'generalledgers'
  id = Column(Integer, primary_key=True)
  invoiceId = Column(Integer)
  ..

class ConsolidatedLedger(Base):
  __tablename__ = 'consolidatedledgers'
  id = Column(Integer, primary_key = True)
  invoiceId = Column(Integer)

...

我在两个表之间没有设置任何关系.我按如下方式加入:

I don't have any relationship set between the two tables. I do a join as follows:

records = DBSession.query(GeneralLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all()

我也试过:

records = DBSession.query(GeneralLedger).filter(GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all()

在这两种情况下，当我在视图中显示结果时，只会显示 GeneralLedger 表中的条目.如何从同一结果集中的两个表中获取结果?我试过这个:

In both cases, when I display the results in my view, only the entries from the GeneralLedger table show up. How do I get results from both tables in the same result set? I've tried this:

records = DBSession.query(GeneralLedger, ConsolidatedLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all()

但是，由于某种原因，当我遍历模板 (Jinja2) 中的结果时，每一行的列值都是空的.此外，当计数:

But, for some reason, when I iterate through the results in my template (Jinja2), the values for the columns are empty for every single row. Also, when count:

total = DBSession.query(GeneralLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).count()

总行数是两个表中匹配记录的总和.我正在使用 webhelpers.paginate 来处理分页:

The total rows is the sum of the matching records from the two tables. I'm using webhelpers.paginate to handling paging:

query = DBSession.query(GeneralLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId)
records = paginate.Page(query, current_page, url=page_url)

并且发送到模板的结果集就好像删除了除ConslidatedLedger 表中的结果之外的所有结果.例如，我将页面总数设置为 20 条记录.如果该页面上有来自 ConslidatedLedger 的记录，则该页面将被截断，只显示来自 GeneralLedger 的记录，但分页没有中断.

and the result set sent to the template is as if all the results where there but the ones on the ConslidatedLedger table are removed. For example, I have my page total set to 20 records. If there are records from ConslidatedLedger on that page, the page is truncated, only showing records from GeneralLedger but the paging isn't broken.

有什么想法吗?谢谢！

推荐答案

records = DBSession.query(GeneralLedger, ConsolidatedLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all()

应该可行，但我认为在使用记录集时，您需要通过 records.GeneralLedger 和 records.ConsolidatedLedger 来引用它们:

should work but I think when working with the recordset you need to refer to them via records.GeneralLedger and records.ConsolidatedLedger:

for record in records:
    print record.GeneralLedger
    print record.ConsolidatedLedger

    print record.GeneralLedger.foo
    # ...etc

这篇关于如何在 SQLAlchemy JOIN 中返回两个表的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！