On Thu, Sep 4, 2014 at 3:28 PM, Ofir Herzas <herz...@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
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.
