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!

score 19 · Accepted Answer · edited May 18 '23 at 19:26

19

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

edited May 18 '23 at 19:26

ggorlen

44,755
7
76
106

answered Dec 03 '13 at 20:09

Peter Tirrell

2,962
4
29
52

That worked but I think my use case needs only the records from the GeneralLedger db. Is there a way that I can do a query(GeneralLedger).join(...)? If I do my original query records = DBSession.query(GeneralLedger).join(ConsolidatedLedger, GeneralLedger.invoiceId == ConsolidatedLedger.invoiceId).all() I get only items from the table generalledgers but the pagination thinks all items are there and the count() does too. How can I get rid of the items from the conslidatedledgers table? – ericso Dec 04 '13 at 15:03
Hmmmm, I'm not familiar with the paginate process. Does the console log show the SQL query that is resulting, and is there a 1-many relationship between GeneralLedger and ConsolidatedLedger? If there's more than one ConsolidatedLedger for ever GeneralLedger record I could see there duplicate results. You could try a .distinct() instead of .all(), if instead of a join maybe compile a list of IDs and do a check if the list contains your GeneralLedger ID? – Peter Tirrell Dec 10 '13 at 20:51

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

1 Answers1

Linked

Related