On Mon, Jan 21, 2019 at 10:17 AM Harshvardhan Gupta <[email protected]> wrote: > > 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 ?
There's an easy answer which is that you need to use the association object pattern: https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html?highlight=association%20object#association-object otherwise, if you were to load Corporate -> Corporate.corporate_applicants -> User, now you iterate: for corp in results: for user in corp.corporate_applicants: print("User name %s application type %s" % ( user.name, .... ? ? ? what goes here? User doesn't have a "type" field, so you can't say User.type ) ) It wouldn't make sense for the "type" attribute and all the other attributes of CorporateApplication to be shoved onto each User object. The same User object can be associated with many CorporateApplication rows per this schema design and there is only one User object per identity per Session. using normal association object pattern you instead get a proper namespace for your different entities: for corp in results: for applicant in corp.corporate_applicants: print("User name %s application type %s" % ( applicant.user.name, applicant.type ) ) > > > > 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]> 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. -- 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.
