Filter, order by on fields of related objects

[Manoj Mokashi]

Firstly, thanks for making this software available.

My question is related to this issue, probably a common one for newbies :

https://stackoverflow.com/questions/16589208/attributeerror-while-querying-neither-instrumentedattribute-object-nor-compa

Consider the following entities :

class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    name = Column(String, unique = True)

class Bar(Base):
    __tablename__ = "bar"
    id = Column(Integer, primary_key = True)
    foo_id = Column(Integer, ForeignKey("foo.id"))

    foo = relationship("Foo", lazy="joined")

ses.query(Bar).filter(Bar.foo.name == "blah") throws
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Bar.foo 

has an attribute 'name'

( Note : i have edited the example somewhat )

From the answers, it seems that i cannot use Bar.foo.name, since foo is a relationship.

Also, i have to explicitly join Foo to the query in order to use its attributes in filter or order-by.

However, when i do this, and i have lazy="joined" in the relationship, 

the relation too creates a join (which cannot be used in the filter, orderby),

plus the join i have already added in the query.

My questions are related to this :

1. Why don't we allow the declared relations to be available in the query for filter/order-by etc ?

Does this not undermine the power of declarative relations ?

Can we declare a list of columns to be fetched for a relation ?

2. Given that we might have to add filter conditions to queries over time, what is the best-practice for loading

declared relations ? Should they have lazy="noload" or default(lazy-loading) 

and we use explicit joins in queries to avoid the duplicate joins ?

3. If i add a filter in the query for Foo without creating a join, it adds Foo in the FROM clause, 

but without any join condition.

Is this a bug ?

regards,

manoj

[Mike Bayer]

they are, you just need to name the entities explicitly in most cases:

query(Bar).join(Bar.foo).filter(Foo.name == 'blah').order_by(Foo.name)

you can use filter_by() which will resolve to the most recent join:

query(Bar).join(Bar.foo).filter_by(name == "blah")

>
> Does this not undermine the power of declarative relations ?

no? the relationship is working out a whole JOIN for you, even if
you are using aliases, mappings to SELECT statements, all kinds of
things. the complexity of relationship() is enormous.

>
> Can we declare a list of columns to be fetched for a relation ?

sure!

query(Bar, Foo.one, Foo.two).join(Bar.foo)

>
> 2. Given that we might have to add filter conditions to queries over time,
> what is the best-practice for loading
>
> declared relations ? Should they have lazy="noload" or default(lazy-loading)

never use "noload". Leave it at the default, and if you really
don't want those lazy loads to emit later, use lazy="raise", to catch
code that might be accessing it after the fact.

>
> and we use explicit joins in queries to avoid the duplicate joins ?

the duplicate joins are there for a reason - you might want to join in
one way for filtering the results, yet you still want to load
collections fully.

I think the main two sections that should help understand better are:

http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=raiseload#the-zen-of-joined-eager-loading

http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html?highlight=raiseload#routing-explicit-joins-statements-into-eagerly-loaded-collections

>
> 3. If i add a filter in the query for Foo without creating a join, it adds
> Foo in the FROM clause,
>
> but without any join condition.
>
> Is this a bug ?

not at all, you've added two FROM elements to the statement, and not
told it how/if to join them, it does what you told it. There could
be ten different FROM clauses, all joinable in any number of
combinations. Should the Query guess how you want to join these?




>
>
> regards,
>
> manoj
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Manoj Mokashi]

hi,

Thanks for the quick response.

So "Fetching related objects and filtering by related objects are independent operations, and one might filter by related objects

to narrow down the queried object using a join(inner), but still fetch all related objects(outer join) for each filtered object" is the take away for me. Maybe it could be highlighted in the docs ? ( Something similar is mentioned at the start of the zen link, but was not that clear to me at the outset)

Where related objects are one ( OTO, MTO), the duplicate queries seem to be an overhead, but it would be useful in the OTM, MTM cases.

About the columns to select for a relation, i meant in the relation def itself,

e.g. relation( "Foo", load_only="name" )

regards,

manoj

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/0QErBahCoJ4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.