sqlalchemy: how to join several tables by one query?

Question

I have the following SQLAlchemy mapped classes:

class User(Base):
    __tablename__ = 'users'
    email = Column(String, primary_key=True)
    name = Column(String)

class Document(Base):
    __tablename__ = "documents"
    name = Column(String, primary_key=True)
    author = Column(String, ForeignKey("users.email"))

class DocumentsPermissions(Base):
    __tablename__ = "documents_permissions"
    readAllowed = Column(Boolean)
    writeAllowed = Column(Boolean)

    document = Column(String, ForeignKey("documents.name"))

I need to get a table like this for user.email = "user@email.com":

email | name | document_name | document_readAllowed | document_writeAllowed

How can it be made using one query request for SQLAlchemy? The code below does not work for me:

result = session.query(User, Document, DocumentPermission).filter_by(email = "user@email.com").all()

Thanks,

I've found that the following works to join two tables: `result = session.query(User, Document).select_from(join(User, Document)).filter(User.email=='user@email.com').all()` But I have not managed yet how to make work the similar for three tables (to include DocumentPermissions). Any Idea? — barankin, May 18 '11 at 12:37
When I perform similar task, I get SyntaxError: keyword can't be an expression — Ishan Tomar, Aug 25 '20 at 12:40

score 141 · Answer 1 · edited Apr 08 '20 at 09:57

141

Try this

q = Session.query(
         User, Document, DocumentPermissions,
    ).filter(
         User.email == Document.author,
    ).filter(
         Document.name == DocumentPermissions.document,
    ).filter(
        User.email == 'someemail',
    ).all()

edited Apr 08 '20 at 09:57

Ryabchenko Alexander

10,057
7
56
88

answered May 18 '11 at 13:04

Abdul Kader

5,781
4
22
40

16

What kind of `join` does it do? inner, outer, cross or what? – Nawaz Dec 06 '13 at 06:59
16

This actually doesn't do a join at all, it returns row objects in a tuple. In this case, it'd return `[(, , ),...]`/ – Fake Name May 10 '15 at 20:28
72

"doesn't do a join at all" - that's a little misleading. It will have sql like `select x from a, b ,c` which is a cross join. The filters then make it an inner join. – Aidan Kane Sep 12 '16 at 13:04
8

You can print the query by leaving off the `.all()`. So `print Session.query....` to see exactly what it is doing. – boatcoder Oct 20 '17 at 14:43
1

@FakeName how to print the result from the returned tuple. – mk_ Aug 14 '18 at 02:27
6

I am new to SQLAlchemy. I noticed `.filter()` can receive multiple criteria if comma separated. Is it preferable to use one `.filter()` with comma separations inside the parenthesis, or use multiple `.filter()` like the above answer? – Intrastellar Explorer Apr 18 '19 at 22:27
1

@IntrastellarExplorer use a single `.filter` if you wish to join using the implicit `and_` SQLAlchemy filter qualifier (represents "where .. and .." in SQL). It is identical in SQL terms (unless I am mistaken) as tagging on a sequence of .filter functions to the overall expression. – CodeMantle Mar 17 '20 at 08:16
Coming from a strong SQL background, this just bothers me. When I see `filter`, I associate that with a `WHERE` clause, and within standard SQL you don't use `WHERE` to specify your joins. The SQLAlchemy docs themselves also conflate the two. – trpt4him Oct 31 '21 at 12:35
@AidanKane `select x from a, b ,c` your comment is also a bit misleading, since there is no difference between a cross join and an inner join when using the where clause. – Lucas Alonso Jul 26 '23 at 12:29

score 100 · Answer 2 · edited Oct 24 '18 at 12:57

As @letitbee said, its best practice to assign primary keys to tables and properly define the relationships to allow for proper ORM querying. That being said...

If you're interested in writing a query along the lines of:

SELECT
    user.email,
    user.name,
    document.name,
    documents_permissions.readAllowed,
    documents_permissions.writeAllowed
FROM
    user, document, documents_permissions
WHERE
    user.email = "user@email.com";

Then you should go for something like:

session.query(
    User, 
    Document, 
    DocumentsPermissions
).filter(
    User.email == Document.author
).filter(
    Document.name == DocumentsPermissions.document
).filter(
    User.email == "user@email.com"
).all()

If instead, you want to do something like:

SELECT 'all the columns'
FROM user
JOIN document ON document.author_id = user.id AND document.author == User.email
JOIN document_permissions ON document_permissions.document_id = document.id AND document_permissions.document = document.name

Then you should do something along the lines of:

session.query(
    User
).join(
    Document
).join(
    DocumentsPermissions
).filter(
    User.email == "user@email.com"
).all()

One note about that...

query.join(Address, User.id==Address.user_id) # explicit condition
query.join(User.addresses)                    # specify relationship from left to right
query.join(Address, User.addresses)           # same, with explicit target
query.join('addresses')                       # same, using a string

For more information, visit the docs.

