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 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.
