[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On Apr 30, 1:11 pm, "Gaetan de Menten" <[EMAIL PROTECTED]> wrote: > There is (at least) one problem remaining though: subqueries do not > get correlated correctly. This is because the table in the main query > is aliased and the one in the subquery is not and that the > "correlator" in the Select class does not recognize an aliased table > as being the same as the original table. > > I see two ways to fix this: > > * The first and most easy one is to change the correlator to correlate > aliased tables with the original ones. Attached is patch which does > just that. But I highly suspect it's not correct to do that. > > * The other option is obviously to aliasize the table in the > subquery... The problem with that is that the ClauseAdapter in > sql_util simply doesn't do it... And I didn't see a way to make it do > it since the "from" objects in a select are pretty much private. Any > idea? ticket #52 needs to be implemented first. the work there is 5% adding the method, 95% in creating the unit tests so that any changes to sql.Select in the future dont break the method. secondly, the "from" list of Select can be affected in two ways, by adding a FromClause that implements _hide_froms() to "conceal" from clauses which it replaces, and also by explicitly calling correlate(from_obj) which is not quite what you need here. but theres no reason append_from() cant have a "hides=None" argument added to it so an external actor can also indicate "FROM object X masks FROM object Y". also theres not any huge reason there cant be a remove_from() either. looking at the code im seeing some possible simplifications to the _process_froms() method which might make some of this easier to see. the usage of these "from altering" methods in sql_util would all be just an enhancement to AbstractClauseProcessor who's usage would become more generalized to be able to process selects and not just binary/compound clause fragments. the Aliasizer also i think will soon be removed in all cases and replaced with ClauseAdapter which is easier to use. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On 4/30/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > turns out scalar columns already worksince they are > just...columns ! which ColumnProperty already handles. That's what I tried to get at... that it was supposed to "just work"... > any old SA > version will allow the test script to work, if you use ColumnProperty > explicitly along with two small fixes in sql.py. in the trunk ive > added those fixes, as well as reinstated the function "column_property > ()" as a synonym for ColumnProperty and adapted your "eager" handling > logic (also aliases the labels) so the columns work out in an eager > relation, so mappings: Thanks a lot for taking the trouble to fix those bugs. There is (at least) one problem remaining though: subqueries do not get correlated correctly. This is because the table in the main query is aliased and the one in the subquery is not and that the "correlator" in the Select class does not recognize an aliased table as being the same as the original table. I see two ways to fix this: * The first and most easy one is to change the correlator to correlate aliased tables with the original ones. Attached is patch which does just that. But I highly suspect it's not correct to do that. * The other option is obviously to aliasize the table in the subquery... The problem with that is that the ClauseAdapter in sql_util simply doesn't do it... And I didn't see a way to make it do it since the "from" objects in a select are pretty much private. Any idea? -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- Index: sql.py === --- sql.py (revision 2589) +++ sql.py (working copy) @@ -2875,9 +2875,12 @@ This basically means the given from object will not come out in this select statement's ``FROM`` clause when printed. """ +if isinstance(from_obj, Alias): +original = from_obj.original +else: +original = from_obj +self.__correlated[original] = from_obj -self.__correlated[from_obj] = from_obj - def append_from(self, fromclause): if type(fromclause) == str: fromclause = FromClause(fromclause)
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
turns out scalar columns already worksince they are
just...columns ! which ColumnProperty already handles. any old SA
version will allow the test script to work, if you use ColumnProperty
explicitly along with two small fixes in sql.py. in the trunk ive
added those fixes, as well as reinstated the function "column_property
()" as a synonym for ColumnProperty and adapted your "eager" handling
logic (also aliases the labels) so the columns work out in an eager
relation, so mappings:
mapper(Tag, tags_table, properties={
'query_score': column_property((tags_table.c.score1 *
tags_table.c.score2).label('tag_score'))
})
user_score = select([func.sum(tags_table.c.score1 *
tags_table.c.score2)],
tags_table.c.user_id == users_table.c.id,
scalar=True).label('user_score')
mapper(User, users_table, properties={
'tags': relation(Tag, backref='user'),
'query_score': column_property(user_score),
})
though it still wont work with every kind of eager load, such as an
eager load from Tag to Userpostgres will see the Tag table
referenced in the subquery and demand the GROUP BY expression as ive
mentioned. i dont really see any way around that scenario.
im usually up for adding features to SA if they dont require actually
adding any featuresthis one just needed two bug fixes and a
little bit of extra grease in the eager load setup.
On Apr 25, 2007, at 3:52 PM, Gaetan de Menten wrote:
> It's better with the attachments (the actual patch and a small
> demonstration/test file)...
>
> On 4/25/07, Gaetan de Menten <[EMAIL PROTECTED]> wrote:
>> Ok, I'm quite a bit stubborn at times, so I implemented this the
>> way I
>> thought because I think it makes much more sense this way.
>>
>> Attached is an experimental (as usual) patch to add a
>> StatementProperty, so that you can define stuff like:
>>
>> mapper(Tag, tags_table, properties={
>> 'query_score': StatementProperty((tags_table.c.score1 *
>> tags_table.c.score2).label('tag_score'), Float()),
>> })
>>
>> or even:
>>
>> user_score = select([func.sum(tags_table.c.score1 *
>> tags_table.c.score2)],
>> tags_table.c.user_id == users_table.c.id,
>> scalar=True).label('user_score')
>>
>> mapper(User, users_table, properties={
>> 'tags': relation(Tag, backref='user', lazy=False),
>> 'query_score': StatementProperty(user_score, Float()),
>> })
>>
>> I don't see what's wrong with this approach so far. As always, I
>> might
>> not see the big picture... I just hope this will be useful in some
>> way, even if it's not what you envisioned.
>>
>> Some random remarks:
>> - the statement you give must have a label (that seem pretty logical
>> this way though)
>> - you need to manually provide the type of the property you create
>> (seem logical too). In a final patch, we'd probably want to also
>> accept types in their class form (Float and Float()).
>> - it works both for lazy and eagerloads (I struggled quite a bit to
>> get those to work)
>> - subselects pulled from a lazyload don't work though. But I think it
>> should be quite easily fixable.
>> - As always, I'm not attached to the names I've given.
>>
>> On 4/24/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>>>
>>>
>>> On Apr 24, 2007, at 4:44 AM, Gaetan de Menten wrote:
>>>
> the next way is to do it almost the same as Jonathan's blog
> says to
> do it, except youd map the relation to some intermediary class
> like
> "Score", and then use AssociationProxy to apply the "scalar"
> property
> to the class.
I thought about something like this but it felt sooo hacky I
disregarded it quickly.
>>>
>>> this is probably the least hacky as like i said im probably going to
>>> implement a feature that works just like this.
>>>
>>
>>
>>
>> --
>> Gaëtan de Menten
>> http://openhex.org
>>
>
>
> --
> Gaëtan de Menten
> http://openhex.org
>
> >
> from sqlalchemy import *
> from sqlalchemy.orm.properties import StatementProperty
>
> metadata = MetaData()
>
> users_table = Table('users', metadata,
> Column('id', Integer, primary_key=True),
> Column('name', String(16)),
> )
>
> tags_table = Table('tags', metadata,
> Column('id', Integer, primary_key=True),
> Column('user_id', Integer, ForeignKey("users.id")),
> Column('score1', Float),
> Column('score2', Float),
> )
>
> metadata.connect('sqlite:///')
> metadata.create_all()
>
> class User(object):
> def __init__(self, name):
> self.name = name
>
> @property
> def prop_score(self):
> return sum(tag.prop_score for tag in self.tags)
>
> class Tag(object):
> def __init__(self, score1, score2):
> self.score1 = score1
> self.score2 = score2
>
> @property
> def prop_score(self):
> return self.score1 * self.score2
>
> # this doesn't work
> tag_score
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On 4/26/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Apr 26, 2007, at 4:40 AM, Gaetan de Menten wrote: > > > >> - the biggest picture not here - its read only ! > > > > It's obviously meant to be that way... So I don't see a problem here. > > We might want to add an exception when people try to change it, but > > that shouldn't be too hard. Besides, I don't see how the > > association_proxy approach would make it any different. > > > > the function allows the adding of any arbitrary SQL expression. if > that expression were just a column somewhere, and not an aggregate, > it would be expected that this would be writeable. reasons like this > are why the feature as proposed is too narrow. IMO if you are > querying things that are read-only, they generally belong more on the > Query side of things and not the object-relationship side of > things Well, for me, everything which is called often enough should be factored in the class as a property. The problem being that if you use a python property, you'd have an extremely inefficient code (to compute one aggregate method, you'd have to load all related instances, and so on...). > within the SA philosophy at least. Ok, I'll shut-up after this mail... I can't argue with that. ;-) > >> - we already have a way to do the above - map to a select > >> statement, > >> thereby forcing the user to figure out GROUP BY, polymorphic loading, > >> etc., also produces an encapsulated statement which eager loaders, > >> LIMIT/OFFSET etc. criterion can be more readily tacked onto. > > > > No, this doesn't suit my needs. I need that particular property to be > > deferrable. As I said, I got several of them and, for each query, I > > can need any combination of them. Even though my patch doesn't include > > the code to do it, with "my" approach, I think it's possible to do it, > > unlike with the "map to a select statement" approach. > > yeah i would just use non_primary mappers and/or Query.select(full > select statement)/ Query.add_column(.etc), the "deferred" version > would be just a property on the class itself that issues a Query. I'll try this (add_column) route. The problem with this approach is that I fear it won't be easy to have those additional columns still available as lazy load. Though I haven't thought much about this option yet, so that might prove easier than I think. > essentially everything you want is possible here except that the > "deferred" option doesnt hook into it the way you want. which almost > implies that maybe you just want a new kind of option() that can > select a non-primary mapper for a particular class query... query > (MyClass).use_mapper(aggregate_mapper).select()... Almost... I would have wanted to not even have to declare alternate mappers, only all the possible "additional" (aggregate) columns. And at query time say: fetch this and that "column" (in addition to the main query) and not those others (which would still be available as lazy properties). > at most, for this feature, it would have to be called "aggregate()" > since its pretty limited to that, and take a limited set of criterion > in the same way as relation(), and the GROUP BY stuff would have to > work since postgres/oracle/etc. require that. FYI, postgres eats my example just fine. > the GROUP BY columns > would be automatically added during the query process since its > basically, "every column we're selecting". the whole query wrapping > thing would have to take place too to cleanly tack on other eager > loads, limiting criterion and such. i think some things might > continue to break when the query is formed this way (particularly > inheritance) and we might have to add a disclaimer "oh by the way, > you cant do X, Y and Z when you have a non-deferred aggregate() > column" if thats the case. > > the main thing is, all the tools are there to do this already, we're > just talking about adding "yet another way to do it" to suit the code > looking a certain way, in a way that also has a lot of non-working > use cases. plus, just because its not in core, so what ? its a > great recipe, maybe extension, we can even add a unit test to ensure > the API continues to support it. elixir can have a built-in feature > tying to the recipe as well (since elixir is the "convenience" layer > with a little more opinion). Ok, I'll try to implement that in my own private layer on top of Elixir, then *if* that's not too invasive I'll either include it in Elixir (if nobody disagrees there) or post it as a recipe for SQLAlchemy. -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On Apr 26, 2007, at 9:46 AM, Gaetan de Menten wrote: > > But nevermind the patch I did. I realize now it's pretty much useless. > But what about the approach I discussed in my previous mail? (using > what is in the relation loaders but without creating the instances) > well as ive said, id like it to support scalars or collections, and to be writeable...it would generally be used for values loaded from another table that are returned as non-entities, like integers, strings, etc. the corresponding feature in hibernate is http:// www.hibernate.org/hib_docs/v3/reference/en/html/ collections.html#collections-ofvalues , except as usual we have the "uselist=False" option which allows it to be just one element. I kind of feel like i have to experiment with hibernate to see exactly what the second option there, "formula='any sql expression'", actually does. im not sure if the feature covers the specific thing you want to do. at the very least, if you mapped to an aggregate thered be a "viewonly=True" thing happening there. implementation-wise, our entity-mapping capabilities are the best way to handle keeping track of these elements and writing them back to the DB, so it would at some level use mapped entities that are handled behind the scenes. you think its a bad idea because youre looking at it as something bolted on to the outside, but it doesnt have to be that way. its likely that it may be best handled as a collection of entities that is only generated at flush time against the mapped collection, within the dependency processor itself. the attribute instrumentation system would need some tweaks to support collections of non-entities as well (which i think is probably not a big deal, if even it cant do it already). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On Apr 26, 2007, at 4:40 AM, Gaetan de Menten wrote: > >> - the biggest picture not here - its read only ! > > It's obviously meant to be that way... So I don't see a problem here. > We might want to add an exception when people try to change it, but > that shouldn't be too hard. Besides, I don't see how the > association_proxy approach would make it any different. > the function allows the adding of any arbitrary SQL expression. if that expression were just a column somewhere, and not an aggregate, it would be expected that this would be writeable. reasons like this are why the feature as proposed is too narrow. IMO if you are querying things that are read-only, they generally belong more on the Query side of things and not the object-relationship side of thingswithin the SA philosophy at least. >> - we already have a way to do the above - map to a select >> statement, >> thereby forcing the user to figure out GROUP BY, polymorphic loading, >> etc., also produces an encapsulated statement which eager loaders, >> LIMIT/OFFSET etc. criterion can be more readily tacked onto. > > No, this doesn't suit my needs. I need that particular property to be > deferrable. As I said, I got several of them and, for each query, I > can need any combination of them. Even though my patch doesn't include > the code to do it, with "my" approach, I think it's possible to do it, > unlike with the "map to a select statement" approach. yeah i would just use non_primary mappers and/or Query.select(full select statement)/ Query.add_column(.etc), the "deferred" version would be just a property on the class itself that issues a Query. essentially everything you want is possible here except that the "deferred" option doesnt hook into it the way you want. which almost implies that maybe you just want a new kind of option() that can select a non-primary mapper for a particular class query... query (MyClass).use_mapper(aggregate_mapper).select()... at most, for this feature, it would have to be called "aggregate()" since its pretty limited to that, and take a limited set of criterion in the same way as relation(), and the GROUP BY stuff would have to work since postgres/oracle/etc. require that. the GROUP BY columns would be automatically added during the query process since its basically, "every column we're selecting". the whole query wrapping thing would have to take place too to cleanly tack on other eager loads, limiting criterion and such. i think some things might continue to break when the query is formed this way (particularly inheritance) and we might have to add a disclaimer "oh by the way, you cant do X, Y and Z when you have a non-deferred aggregate() column" if thats the case. the main thing is, all the tools are there to do this already, we're just talking about adding "yet another way to do it" to suit the code looking a certain way, in a way that also has a lot of non-working use cases. plus, just because its not in core, so what ? its a great recipe, maybe extension, we can even add a unit test to ensure the API continues to support it. elixir can have a built-in feature tying to the recipe as well (since elixir is the "convenience" layer with a little more opinion). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On 4/26/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Apr 26, 2007, at 4:40 AM, Gaetan de Menten wrote: > > >> - you have an aggregate function there. wheres the GROUP > >> BY ? not > >> just of the main table's columns but any other columns named for > >> eager loads. > > > > It's unneeded in my example, and for cases where this would be needed, > > I think we could easily enhance the StatementProperty so that you can > > add anything to the parent query: join conditions, etc... > > > > how do you select multiple columns, where only one column is used in > an aggregate function, and no GROUP BY? The answer is: you don't. This is a subselect. Here is, for example, the query generated for the lazy version: SELECT (SELECT sum(tags.score1 * tags.score2) FROM tags WHERE tags.user_id = users.id) AS user_score, users.id AS users_id, users.name AS users_name FROM users ORDER BY users.id > are you using mysql only ? Nope. By the way, the patch I did is buggier than I thought for eager relationships. I thought it broke only for subselects, but the simple "statement" I provided in my example doesn't work nicely either (because it needlessly joins to the "tags" table). So the result are correct but it's awfull behind the scene. :) But nevermind the patch I did. I realize now it's pretty much useless. But what about the approach I discussed in my previous mail? (using what is in the relation loaders but without creating the instances) -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On Apr 26, 2007, at 4:40 AM, Gaetan de Menten wrote: >> - you have an aggregate function there. wheres the GROUP >> BY ? not >> just of the main table's columns but any other columns named for >> eager loads. > > It's unneeded in my example, and for cases where this would be needed, > I think we could easily enhance the StatementProperty so that you can > add anything to the parent query: join conditions, etc... > how do you select multiple columns, where only one column is used in an aggregate function, and no GROUP BY? are you using mysql only ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On 4/26/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
>
> On Apr 25, 2007, at 3:52 PM, Gaetan de Menten wrote:
>
> > It's better with the attachments (the actual patch and a small
> > demonstration/test file)...
> >
> > On 4/25/07, Gaetan de Menten <[EMAIL PROTECTED]> wrote:
> >> Ok, I'm quite a bit stubborn at times, so I implemented this the
> >> way I
> >> thought because I think it makes much more sense this way.
> >>
> >> Attached is an experimental (as usual) patch to add a
> >> StatementProperty, so that you can define stuff like:
> >>
> >> mapper(Tag, tags_table, properties={
> >> 'query_score': StatementProperty((tags_table.c.score1 *
> >> tags_table.c.score2).label('tag_score'), Float()),
> >> })
> >>
> >> or even:
> >>
> >> user_score = select([func.sum(tags_table.c.score1 *
> >> tags_table.c.score2)],
> >> tags_table.c.user_id == users_table.c.id,
> >> scalar=True).label('user_score')
> >>
> >> mapper(User, users_table, properties={
> >> 'tags': relation(Tag, backref='user', lazy=False),
> >> 'query_score': StatementProperty(user_score, Float()),
> >> })
> >>
> >> I don't see what's wrong with this approach so far. As always, I
> >> might
> >> not see the big picture... I just hope this will be useful in some
> >> way, even if it's not what you envisioned.
>
> OK well thats not bad. also im glad you are getting familiarized with
> ORM internals, since there are very few who have ventured in there so
> far. But here some bigger picture things:
>
> - you have an aggregate function there. wheres the GROUP BY ? not
> just of the main table's columns but any other columns named for
> eager loads.
It's unneeded in my example, and for cases where this would be needed,
I think we could easily enhance the StatementProperty so that you can
add anything to the parent query: join conditions, etc...
> - it only works for scalars. what about statements that return lists
> ?
Yeah that's a limitation of the approach, but I think it's ok. Maybe
rename the thing to ScalarProperty to make it more obvious?
> - it doesnt figure out any kind of join conditions to the parent
> table. what happens when a polymorphically loading mapper tries to
> use it (or any of the other myriad bizarro things that happen with
> inheritance)?
Ok, that might be a problem. Honestly, I have a pretty narrow
knowledge (and fuzzy understanding) of the internals of that whole
polymorphic inheritance. But wouldn't it be possible to factor out
what is done in the relation loaders. The problems ought to be the
same, right? And it'd also solve your list statement complaint above.
From my understanding, it's "just" the last part (ie to create an
instance out of the scalar(s)) which needs to be skipped). In fact,
this was how I originally intended to implement the thing but that
code seemed too complex to tap into to make my demonstration patch, so
I rather took the ColumnProperty code as a base ;-).
My point was and still is (I just realized I haven't said it clearly
yet): I don't get why it would be preferable to go through the trouble
of creating instances and proxies just to go through them and only
ever use them as scalars, when one could (or at least, I'm convinced
one could) simply skip the instance creation step.
> - the biggest picture not here - its read only !
It's obviously meant to be that way... So I don't see a problem here.
We might want to add an exception when people try to change it, but
that shouldn't be too hard. Besides, I don't see how the
association_proxy approach would make it any different.
> - we already have a way to do the above - map to a select statement,
> thereby forcing the user to figure out GROUP BY, polymorphic loading,
> etc., also produces an encapsulated statement which eager loaders,
> LIMIT/OFFSET etc. criterion can be more readily tacked onto.
No, this doesn't suit my needs. I need that particular property to be
deferrable. As I said, I got several of them and, for each query, I
can need any combination of them. Even though my patch doesn't include
the code to do it, with "my" approach, I think it's possible to do it,
unlike with the "map to a select statement" approach.
>
> Since you have built upon interfaces that I consider to be public so
> this is definitely at least a decent recipe. im not sold on it as a
> core feature though since its scope is too narrow compared to what
> people will expect from it.
>
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On Apr 25, 2007, at 3:52 PM, Gaetan de Menten wrote:
> It's better with the attachments (the actual patch and a small
> demonstration/test file)...
>
> On 4/25/07, Gaetan de Menten <[EMAIL PROTECTED]> wrote:
>> Ok, I'm quite a bit stubborn at times, so I implemented this the
>> way I
>> thought because I think it makes much more sense this way.
>>
>> Attached is an experimental (as usual) patch to add a
>> StatementProperty, so that you can define stuff like:
>>
>> mapper(Tag, tags_table, properties={
>> 'query_score': StatementProperty((tags_table.c.score1 *
>> tags_table.c.score2).label('tag_score'), Float()),
>> })
>>
>> or even:
>>
>> user_score = select([func.sum(tags_table.c.score1 *
>> tags_table.c.score2)],
>> tags_table.c.user_id == users_table.c.id,
>> scalar=True).label('user_score')
>>
>> mapper(User, users_table, properties={
>> 'tags': relation(Tag, backref='user', lazy=False),
>> 'query_score': StatementProperty(user_score, Float()),
>> })
>>
>> I don't see what's wrong with this approach so far. As always, I
>> might
>> not see the big picture... I just hope this will be useful in some
>> way, even if it's not what you envisioned.
OK well thats not bad. also im glad you are getting familiarized with
ORM internals, since there are very few who have ventured in there so
far. But here some bigger picture things:
- you have an aggregate function there. wheres the GROUP BY ? not
just of the main table's columns but any other columns named for
eager loads.
- it only works for scalars. what about statements that return lists ?
- it doesnt figure out any kind of join conditions to the parent
table. what happens when a polymorphically loading mapper tries to
use it (or any of the other myriad bizarro things that happen with
inheritance)?
- the biggest picture not here - its read only !
- we already have a way to do the above - map to a select statement,
thereby forcing the user to figure out GROUP BY, polymorphic loading,
etc., also produces an encapsulated statement which eager loaders,
LIMIT/OFFSET etc. criterion can be more readily tacked onto.
Since you have built upon interfaces that I consider to be public so
this is definitely at least a decent recipe. im not sold on it as a
core feature though since its scope is too narrow compared to what
people will expect from it.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
It's better with the attachments (the actual patch and a small
demonstration/test file)...
On 4/25/07, Gaetan de Menten <[EMAIL PROTECTED]> wrote:
> Ok, I'm quite a bit stubborn at times, so I implemented this the way I
> thought because I think it makes much more sense this way.
>
> Attached is an experimental (as usual) patch to add a
> StatementProperty, so that you can define stuff like:
>
> mapper(Tag, tags_table, properties={
> 'query_score': StatementProperty((tags_table.c.score1 *
> tags_table.c.score2).label('tag_score'), Float()),
> })
>
> or even:
>
> user_score = select([func.sum(tags_table.c.score1 *
> tags_table.c.score2)],
> tags_table.c.user_id == users_table.c.id,
> scalar=True).label('user_score')
>
> mapper(User, users_table, properties={
> 'tags': relation(Tag, backref='user', lazy=False),
> 'query_score': StatementProperty(user_score, Float()),
> })
>
> I don't see what's wrong with this approach so far. As always, I might
> not see the big picture... I just hope this will be useful in some
> way, even if it's not what you envisioned.
>
> Some random remarks:
> - the statement you give must have a label (that seem pretty logical
> this way though)
> - you need to manually provide the type of the property you create
> (seem logical too). In a final patch, we'd probably want to also
> accept types in their class form (Float and Float()).
> - it works both for lazy and eagerloads (I struggled quite a bit to
> get those to work)
> - subselects pulled from a lazyload don't work though. But I think it
> should be quite easily fixable.
> - As always, I'm not attached to the names I've given.
>
> On 4/24/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
> >
> >
> > On Apr 24, 2007, at 4:44 AM, Gaetan de Menten wrote:
> >
> > >
> > >> the next way is to do it almost the same as Jonathan's blog says to
> > >> do it, except youd map the relation to some intermediary class like
> > >> "Score", and then use AssociationProxy to apply the "scalar" property
> > >> to the class.
> > >
> > > I thought about something like this but it felt sooo hacky I
> > > disregarded it quickly.
> > >
> >
> > this is probably the least hacky as like i said im probably going to
> > implement a feature that works just like this.
> >
>
>
>
> --
> Gaëtan de Menten
> http://openhex.org
>
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
from sqlalchemy import *
from sqlalchemy.orm.properties import StatementProperty
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(16)),
)
tags_table = Table('tags', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey("users.id")),
Column('score1', Float),
Column('score2', Float),
)
metadata.connect('sqlite:///')
metadata.create_all()
class User(object):
def __init__(self, name):
self.name = name
@property
def prop_score(self):
return sum(tag.prop_score for tag in self.tags)
class Tag(object):
def __init__(self, score1, score2):
self.score1 = score1
self.score2 = score2
@property
def prop_score(self):
return self.score1 * self.score2
# this doesn't work
tag_score = select([tags_table.c.score1 * tags_table.c.score2],
scalar=True).label('tag_score')
mapper(Tag, tags_table, properties={
# this doesn't work. It seems like the inside of a select is not aliasized.
#'query_score': StatementProperty(tag_score, Float()),
'query_score': StatementProperty((tags_table.c.score1 *
tags_table.c.score2).label('tag_score'),
Float()),
})
user_score = select([func.sum(tags_table.c.score1 *
tags_table.c.score2)],
tags_table.c.user_id == users_table.c.id,
scalar=True).label('user_score')
mapper(User, users_table, properties={
'tags': relation(Tag, backref='user'),
#'tags': relation(Tag, backref='user', lazy=False),
'query_score': StatementProperty(user_score, Float()),
})
u1 = User('joe')
t1 = Tag(5.0, 3.0)
t2 = Tag(55.0, 1.0)
u1.tags = [t1, t2]
u2 = User('bar')
t3 = Tag(5.0, 4.0)
t4 = Tag(50.0, 1.0)
t5 = Tag(15.0, 2.0)
u2.tags = [t3, t4, t5]
session = create_session()
session.save(u1)
session.save(u2)
session.flush()
session.clear()
metadata.engine.echo = True
users = session.query(User).select()
print
for user in users:
print "===%s===" % user.name
print "totals: que
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
Ok, I'm quite a bit stubborn at times, so I implemented this the way I
thought because I think it makes much more sense this way.
Attached is an experimental (as usual) patch to add a
StatementProperty, so that you can define stuff like:
mapper(Tag, tags_table, properties={
'query_score': StatementProperty((tags_table.c.score1 *
tags_table.c.score2).label('tag_score'), Float()),
})
or even:
user_score = select([func.sum(tags_table.c.score1 *
tags_table.c.score2)],
tags_table.c.user_id == users_table.c.id,
scalar=True).label('user_score')
mapper(User, users_table, properties={
'tags': relation(Tag, backref='user', lazy=False),
'query_score': StatementProperty(user_score, Float()),
})
I don't see what's wrong with this approach so far. As always, I might
not see the big picture... I just hope this will be useful in some
way, even if it's not what you envisioned.
Some random remarks:
- the statement you give must have a label (that seem pretty logical
this way though)
- you need to manually provide the type of the property you create
(seem logical too). In a final patch, we'd probably want to also
accept types in their class form (Float and Float()).
- it works both for lazy and eagerloads (I struggled quite a bit to
get those to work)
- subselects pulled from a lazyload don't work though. But I think it
should be quite easily fixable.
- As always, I'm not attached to the names I've given.
On 4/24/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
>
> On Apr 24, 2007, at 4:44 AM, Gaetan de Menten wrote:
>
> >
> >> the next way is to do it almost the same as Jonathan's blog says to
> >> do it, except youd map the relation to some intermediary class like
> >> "Score", and then use AssociationProxy to apply the "scalar" property
> >> to the class.
> >
> > I thought about something like this but it felt sooo hacky I
> > disregarded it quickly.
> >
>
> this is probably the least hacky as like i said im probably going to
> implement a feature that works just like this.
>
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On Apr 24, 2007, at 4:44 AM, Gaetan de Menten wrote: > >> the next way is to do it almost the same as Jonathan's blog says to >> do it, except youd map the relation to some intermediary class like >> "Score", and then use AssociationProxy to apply the "scalar" property >> to the class. > > I thought about something like this but it felt sooo hacky I > disregarded it quickly. > this is probably the least hacky as like i said im probably going to implement a feature that works just like this. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On 4/24/07, Gaetan de Menten <[EMAIL PROTECTED]> wrote:
> On 4/23/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
> >
> >
> > On Apr 23, 2007, at 3:49 PM, Gaetan de Menten wrote:
> >
> > > Hello there,
> > >
> > > In a mapped object, is there any way to map a scalar attribute to an
> > > arbitrary selectable/subquery?
> > >
> > > Jonathan Ellis demonstrated how to do that for relations on this page:
> > > http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html
> > >
> > > I'd like to do that for scalars.
> > >
> > > I've thought about using a property returning a query, but this still
> > > generates one query per user (my mapped object) and I need to do
> > > everything in one pass.
> > >
> > > See attached file for an example of what I'd like to do.
> > >
> > > I've the feeling it might already be possible but I don't see how. If
> > > it's not possible yet, do you have any pointer how I could implement
> > > that?
> > >
> >
> > there are probably three general ways to do what youre doing there.
>
> Thanks for the quick answer!
>
> > the oldest way is something i did in the zblog demo before SA 0.1 was
> > released, which is that you map to the full query you want:
> >
> > s = select([users_table, func.sum(tags_table.c.score1 *
> > tags_table.c.score2).label('score')],
> > users_table.c.user_id==tags_table.c.user_id, group_by=[c for c in
> > users_table.c])
> >
> > mapper(User, s)
> >
> > the effect above is that your func() becomes another ColumnProperty.
>
> That's what I was looking for. So simple... and I didn't think of it. Damn...
Hmmm, on second thought, it's not enough for my needs. I need to be
able to defer that "column" and undefer it per query. I know I could
use several mappers with entity_names but since I'll have several such
"columns", it'll be too much trouble to create an alternate mapper for
each and every possible combination of those "columns" being deferred
or not. So I guess I'll need to use the ugly intermediary class
solution for now.
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On 4/23/07, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
>
> On Apr 23, 2007, at 3:49 PM, Gaetan de Menten wrote:
>
> > Hello there,
> >
> > In a mapped object, is there any way to map a scalar attribute to an
> > arbitrary selectable/subquery?
> >
> > Jonathan Ellis demonstrated how to do that for relations on this page:
> > http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html
> >
> > I'd like to do that for scalars.
> >
> > I've thought about using a property returning a query, but this still
> > generates one query per user (my mapped object) and I need to do
> > everything in one pass.
> >
> > See attached file for an example of what I'd like to do.
> >
> > I've the feeling it might already be possible but I don't see how. If
> > it's not possible yet, do you have any pointer how I could implement
> > that?
> >
>
> there are probably three general ways to do what youre doing there.
Thanks for the quick answer!
> the oldest way is something i did in the zblog demo before SA 0.1 was
> released, which is that you map to the full query you want:
>
> s = select([users_table, func.sum(tags_table.c.score1 *
> tags_table.c.score2).label('score')],
> users_table.c.user_id==tags_table.c.user_id, group_by=[c for c in
> users_table.c])
>
> mapper(User, s)
>
> the effect above is that your func() becomes another ColumnProperty.
That's what I was looking for. So simple... and I didn't think of it. Damn...
> the next way is to do it almost the same as Jonathan's blog says to
> do it, except youd map the relation to some intermediary class like
> "Score", and then use AssociationProxy to apply the "scalar" property
> to the class.
I thought about something like this but it felt sooo hacky I
disregarded it quickly.
> I might put a built-in feature in sa for "scalar"
> properties that does this, automatically creating an anonymous class
> for the intermediaryso that would be the closest to a "scalar
> relation".
A built-in thing would be great, but I don't see the point in using an
intermediary class at all. Wouldn't it be possible to "just" skip the
"make an instance out of the value" part and be done with it (more
like in your first solution)? It's probably no as easy as I make it
sound but it really feels like a better solution.
--
Gaëtan de Menten
http://openhex.org
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Selectable/subquery for a (scalar) column?
On Apr 23, 2007, at 3:49 PM, Gaetan de Menten wrote:
> Hello there,
>
> In a mapped object, is there any way to map a scalar attribute to an
> arbitrary selectable/subquery?
>
> Jonathan Ellis demonstrated how to do that for relations on this page:
> http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html
>
> I'd like to do that for scalars.
>
> I've thought about using a property returning a query, but this still
> generates one query per user (my mapped object) and I need to do
> everything in one pass.
>
> See attached file for an example of what I'd like to do.
>
> I've the feeling it might already be possible but I don't see how. If
> it's not possible yet, do you have any pointer how I could implement
> that?
>
there are probably three general ways to do what youre doing there.
the oldest way is something i did in the zblog demo before SA 0.1 was
released, which is that you map to the full query you want:
s = select([users_table, func.sum(tags_table.c.score1 *
tags_table.c.score2).label('score')],
users_table.c.user_id==tags_table.c.user_id, group_by=[c for c in
users_table.c])
mapper(User, s)
the effect above is that your func() becomes another ColumnProperty.
the next way is to do it almost the same as Jonathan's blog says to
do it, except youd map the relation to some intermediary class like
"Score", and then use AssociationProxy to apply the "scalar" property
to the class. I might put a built-in feature in sa for "scalar"
properties that does this, automatically creating an anonymous class
for the intermediaryso that would be the closest to a "scalar
relation".
the third way is not as slick but is to use add_column() on query:
session.query(User).join('tags').add_column(func.sum
(tags_table.c.score1 * tags_table.c.score2).label('score'))).group_by
([c for c in users_table.c])
which will give you back tuples of (User, int).
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
