9

I would like to know wether there is a way to combine joining two tables, and retrieving the two entities only with their relevant columns.

I want to avoid doing a thing such select * after joining two tables and getting only column id from Table A and column address from table B. Working with python2.7 I heard of sqlalchemy feature called with_entities, but not sure how can it be mixed with this kind of join, lets work on this example from sqlalchemy tutorial:

u, a  = session.query(User, Address).\
                     filter(User.id==Address.user_id).\
                     filter(Address.email_address=='jack@google.com').\
                     first():
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
JavaSa
  • 5,813
  • 16
  • 71
  • 121
  • Possible duplicate of [querying and selecting specific column in SQLAlchemy](https://stackoverflow.com/questions/24012246/querying-and-selecting-specific-column-in-sqlalchemy) – Ilja Everilä Jul 25 '17 at 05:52

1 Answers1

9

Check out Query.join()

id, address  = session.query(A.id, B.address).\
             join(B, B.user_id == A.id).\ 
             filter(A.email_address=='jack@google.com').\
             first()

This can be done using a join or outerjoin depending on the use case, joins can be implicit or explicit. The .join second argument is the explicit join statement.

jackotonye
  • 3,537
  • 23
  • 31
  • If i have identical field names in both models, how should i query it? for example i want to bring the `address` field from both models: `session.query(A.id, A.address, B.address)....` – shayms8 Dec 01 '21 at 16:37
  • 1
    You’ll need to label the field using `B.address.label('b_address')` – jackotonye Dec 02 '21 at 06:28