Hi, thanks for the reply. >From what I understand , your example shows me filtering through a many to many relationship. However I was looking for explicitly also loading the extra field in the pivot table. In your example, will CorporateApplications.foo also be loaded in the result ?
On Monday, 21 January 2019 20:38:52 UTC+5:30, Mike Bayer wrote: > > On Sun, Jan 20, 2019 at 7:50 AM Harshvardhan Gupta > <[email protected] <javascript:>> 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] <javascript:>. > > To post to this group, send email to [email protected] > <javascript:>. > > 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.
