9

I need to find the equivalent of this query in sqlalchemy.

SELECT u.user_id, u.user_name, c.country FROM
table_user u , table_country c WHERE u.user_email = 'abc@def.com'

i tried this below code:

session.query(User).join(Country.country).filter(User.user_email == 'abc@def.com').first()

and this gave me below error :

  AttributeError: 'ColumnProperty' object has no attribute 'mapper'

can anyone give an example of join query with tables mapped to new class objects ?

Daniel Roseman
  • 588,541
  • 66
  • 880
  • 895
skoovill
  • 1,069
  • 2
  • 12
  • 22

1 Answers1

20

Try this, assuming your User mapper has a relationship to Country configured.

user, country = session.query(User, Country.country).join(Country).filter(User.user_email == 'abc@def.com').first()
jd.
  • 10,678
  • 3
  • 46
  • 55
  • 3
    i need it to only have one object which has all the columns of both the tables. – skoovill Jul 05 '11 at 12:51
  • 7
    Then it's a mapping problem rather than a querying one. The docs for what you want: http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables – jd. Jul 05 '11 at 13:00
  • Is this answer works without defining a relationship? – JavaSa Dec 27 '16 at 12:23
  • @JavaSa No. The relationship is what defines what you can join. To simplify: ``class User(db.Model): MyColumn = db.Column(db.String(100))`` and ``class Country(db.Model): AnotherColumn = db.Column(db.String(100))`` does not have any relationship defined. If you were to do ``user, country = db.session.query(User, Country.AnotherColumn).join(Country).first()`` you would get: ``sqlalchemy.exc.InvalidRequestError: Could not find a FROM clause to join from. Tried joining to , but got: Can't find any foreign key relationships between 'User' and 'Country'.`` – Juha Untinen Sep 11 '17 at 07:28
  • You might be able to do this though: ``user, country = db.session.query(User, Country.AnotherColumn).join(Country, Country.ID==User.ID).first()``. Running that would generate a query like this: ``[SQL: 'SELECT "User"."ID" AS "User_ID", "User"."MyColumn" AS "User_MyColumn", "Country"."AnotherColumn" AS "Country_AnotherColumn" \nFROM "User" JOIN "Country" ON "Country"."ID" = "User"."ID"\n LIMIT ? OFFSET ?'] [parameters: (1, 0)]`` you will of course need to change the criteria ``Country.ID==User.ID`` to something that makes sense :) – Juha Untinen Sep 11 '17 at 07:31