15

I'm trying to do a join from two tables in flask-sqlalchemy and I want all the columns from both tables but if I execute:

Company.query.join(Buyer, Buyer.buyer_id == Company.id).all()

I have only the columns from Company (It returns, in fact, a Company object).

I know I can do something like:

Company.query.join(Buyer, Buyer.buyer_id == Company.id) \
             .add_columns(Buyer.id, Buyer.name, etc..).all()

It returns in this case:

(<Company 2>, 1, 'S67FG', etc..)

the problem is that I have a lot of columns and also I don't know how to marshmallow the returned obj with flask-marshmallow (with nested fields does not work).

Is there a way to return a new obj with columns from the two tables? What is for you the best way to manage these situations?

Any suggestion is highly appreciated. Thanks

davidism
  • 121,510
  • 29
  • 395
  • 339
disgra
  • 683
  • 3
  • 6
  • 18
  • Does this answer your question? [How do I return results from both tables in a SQLAlchemy JOIN?](https://stackoverflow.com/questions/20357540/how-do-i-return-results-from-both-tables-in-a-sqlalchemy-join) – ggorlen May 18 '23 at 19:26

1 Answers1

13

at the end I've achieved this by using this simple sqlalchemy query:

db.session.query(Company, Buyer).join(Buyer, Buyer.buyer_id == Company.id).all()

It returns:

(<Company 2>, <Buyer 1, 2>)
disgra
  • 683
  • 3
  • 6
  • 18
  • 1
    [`Query.add_entity()`](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.add_entity) and [`Query.with_entities()`](http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.with_entities) can be used as well. – Ilja Everilä Jan 11 '18 at 14:03
  • 1
    I did similar to @peterpalace in my code. Strange that such a seemingly common thing is so hard to find in the documentation, although I guess it's implicit in the green box "What does Query select from if there’s multiple entities?" at https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#querying-with-joins. Is there an advantage to using `add_entity()` or `with_entities()`? – antgel Feb 22 '19 at 17:29
  • Is there an easy way to join this tuple of objects into one pydantic basemodel? – Tony Sep 01 '22 at 18:15