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

User · Answer

Try this  q   Session query           User  Document  DocumentPermissions        filter           User email    Document author        filter           Document name    DocumentPermissions document        filter          User email     someemail         all

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[python] sqlalchemy: how to join several tables by one query?

Examples related to python

Examples related to sql

Examples related to join

Examples related to sqlalchemy