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 < <mailto:herz...@gmail.com> 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-loadi ng-with-multiple-entities> http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#deferred-loadin g-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(' <http://t_employee.id/> t_employee.id', > ondelete="CASCADE"), nullable=False) > job_id = sa.Column(sa.BigInteger, sa.ForeignKey(' <http://t_job.id/> 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-colum n-loading> 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 <mailto:sqlalchemy+unsubscr...@googlegroups.com> sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com. Visit this group at <http://groups.google.com/group/sqlalchemy> http://groups.google.com/group/sqlalchemy. For more options, visit <https://groups.google.com/d/optout> 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> https://groups.google.com/d/topic/sqlalchemy/OprfrGJcoJU/unsubscribe. To unsubscribe from this group and all its topics, send an email to <mailto:sqlalchemy+unsubscr...@googlegroups.com> sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com. Visit this group at <http://groups.google.com/group/sqlalchemy> http://groups.google.com/group/sqlalchemy. For more options, visit <https://groups.google.com/d/optout> 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 <mailto:sqlalchemy+unsubscr...@googlegroups.com> sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to <mailto:sqlalchemy@googlegroups.com> sqlalchemy@googlegroups.com. Visit this group at <http://groups.google.com/group/sqlalchemy> http://groups.google.com/group/sqlalchemy. For more options, visit <https://groups.google.com/d/optout> 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.
