load_only doesn't affect joins

[Ofir Herzas]

load_only as stated in http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html does the following:

"An arbitrary set of columns can be selected as “load only” columns, which will be loaded while deferring all other columns on a given entity..."

However, joined relationships are not affected by this, meaning that the produced sql still joins all 'joined' relationships

Consider the following example:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'
    id = sa.Column(sa.Integer, primary_key=True)
    bars = sa.orm.relationship("Bar", lazy="joined")
 
class Bar(Base):
    __tablename__ = 'bar'
    id = sa.Column(sa.Integer, primary_key=True)
    foo_id = sa.Column(sa.Integer, sa.ForeignKey('foo.id'))
    baz = sa.Column(sa.Integer)
 
e = sa.create_engine("sqlite://", echo=True)
session = sa.orm.Session(e)
session.query(Foo).options(sa.orm.load_only('id')).all()

The query produced is the following:

SELECT foo.id AS foo_id, bar_1.id AS bar_1_id, bar_1.foo_id AS bar_1_foo_id, bar_1.baz AS bar_1_baz
FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id = bar_1.foo_id

I understand that I can use the lazyload option to prevent the join but I thought it would be nice if the load_only handled it out-of-the-box...

[Mike Bayer]

On 12/03/2015 06:16 AM, Ofir Herzas wrote:
> load_only as stated
> in http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html does
> the following:
> "An arbitrary set of columns can be selected as “load only” columns,

> which will be loaded *while deferring all other columns* on a given
> entity..."


Hi Ofir -

As it says, "on a given entity", an entity refers to a class-oriented
object. A relationship refers to a different set of entities so these
must be referred to independently.

The "load_only" function in that code example is hyperlinked, which is
intended to encourage the reader to click and read through the more
detailed documentation for this function which is at:

http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_columns.html?highlight=load_only#sqlalchemy.orm.load_only

>
> However, joined relationships are not affected by this, meaning that the
> produced sql still joins all 'joined' relationships
>
> Consider the following example:
>
> |
> importsqlalchemy assa
> fromsqlalchemy.ext.declarative importdeclarative_base
>
> Base=declarative_base()
>
> classFoo(Base):
> __tablename__ ='foo'
> id =sa.Column(sa.Integer,primary_key=True)
> bars =sa.orm.relationship("Bar",lazy="joined")
>
> classBar(Base):
> __tablename__ ='bar'
> id =sa.Column(sa.Integer,primary_key=True)
> foo_id =sa.Column(sa.Integer,sa.ForeignKey('foo.id'))
> baz =sa.Column(sa.Integer)
>
> e =sa.create_engine("sqlite://",echo=True)
> session =sa.orm.Session(e)
> session.query(Foo).options(sa.orm.load_only('id')).all()
> |
>
> The query produced is the following:
> |
> SELECT foo.id AS foo_id,bar_1.id AS bar_1_id,bar_1.foo_id AS

> bar_1_foo_id,bar_1.baz AS bar_1_baz

> FROM foo LEFT OUTER JOIN bar AS bar_1 ON foo.id =bar_1.foo_id
> |
>
> I understand that I can use the lazyload option to prevent the join but
> I thought it would be nice if the load_only handled it out-of-the-box...

there's "out of the box" and there's "assuming a specific and arbitrary
intent" - you have lazy="joined" in your mapping so if you don't want
that to occur you need to tell the query to turn it off, load_only('a',
'b').lazyload('bars').





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

[Mike Bayer]

another thought, you'll probably like this:

load_only('a', 'b').lazyload('*')


that should be easier

[Ofir Herzas]

The lazy me you mean :)

I saw that option in the documents and my comment was not regarding the complexity of the solution.

I just thought that a 'load_only' method is better understood if it loads only the specified attributes.

With an abstraction layer, the user often "sees" things differently than the way they really are.

For example, when I load the Foo object and I get the bars list, I don't want to care about 'bars' being a relationship (I know it is but I already handled that when I defined my model). For me, it's an attribute of type list...

Of course some operations are relationship-specific but those that are not should not (IMO) force the user to consider them differently.

Since I have my own wrapper around session.query, I added the following lines to it:

query = session.query(model)

if properties is not None:
    props = [p for p in properties if p in model.__table__.c]
    query = query.options(sa.orm.load_only(*props))
   
    noload = set([x.key for x in model.__mapper__.relationships]) - set(props)
    for x in noload:
        query = query.options(sa.orm.lazyload(x))

...

That currently does what I want...

>> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[Jonathan Vanasco]

Thanks for posting a full self-contained working example of your problem!

[Ofir Herzas]

Thanks for caring ;)

On Dec 3, 2015 6:08 PM, "Jonathan Vanasco" <jona...@findmeon.com> wrote:

Thanks for posting a full self-contained working example of your problem!

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