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: 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
Description: application/text
