I haven't run my query, no. If that form doesn't work, try this instead:
session.query(User,
Address).outerjoin(User.addresses).filter(Address.city==city)
"Caching" is a property of the session, but it can be affected by the
transaction. By default, Session.expire_on_commit is True, so all
cached relationships are expired when you commit.
Simon
On Fri, May 24, 2019 at 12:18 PM Manoj Mokashi <[email protected]> wrote:
>
> HI Simon,
> thanks or your input.
> Yes, that was one question i forgot to add : how to prevent caching of
> addresses in this case.
> From my experiment, one can use session.commit/rollback/expire_all or
> expire(entity) specifically.
> That might mean caching is related to the transactions and not session ?
> Have you run your query ? I thought a condition was not allowed for
> relations, i.e should be outerjoin Address rather than User.addresses.
>
>
> On Fri, May 24, 2019 at 4:26 PM Simon King <[email protected]> wrote:
>>
>> (Below is just my opinion, feel free to ignore it)
>>
>> I think this example goes against some of the philosophy of
>> SQLAlchemy, in that "User.addresses" is not really intended to be
>> filtered. Since a given user can only exist once in a session, if you
>> load that user with a filtered "addresses" property, any subsequent
>> code that uses the same session to load that user from the database
>> will also get that filtered view, whether they wanted it or not.
>>
>> Also, I'm not sure what happens if the user has *already* been loaded
>> into the session; I don't know if your query will overwrite the
>> "user.addresses" property, or if it will leave it untouched.
>>
>> Instead, I think you should probably use something like:
>>
>> def get_city_addresses(session, city):
>> q = session.query(User, Address).outerjoin(User.addresses,
>> Address.city==city).order_by(User.id)
>> for user, addresses in itertools.groupby(q, key=lambda (u, a): u):
>> yield user, list(addresses)
>>
>> Simon
>>
>> On Fri, May 24, 2019 at 11:17 AM Manoj Mokashi <[email protected]>
>> wrote:
>> >
>> > About DRY :
>> > If i define a join condition in a relationship, but still have to define
>> > it again in the queries, it seems to go against DRY.
>> > If the condition changes, i would have to change it everywhere. I suppose
>> > we could have method like joinAddress(query) to do it in a single place.
>> >
>> > Consider getting only Mumbai city addresses of all users.
>> > qry_users = session.query(User).outerjoin(User.addresses,
>> > Address.city=='Mumbai').options(contains_eager(User.addresses)).all()
>> > Currently, the above is not allowed, i.e. specify additional condition for
>> > a relation in join.
>> > So we have to user Address entity and add the join condition again.
>> > qry_users = session.query(User).outerjoin(Address, (Address.user_id ==
>> > User.id) &
>> > (Address.city=='Mumbai')).options(contains_eager(User.addresses)).all()
>> >
>> > If in this case, it were allowed to add the condition to the relation, it
>> > would be nice.
>> >
>> > But it still forces me to use a join, whereas my relation could be set to
>> > load differently.
>> > I know query api is pretty complex and flexible, and i am new to
>> > sqlalchemy, so i probably don't understand many things well.
>> >
>> > I was thinking that if we had a options(filterrelated(User.addresses,
>> > Address.city=='Mumbai')), it would fetch Users,
>> > and for each User, it would set this filter condition in User.addresses.
>> > Later, when the addresses relation is to be loaded by whatever way, it
>> > would see this filter and apply it.
>> > My usage then becomes :
>> > qry_users = session.query(User).options(filterrelated(User.addresses,
>> > Address.city=='Mumbai')).all()
>> >
>> >
>> >
>> > On Thu, May 23, 2019 at 11:46 PM Mike Bayer <[email protected]>
>> > wrote:
>> >>
>> >>
>> >>
>> >> On Thu, May 23, 2019, at 8:04 AM, Manoj Mokashi wrote:
>> >>
>> >> >>Was aiming for DRY( don't repeat yourself) for the relation : once i
>> >> >>declare the relation, i should not have to join to it again, to filter
>> >> >>it.
>> >>
>> >>
>> >> DRY is about unnecessary repetition that makes it difficult to change
>> >> parts of the code later, because certain elements are repeated in many
>> >> places. That's not the case here as there are two distinct API
>> >> operations being performed on the object.
>> >>
>> >>
>> >>
>> >> To be clearer,
>> >> The relationship could be loaded lazy or selectin, not necessarily a join.
>> >> It should load as defined, but with the filter applied.
>> >>
>> >>
>> >> filters don't apply to lazy or selectin loads or any other kind of load
>> >> other than contains_eager() which is against the Query as it stands, so
>> >> I'm not sure what you mean.
>> >>
>> >>
>> >>
>> >> regards,
>> >> manoj
>> >>
>> >> On Thu, May 23, 2019 at 10:41 AM Manoj Mokashi <[email protected]>
>> >> wrote:
>> >>
>> >> Hi Michael,
>> >>
>> >> Thanks for the quick reply as usual.
>> >> You have understood the use case correctly.
>> >> Was aiming for DRY( don't repeat yourself) for the relation : once i
>> >> declare the relation, i should not have to join to it again, to filter it.
>> >> The transform looks useful.
>> >>
>> >> Another thing : Is it possible to specify which relation columns to fetch
>> >> when i declare the relation ?
>> >>
>> >> regards,
>> >> manoj
>> >>
>> >> On Wed, May 22, 2019 at 12:05 PM Mike Bayer <[email protected]>
>> >> wrote:
>> >>
>> >> On Wed, May 22, 2019 at 1:02 AM Manoj Mokashi <[email protected]>
>> >> wrote:
>> >> >
>> >> > Hi Michael,
>> >> >
>> >> > It would be nice to have something like options(filterrelated(relation,
>> >> > filter))
>> >> > That way, since the join is already specified in the relation, we don't
>> >> > have to add it again.
>> >> > Or is that difficult due to lazyloading ?
>> >>
>> >> hi there -
>> >>
>> >> you're responding to a message that is 11 years old. Can you start
>> >> from scratch and specify what you are looking for?
>> >>
>> >> "filterelated" as you describe would appear to correspond to:
>> >>
>> >>
>> >> query(Thing).outerjoin(Thing.relationship).filter(Thing.something ==
>> >> 'bar').options(contains_eager(Thing.relationship))
>> >>
>> >> that is, you are asking for three separate things, so there's three
>> >> separate methods to call. I don't see it as straightforward from an
>> >> API perspective to add various shortcut functions that perform these
>> >> tasks in a different way since this spreads confusion.
>> >>
>> >> If you'd like you make your own "filterrelated" function, do this:
>> >>
>> >> def filterrelated(relation, filter):
>> >> def transform(q):
>> >> return
>> >> q.outerjoin(relation).filter(filter).options(contains_eager(relation))
>> >> return transform
>> >>
>> >>
>> >> then you use it like this:
>> >>
>> >> my_query = my_query.with_transformation(filterrelated(relation, filter))
>> >>
>> >> that way you can have the funciton you want and SQLAlchemy's official
>> >> API doesn't have to have a confusing array of functions that all do
>> >> similar yet different things.
>> >>
>> >>
>> >>
>> >>
>> >> >
>> >> > Btw, when we use contains_eager() with add_entity(), it creates two
>> >> > entities in the output instead of just the main one.
>> >> > Is that expected ?
>> >> >
>> >> > regards,
>> >> > manoj
>> >> >
>> >> > On Thursday, March 20, 2008 at 7:53:57 PM UTC+5:30, Michael Bayer wrote:
>> >> >>
>> >> >>
>> >> >> On Mar 19, 2008, at 8:31 PM, Fotinakis wrote:
>> >> >>
>> >> >> >
>> >> >> >
>> >> >> > SELECT *
>> >> >> > FROM users
>> >> >> > LEFT OUTER JOIN
>> >> >> > ( SELECT * FROM addresses WHERE type = 1 )
>> >> >> > AS addresses ON users.id = addresses.uid
>> >> >> >
>> >> >> > I can do this:
>> >> >> >
>> >> >> > query =
>> >> >> > session
>> >> >> > .query
>> >> >> > (User
>> >> >> > ).add_entity
>> >> >> > (Address).outerjoin(addresses).filter(Address.type=='home')
>> >> >> >
>> >> >> > But, that filters on the entire query, not just on the joined sub-
>> >> >> > query, generating something like this:
>> >> >> >
>> >> >> > SELECT *
>> >> >> > FROM users
>> >> >> > LEFT OUTER JOIN addresses ON users.id = addresses.uid
>> >> >> > WHERE addresses.type = 1
>> >> >> > ORDER BY hosts.mac
>> >> >> >
>> >> >> > Because it's a one-to-many relationship, this query only returns the
>> >> >> > users that have a home addresses ... and _excludes_ users totally who
>> >> >> > have an address, but one that is not of type 'home'. I need it to
>> >> >> > return all users regardless (hence the LEFT JOIN) and just join
>> >> >> > addresses of type 1.
>> >> >> >
>> >> >>
>> >> >> you'd probably want to put the criterion in the ON clause:
>> >> >>
>> >> >>
>> >> >> session
>> >> >> .query
>> >> >> (User).add_entity(Address).select_from(users.outerjoin(addresses,
>> >> >> and_(Address.type=='home', Address.user_id=User.id)))
>> >> >>
>> >> >> alternatively you can shove the actual subquery in there in a few
>> >> >> ways, one of them is like this:
>> >> >>
>> >> >> sel = addresses.select().where(Address.type=='home')
>> >> >> session.query(User).add_entity(Address).outerjoin(('addresses',
>> >> >> sel))
>> >> >>
>> >> >> or otherwise spell out the join to the subquery using select_from()
>> >> >> again.
>> >> >>
>> >> >>
>> >> > --
>> >> > 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 [email protected].
>> >> > To post to this group, send email to [email protected].
>> >> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> >> > To view this discussion on the web visit
>> >> > https://groups.google.com/d/msgid/sqlalchemy/f10335a2-9b95-4f1c-881b-03202d697121%40googlegroups.com.
>> >> > For more options, visit https://groups.google.com/d/optout.
>> >>
>> >> --
>> >> 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 a topic in the
>> >> Google Groups "sqlalchemy" group.
>> >> To unsubscribe from this topic, visit
>> >> https://groups.google.com/d/topic/sqlalchemy/2r-CzHOnGx4/unsubscribe.
>> >> To unsubscribe from this group and all its topics, send an email to
>> >> [email protected].
>> >> To post to this group, send email to [email protected].
>> >> Visit this group at https://groups.google.com/group/sqlalchemy.
>> >> To view this discussion on the web visit
>> >> https://groups.google.com/d/msgid/sqlalchemy/CA%2BRjkXFmGu8vUR4%3D88vP%2BZ%3Dvi-bgOjPwCgx6r912XX_MF6-VOQ%40mail.gmail.com.
>> >> For more options, visit https://groups.google.com/d/optout.
>> >>
>> >>
>> >> --
>> >> 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 [email protected].
>> >> To post to this group, send email to [email protected].
>> >> Visit this group at https://groups.google.com/group/sqlalchemy.
>> >> To view this discussion on the web visit
>> >> https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2B8WZoFGF_8T7OnakiQm6F83zUY%2BwGFvURjjFmCzX-XQA%40mail.gmail.com.
>> >> For more options, visit https://groups.google.com/d/optout.
>> >>
>> >>
>> >> --
>> >> 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 a topic in the
>> >> Google Groups "sqlalchemy" group.
>> >> To unsubscribe from this topic, visit
>> >> https://groups.google.com/d/topic/sqlalchemy/2r-CzHOnGx4/unsubscribe.
>> >> To unsubscribe from this group and all its topics, send an email to
>> >> [email protected].
>> >> To post to this group, send email to [email protected].
>> >> Visit this group at https://groups.google.com/group/sqlalchemy.
>> >> To view this discussion on the web visit
>> >> https://groups.google.com/d/msgid/sqlalchemy/b20d6045-5a6d-4bbf-ab50-2e203a0d6950%40www.fastmail.com.
>> >> For more options, visit https://groups.google.com/d/optout.
>> >
>> > --
>> > 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 [email protected].
>> > To post to this group, send email to [email protected].
>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>> > To view this discussion on the web visit
>> > https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2B5XcftoxL5zOGUZFf2Vntso_oMX58rj6HXjgY4LQcQKw%40mail.gmail.com.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>> --
>> 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 a topic in the
>> Google Groups "sqlalchemy" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/sqlalchemy/2r-CzHOnGx4/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/CAFHwexefniB1%2B3_DWzyjrm8B%2Biwcb64PJZVzft8oTH%3DjLy8sDg%40mail.gmail.com.
>> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CALbcBG%2BZa%2BednRBT_R2Jn3SqoVPGJGgf1%3DwD7v%2BczTL3u%2BZBzw%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcfaCV8DL4Fa1pVu5zkObAQP4YHruiS%2Bco4ArnETyaC%3DQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
