turns out scalar columns already work....since 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 User....postgres 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 features....this 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 = 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: query=%s | property=%s" % (user.query_score,
> user.prop_score)
> print
> for tag in user.tags:
> print "%s * %s => query=%s | property=%s" % (tag.score1,
> tag.score2, tag.query_score, tag.prop_score)
>
> # ideally, I'd like to reuse the score property on tag in the score
> property on
> # users, and also I need to be able to defer/undefer those properties
>
> <statement_property.diff>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
