On Sun, Jan 20, 2019 at 7:50 AM Harshvardhan Gupta <[email protected]> wrote: > > I have two tables, Corporates and Users , each corporate can be reviewing > multiple users, and each user can apply to multiple corporates. > > My models are defined as: > > class Corporate(Dictifiable, db.Model): > > > > __tablename__ = 'corporate' > > > > > id = Column(Integer, Sequence('corporate_id_seq'), primary_key=True) > > name = Column(String(1024), nullable=False) > > > > > admins = db.relationship('User', secondary='corporate_admin') > > tests = db.relationship('Test', secondary='corporate_test') > > applicants = db.relationship('User', secondary='corporate_applicants') > > > > > > > > class User(Dictifiable, db.Model, UserMixin): > > > __tablename__ = 'user' > > > > > applicant_for = db.relationship('Corporate', > > secondary='corporate_applicants') > > > My many to many table is defined as: > > class CorporateApplicants(Dictifiable, db.Model): > > > > __tablename__ = 'corporate_applicants' > > > > > user_id = Column(Integer, ForeignKey('user.id'), primary_key=True) > > corporate_id = Column(Integer, ForeignKey('corporate.id'), > > primary_key=True) > > > > > # i want to access this > > type = Column(db.Enum(ApplicationStatusTypes), nullable=True) > > > My queries are usually done like this: > > Corporate.query .join(Corporate.applicants)# SQLAlchemy magic will make it > join through the many to many > .options(contains_eager(Corporate.applications)) .all() > > But this way I am unable to access the type field (for each user, corporate > combination it will be either "shortlist","hired","reject". > > It is not possible to run a loop and then fetch each value separately due to > the high number of applicants. > > How can I access this extra column while querying a many to many relationship > in an eager manner.
there's still magic that will help you but as always, explicit approach will get you there, you need to name the entity you want to query: sess.query(Corporate).join(CorporateApplicants).join(User).filter(CorporateApplications.foo == 'bar') if those joins fail that there is too much ambiguity, or just as a better practice in general, build some extra relationships so you can use them: sess.query(Corporate).join(Corporate.applicant_associations).join(CorporateApplicant.user).filter(CorporateApplications.foo == 'bar') > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