DO THIS. See - not a lot of stuff specified in the joins. That's because if the tables/db-model has already been setup-up with proper foreign keys between these tables - SQLAlchemy will take care of joining ON the proper columns for you. — Brad, Jan 13 '19 at 15:11
This is the most correct answer and proper use of sqlalchemy. However, I had to use the filter solution because SQLAlchemy does not accept delete statements with join() — tbarbot, Nov 08 '21 at 08:33

score 65 · Answer 3 · answered Jun 03 '11 at 11:50

65

A good style would be to setup some relations and a primary key for permissions (actually, usually it is good style to setup integer primary keys for everything, but whatever):

class User(Base):
    __tablename__ = 'users'
    email = Column(String, primary_key=True)
    name = Column(String)

class Document(Base):
    __tablename__ = "documents"
    name = Column(String, primary_key=True)
    author_email = Column(String, ForeignKey("users.email"))
    author = relation(User, backref='documents')

class DocumentsPermissions(Base):
    __tablename__ = "documents_permissions"
    id = Column(Integer, primary_key=True)
    readAllowed = Column(Boolean)
    writeAllowed = Column(Boolean)
    document_name = Column(String, ForeignKey("documents.name"))
    document = relation(Document, backref = 'permissions')

Then do a simple query with joins:

query = session.query(User, Document, DocumentsPermissions).join(Document).join(DocumentsPermissions)

answered Jun 03 '11 at 11:50

thule

4,034
21
31

1

What is `query` set to in the last line and how do you access the joined records in it? – Petrus Theron Jan 16 '14 at 18:40
@pate I'm not sure what you mean by 'what is query set to', but it will join according the relations, and the yield 3-tuples. The arguments to the query() call are essentially the select list in sqlalchemy. – thule Jan 17 '14 at 18:53
How do I access the `Document` (2nd tuple value) in the query result set? – Petrus Theron Oct 14 '16 at 11:04
1

@PetrusTheron Like I said, query will yield 3-tuples. You can index elements, or just unpack: `for (user, doc, perm) in query: print "Document: %s" % doc` – thule Oct 15 '16 at 16:39
what is `'permissions'` in `document = relation(Document, backref = 'permissions')` ? – Cabrera Jun 05 '18 at 04:07
1

Whoa, blast from the past. 'permissions' is an attribute of Document object, that gives you set of DocumentPermission objects. Hence backref. – thule Jun 05 '18 at 18:36
is there a possibility to use the ORM-relations more effectively, so that additional join-terms do not have to be explicitly declared? i.e. `Document`, should already know how to join a user by its `author` attribute, so I would guess that `session.query(Document.name, Document.author.email)` should in principle be able to resolve; but to me it doesn't :( – mzoll Nov 11 '19 at 16:45
@mzolI think there is now! Have not worked with sqlalchemy for a bit, but apparently in new versions you can do something like `session.query(Document).join(Document.author)`. Looks a lot less derpy, I must admit. Source: https://www.pythoncentral.io/sqlalchemy-orm-examples/ – thule Nov 12 '19 at 20:01
I'm confused as to why you have to write `.join(Document)` and `.join(DocumentsPermissions)` but don't need to specify User with `.join(User)` or similar. What defines User to be the base of this query, that the other tables join to? Is it just that User is the first argument to `query()`? Does it need to be the FIRST argument if so? – user2428107 Mar 03 '21 at 11:14
@user2428107 Yes, and yes. It makes some degree of sense when you think of it -- the resulting sql query is `SELECT ... from users join documents join documents_permissions ...` – thule Mar 04 '21 at 23:06
Thanks @letitbee. On further reading of docs it looks like there is a `.select_from()` function that can be used to specify what the base table for the query is, but it's optional and the default is the leftmost query argument. So if I understand correctly a `.select_from(User)` could have been chained into this call without in this case changing the result. Confusingly when I tried my own code I was able to change the order of query arguments without this and it still all worked! Maybe depending on the foreign key relationships, it is sometimes just unambiguous what the join ought to be. – user2428107 Mar 05 '21 at 07:17

Matt-Heun Hong · Answer 4 · 2017-09-08T20:24:55.973

12

Expanding on Abdul's answer, you can obtain a KeyedTuple instead of a discrete collection of rows by joining the columns:

q = Session.query(*User.__table__.columns + Document.__table__.columns).\
        select_from(User).\
        join(Document, User.email == Document.author).\
        filter(User.email == 'someemail').all()

edited Sep 08 '17 at 20:24

answered Sep 08 '17 at 19:15

Matt-Heun Hong

408
3
13

1

This works. However, they column names are missing when serializing the object. – Lucas Nov 14 '18 at 22:46

Valery Ramusik · Answer 5 · 2020-09-26T21:38:32.663

This function will produce required table as list of tuples.

def get_documents_by_user_email(email):
    query = session.query(
       User.email, 
       User.name, 
       Document.name, 
       DocumentsPermissions.readAllowed, 
       DocumentsPermissions.writeAllowed,
    )
    join_query = query.join(Document).join(DocumentsPermissions)

    return join_query.filter(User.email == email).all()

user_docs = get_documents_by_user_email(email)

sqlalchemy: how to join several tables by one query?

5 Answers5

Linked

Related