2008/11/1 Victor Lin <[EMAIL PROTECTED]>:
>
> Hi, I have an entity like this:
>
> class Item(Entity):
> title = Field(Uncode(32))
> agree = Field(Integer)
> vote = ManyToOne('Vote')
>
> class Vote(Entity):
> title = Field(Uncode(32))
> items = OneToMany('Item')
>
> Now I'd like to get sum of all agree of items. I remember that I have
> seen some method like this:
>
> class Vote(Entity):
> title = Field(Uncode(32))
> items = OneToMany('Item')
> @property
> def sumOfAgree(self):
> # do something to sum agree
> return sum
>
> It is so difficult to search information about Elixir, so I find no
> proper examples that show how to achieve that.
>
> And here comes another problem. What If I'd like to sort by
> sumOfAgree?
>
> For example:
> hotVoteList = Vote.query.order_by(desc(Vote.sumOfAgree))
>
> Is that possible to do what I want with Elixir? how? I will be
> appreciate.
>
> Thanks.
> Victor Lin.
Hi Victor,
When working with Elixir you should also refer to SQLAlchemy documentation:
http://www.sqlalchemy.org/trac/wiki/FAQ#HowdoIattachanaggregatecolumnorotherSQLexpressiontoanORMQueryi.e.maxcountxyetc.
You can try something like this:
from elixir import *
from sqlalchemy.sql import func
class Item(Entity):
title = Field(Unicode(32))
agree = Field(Integer)
vote = ManyToOne('Vote')
class Vote(Entity):
title = Field(Unicode(32))
items = OneToMany('Item')
@property
def sum_of_agree(self):
query = Item.query.filter_by(vote=self)
sum_col = func.sum(Item.agree).label('sum')
query = query.add_column(sum_col)
return query.one().sum
@classmethod
def get_ordered_by_agree_desc(cls):
query = cls.query.join(Item).group_by(Item.vote_id)
sum_col = func.sum(Item.agree).label('sum')
query = query.add_column(sum_col)
return [tuple[0] for tuple in query.order_by('sum DESC').all()]
metadata.bind = 'sqlite:///'
setup_all(True)
item1 = Item(title=u"Hello")
item1.agree = 1
item2 = Item(title=u"Foo")
item2.agree = 2
item3 = Item(title=u"Bar")
item3.agree = 3
item4 = Item(title=u"Baz")
item4.agree = 4
vote1 = Vote(title=u'A vote')
vote1.items.append(item1)
vote1.items.append(item2)
vote2 = Vote(title=u'Another vote')
vote2.items.append(item3)
vote2.items.append(item4)
session.commit()
assert vote1.sum_of_agree == 3
assert vote2.sum_of_agree == 7
assert Vote.get_ordered_by_agree_desc() == [vote2, vote1]
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"SQLElixir" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlelixir?hl=en
-~----------~----~----~----~------~----~------~--~---
