You need to join along the actual relationships between your classes. You've got this:
Employee._jobs -> EmployeeJob.? -> Job (I assume EmployeeJob has a "job" relationship to Job.) I think you probably want something like this: (session.query(Employee) .options(load_only('id', 'first_name'), joinedload("_jobs").joinedload("job")) ).all() Note that load_only is a separate option. The way you had it: joinedload(Employee._jobs).load_only('id', 'first_name') ...would be looking for 'id' and 'first_name' columns on the EmployeeJob object. There are lots of examples at: http://docs.sqlalchemy.org/en/latest/orm/loading.html Hope that helps, Simon On 4 Sep 2014, at 19:17, Ofir Herzas <herz...@gmail.com> wrote: > Nevertheless, is there a way to achieve what I want? (which is to selectively > load several columns and this 'jobs' property from Employee) > > Thanks, > Ofir > > > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On > Behalf Of Michael Bayer > Sent: Thursday, September 04, 2014 8:42 PM > To: sqlalchemy@googlegroups.com > Subject: Re: [sqlalchemy] selecting from a relationship > > > On Sep 4, 2014, at 1:32 PM, Ofir Herzas <herz...@gmail.com> wrote: > > > Thanks Michael, > I'm using 0.9.7 and while your example did work, the following did not: > > Ok that's not a relationship(). joinedload() only works with relationships. > > > > > > > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > > Base = declarative_base() > > class Employee(Base): > __tablename__ = 'employee' > > id = Column(Integer, primary_key=True) > first_name = Column(String) > _jobs = relationship("EmployeeJob", lazy="joined") > > @property > def jobs(self): > return [item.job_id for item in sorted(self._jobs, > key=attrgetter('id'))] > > class EmployeeJob(Base): > __tablename__ = "employee_job" > > id = Column(Integer, primary_key=True) > employee_id = Column(Integer, ForeignKey('employee.id')) > job_id = Column(Integer, ForeignKey('job.id')) > > class Job(Base): > __tablename__ = 'job' > > id = Column(Integer, primary_key=True) > name = Column(String) > > e = create_engine("sqlite://", echo=True) > Base.metadata.create_all(e) > > session = Session(e) > > session.query(Employee).options(joinedload(Employee.jobs).load_only('id', > 'first_name')).all() > > > Notice that the difference here is that I'm using a property decorator on > jobs, there is a link table (EmployeeJob), and I'm trying to load information > of Employee (first_name should be from there) > > Thanks, > Ofir > > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On > Behalf Of Michael Bayer > Sent: Thursday, September 04, 2014 8:00 PM > To: sqlalchemy@googlegroups.com > Subject: Re: [sqlalchemy] selecting from a relationship > > > On Sep 4, 2014, at 12:28 PM, Ofir Herzas <herz...@gmail.com> wrote: > > > > Thanks Simon, > I've tried the following: > > session.query(Employee).options(sa.orm.joinedload(Employee.jobs).load_only('id','first_name')).all() > > which according to the documentation > (http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loading-with-multiple-entities) > should work, but it throws an exception (ArgumentError: mapper option > expects string key or list of attributes) > > Can you please provide an example? > > check your SQLAlchemy version, I cannot reproduce that issue: > > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > > Base = declarative_base() > > class Employee(Base): > __tablename__ = 'employee' > > id = Column(Integer, primary_key=True) > jobs = relationship("Job") > > class Job(Base): > __tablename__ = 'job' > > id = Column(Integer, primary_key=True) > employee_id = Column(Integer, ForeignKey('employee.id')) > first_name = Column(String) > > e = create_engine("sqlite://", echo=True) > Base.metadata.create_all(e) > > session = Session(e) > > session.query(Employee).options(joinedload(Employee.jobs).load_only('id', > 'first_name')).all() > > output: > > SELECT employee.id AS employee_id, job_1.id AS job_1_id, job_1.first_name AS > job_1_first_name > FROM employee LEFT OUTER JOIN job AS job_1 ON employee.id = job_1.employee_id > > > > > > > Please notice that I'm trying to load only some properties of Employee > (including one relationship) while this behavior should not be the default > behavior (meaning that I don't want the defer the columns at model level) > > > On Thursday, September 4, 2014 5:59:21 PM UTC+3, Simon King wrote: > On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas <her...@gmail.com> wrote: > > Hi, > > I have a model similar to the following: > > > > class Employee(Base): > > __tablename__ = "t_employee" > > > > id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % __tablename__), > > primary_key=True, nullable=False) > > first_name = sa.Column(sa.String(30)) > > last_name = sa.Column(sa.String(30)) > > phone_number = sa.Column(sa.String(30)) > > > > _jobs = sa.orm.relationship("EmployeeJob", lazy="joined", cascade="all, > > delete, delete-orphan") > > > > @property > > def name(self): > > return self.first_name + (" " + self.last_name if > > len(self.last_name > > or "") > 0 else "") > > > > @property > > def jobs(self): > > return [item.job_id for item in sorted(self._jobs, > > key=attrgetter('id'))] > > > > @jobs.setter > > def jobs(self, value): > > self._jobs = [EmployeeJob(job_id=id_) for id_ in to_list(value)] > > > > class EmployeeJob(Base): > > id = sa.Column(sa.BigInteger, sa.Sequence('%s_id_seq' % __tablename__), > > primary_key=True, nullable=False) > > employee_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_employee.id', > > ondelete="CASCADE"), nullable=False) > > job_id = sa.Column(sa.BigInteger, sa.ForeignKey('t_job.id', > > ondelete="CASCADE"), nullable=False) > > > > > > Now, I'm trying to write a simple query that will fetch all employees with > > their jobs. > > As I understand, I need to use joinedload so that the list of jobs will be > > eagerly loaded but I can't understand how to do it. > > > > I tried the following: > > session.query(Employee.id).options(sa.orm.joinedload(Employee.jobs)) > > > > but it doesn't work. > > > > Just to clarify, I want to load some of the columns, not all of them, and > > I'm expecting to get the list of jobs for each employee (hopefully like the > > getter produces them) > > > > session.query(Employee) does fetch the required information but it selects > > some unneeded columns > > > > Also, how do I select the name property? > > > > Thanks, > > Ofir > > > > Eager loading means that when you have an instance of Employee, and > you access its 'jobs' property, no SQL is emitted because the data is > already available. This implies that you have to query for the > Employee class, not just one of its columns (otherwise you wouldn't > have an instance from which to access the "jobs" property) > > If you don't want to load all the Employee columns, you can defer them: > > > http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-column-loading > > > Hope that helps, > > Simon > > -- > 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 tosqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > -- > You received this message because you are subscribed to a topic in the Google > Groups "sqlalchemy" group. > To unsubscribe from this topic, > visithttps://groups.google.com/d/topic/sqlalchemy/OprfrGJcoJU/unsubscribe. > To unsubscribe from this group and all its topics, send an email > tosqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > -- > 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 tosqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > -- > You received this message because you are subscribed to a topic in the Google > Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/OprfrGJcoJU/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > -- > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.