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 <[email protected]> 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: [email protected] [mailto:[email protected]] On
> Behalf Of Michael Bayer
> Sent: Thursday, September 04, 2014 8:42 PM
> To: [email protected]
> Subject: Re: [sqlalchemy] selecting from a relationship
>
>
> On Sep 4, 2014, at 1:32 PM, Ofir Herzas <[email protected]> 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: [email protected] [mailto:[email protected]] On
> Behalf Of Michael Bayer
> Sent: Thursday, September 04, 2014 8:00 PM
> To: [email protected]
> Subject: Re: [sqlalchemy] selecting from a relationship
>
>
> On Sep 4, 2014, at 12:28 PM, Ofir Herzas <[email protected]> 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 <[email protected]> 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 [email protected].
> To post to this group, send email to [email protected].
> 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
> [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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
> [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
