Thanks Simon, it worked! I did the whole thing just because I had an sql select query talking 1.5 seconds to complete (several joins and many properties, just 1000 records), and this change reduced it to 1.1 seconds (I guess I'll have to find a better way to improve performance)
-----Original Message----- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Simon King Sent: Thursday, September 04, 2014 11:14 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] selecting from a relationship 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('i > d', '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('i > d', '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 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.
