>>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.
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.
regards,
manoj
On Thu, May 23, 2019 at 10:41 AM Manoj Mokashi <manojmoka...@gmail.com>
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 <mike...@zzzcomputing.com>
> wrote:
>
>> On Wed, May 22, 2019 at 1:02 AM Manoj Mokashi <manojmoka...@gmail.com>
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
>> > To post to this group, send email to sqlalchemy@googlegroups.com.
>> > 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
>> sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
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.
