mysql - Python - Sqlalchemy: How to get list of the records from many-to-many relationship -

i have db below:

+-----+     +------+     +-------+      +-------------+ |users|-----|emails|-----|assoc_t|------|other_t      | |     |1   m|      |1   m|       |m    1|             | |other|     |other |     |other  |      |types        | |data |     |data  |     |data   |      |other data   | +-----+     +------+     +-------+      +-------------+ 

short description: i have user, have many emails, , email has many-to-many relationship other_t

if have user object in sql alchemy:

user = usermodel.query.join(usermodel.emails).filter_by(email=id).first() 

how unique list of other_t of current user ?

i use below method, doesn't right though works. (too many nested loop , queries db) *unless tell me has done way...

class user(object):     __dbmodel = usermodel()      @property     def email(self):         return self.__dbmodel.emails if self.__dbmodel else none      def __init__(self, id=none):         if helpers.is_email(str(id)):             self.__dbmodel = usermodel.query.join(usermodel.emails).filter_by(email=id).first() if id else usermodel()         elif helpers.is_number(str(id)):             pass      # question here: how list of othert record based on usermodel defined in __init__?      def get_othert(self, email=none, other_types=none):         # list of email user have         emails = []         if not email:             emails = self.email.all()         else:             if helpers.is_email(str(email)):                 emails.append(user.email.filter_by(email=email).first())             else:                 return false          # list of other_t_id in assoc table         o_t_id = []         e in emails:             assoc_other_t = e.emailassociations             assoc in assoc_other_t:                 if assoc.other_t_id not in o_t_id:                     o_t_id .append(assoc.other_t_id)          # now, after have list of other_t id, actual other_t         ret = []         o in o_t_id :             ret.append(other_tmodel.query.filter_by(other_t_id=o, types=other_types).first()          return ret 

here sql alchemy model:

usermodel

class usermodel(db.model):     __tablename__ = "users"      userid = db.column(db.integer, primary_key=true, autoincrement=true)     firstname = db.column(db.string(255), nullable=false)     lastname = db.column(db.string(255), nullable=false)      # relationships     emails = db.relationship('emailmodel', backref='user', lazy='dynamic')      @orm.reconstructor     def init_on_load(self):         pass      def __init__(self):         pass 

emailmodel

class emailmodel(db.model):     __tablename__ = "emails"      email = db.column(db.string(255), unique=true, primary_key=true, nullable=false)     userid = db.column(db.integer, db.foreignkey('users.userid'), nullable=false)      # relationships     emailassociations = db.relationship("assoc_tmodel", back_populates="emails")      @orm.reconstructor     def init_on_load(self):         pass      def __init__(self):         pass 

assoc_tmodel

class assoc_tmodel(db.model):     __tablename__ = 'assoc_t'      other_t_id = db.column(         db.integer, db.foreignkey('other_t.other_t_id'),         primary_key=true, nullable=false     )     email = db.column(         db.string(255), db.foreignkey('emails.email'),         primary_key=true, nullable=false     )     emailverified = db.column(db.boolean, nullable=false, server_default='0')      # relationships     emails = db.relationship("emailmodel", back_populates="emailassociations")     other_ts = db.relationship("other_tmodel", back_populates="other_tassociations")      @orm.reconstructor     def init_on_load(self):         pass      def __init__(self):         pass 

other_tmodel

class other_tmodel(db.model):     __tablename__ = "other_t"      other_t_id = db.column(db.integer, nullable=false, unique=true, primary_key=true, autoincrement=true)     other_t_type = db.column(db.enum('one', 'two', 'three'), nullable=false, server_default='one')     other_data= db.column(db.string(255), nullable=false)      # relationships     other_tassociations= db.relationship("assoc_tmodel", back_populates="other_ts")     @orm.reconstructor     def init_on_load(self):         pass      def __init__(self):         pass 

thanks!

your implementation results in over-fetching data don't need.

emails = self.email.all() load email entities memory.

assoc_other_t = e.emailassociations trigger additional sql query each emailassociations impact performance.

you can use subquery avoid intermediate fetches.

class user(object):      def get_othert(self, email=none, other_types=none):         if email , not helpers.is_email(email):             # i'd recommend exception here             return false         # assoc_tmodel subquery         # select other_t_id other_t_id         assoc_sq = session.query(assoc_tmodel.other_t_id)          # handle email predicate         if email:             # specific email             assoc_sq = assoc_sq.filter(assoc_tmodel.email == email)         else:             # emails associated current user             email_sq = session.query(emailmodel.email).\                 filter(emailmodel.userid == self.__dbmodel.userid)             assoc_sq = assoc_sq.filter(assoc_tmodel.email.in_(email_sq))          # fetch other_tmodel          q = session.query(other_tmodel).\             filter(other_tmodel.other_t_id.in_(assoc_sq))          if other_types:             # unclear `other_types` list?             q = q.filter(other_tmodel.other_types.in_(other_types))             # or scalar value?             q = q.filter(other_tmodel.other_types == other_types)          return q.all()