selecting from a relationship

[Ofir Herzas]

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

[Simon King]

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

[Ofir Herzas]

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?

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)

[Jonathan Vanasco]

load_only indicates the columns in Employee.jobs that you want to load.

'first_name' is located on Employee, not EmployeeJob

[Michael Bayer]

On Sep 4, 2014, at 12:28 PM, Ofir Herzas <her...@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('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

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Ofir Herzas]

Thanks Michael,

I'm using 0.9.7 and while your example did work, the following did not:

 

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

--
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+...@googlegroups.com.

[Michael Bayer]

On Sep 4, 2014, at 1:32 PM, Ofir Herzas <her...@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.

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+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Ofir Herzas]

Nevertheless, is there a way to achieve what I want? (which is to selectively load several columns and this 'jobs' property from Employee)

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+...@googlegroups.com.

[Simon King]

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

> To unsubscribe from this group and stop receiving emails from it, send an email tosqlalchemy...@googlegroups.com.

> To post to this group, send email to sqlal...@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...@googlegroups.com.

> To post to this group, send email to sqlal...@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...@googlegroups.com.

> To post to this group, send email to sqlal...@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+...@googlegroups.com.
> To post to this group, send email to sqlal...@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+...@googlegroups.com.

[Ofir Herzas]

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: sqlal...@googlegroups.com [mailto:sqlal...@googlegroups.com] On
Behalf Of Simon King
Sent: Thursday, September 04, 2014 11:14 PM
To: sqlal...@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

[Jonathan Vanasco]

On Thursday, September 4, 2014 2:19:34 PM UTC-4, Ofir Herzas wrote:

Nevertheless, is there a way to achieve what I want? (which is to selectively load several columns and this 'jobs' property from Employee)

The ways I usually do that are:

1.  Select the other object.  Query for EmployeeJob, and then "load only" the Employee columns.  

2. Explicitly load columns.  I'm not sure if this will work for relationships:

      session.query( TableClass.column_a, TableClass.column_b).all()

    instead of returning an ORM object, you get a tuple.

3. Define a column as deferred in the mapper [ http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#deferred-column-loading]

Looking at your example though, unless you have very high traffic -- you're not going to have a significant performance gain with a query like that.  Your tables don't have many columns, and they're not very large.  People are usually concerned about load_only when you have very large fields (BLOBS, TEXT, HSTORE) or complex joins and need to save memory.

You might just be better off letting the ORM load stuff you don't need -- you'll get much more code done that way.

[Ofir Herzas]

Thanks Jonathan,
I actually have more properties and relationships which were removed for the sake of the example.
You are right though, the performance gain was not as I hoped it would be.

I'll have to think of other methods (caching maybe)...

--