sqlalchemy: how to join several tables by one query?

Try this

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

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.writeAllowedFROM    user, document, documents_permissionsWHERE    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 userJOIN document ON document.author_id = user.id AND document.author == User.emailJOIN 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 conditionquery.join(User.addresses)                    # specify relationship from left to rightquery.join(Address, User.addresses)           # same, with explicit targetquery.join('addresses')                       # same, using a string

For more information, visit the docs.

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)