How do I return results from both tables in a SQLAlchemy JOIN?

Question

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()

I've also tried:

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

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()

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()

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)

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.

Any thoughts? Thanks!

Answer 1

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

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