cumulative sum of 1-many table columns.

[jeetu]

My problem scenario is analogous to the following. I have an Artist table and an Album table. Each artist can have multiple albums with sales of each album. The artist also has a total_sales column which is basically a cumulative of album's sales for that artist. I tried reading about attribute events and memoized property but I am unable to get it integrated with my code.  I tried to achieve something like https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/o_KxuHwz4WQ. But my lack of thorough understanding of decoraters and sqlalchemy is proving to be a hindrance. My example code (heavily borrowed from resources on internet and sqlalchemy group) is http://pastebin.com/vhRTcrWV
Just for information if at all it matters:n my actual code I am using toscawidgets (inside turbogears) and dynamic forms for creating Albums table's values and Artist's values on a single page.

PS: I hope it is not sounding like homework

[Michael Bayer]

@memoized_property is a handy tool, and works simply, just clear out __dict__ of that key and it's reset.

since you're looking to work in python, the total sales are just:

    @memoized_property

    def total_sales(self):

        # This should be the sum of all Album sales for this Artist. It should

        # be updated as soon as/just after a new Album is added or an Album's

        # sales is updated.

        return sum([album.sales or 0 for album in self.albums])

the events you need are simple, just whenever "sales" or "artist" (ideally you'd name this in the singular since it is many-to-one) change, pop the "total_sales" out of the dict:

from sqlalchemy.orm import validates

class Album(Base):

    # ...

    @validates("sales")

    def _update_sales(self, key, value):

        if self.artist is not None:

            self.artist.__dict__.pop('total_sales', None)

        return value

    @validates("artist", include_removes=True)

    def _update_artist(self, key, artist, is_remove):

        artist.__dict__.pop('total_sales', None)

        return artist

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/CcGMUZ2UHSYJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[jeetu]

Thanks Michael for looking into my problem. I tried following your advice but still not able to get the total_sale. Do I have to implement some special getter/setter for memoized property. If you run the code, you will see that nowhere  'print "SETTING TOTAL_SALES"' is getting executed which is in the memoized definition. Also how can i access the memoized property as it is not available in straight forward querying on Artist.

http://pastebin.com/Gqa7b0dd

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

[jeetu]

Just ignore the above post. I am able to get it working for adding new artist with albums. But I am still getting some problems while updating the albums's sales values from Turogears/toscawidgets2 interface. Let me bring the problem to sqlalchemy level then I will post it further (if the problem still remains). The code in the above post is working fine for adding new Artist and Albums. Thanks Michael for helping me out.

[jeetu]

Just one minor update for future reference, We have to add the None checking condition in _update_artist  method as well for the update to happen properly in the web application. But I was unable to produce the problem in the text sqlalchemy file that I have posted in the pastebin.

@validates("artist", include_removes=True)

    def _update_artist(self, key, artist, is_remove):

         if artist is not None:

            artist.__dict__.pop('total_sales', None)

        return artist

[jeetu]

I am not able see the total_sales column in sqlite manager plugin of firefox and sqlagrid of toscawidgets. But I can access the total_sales when I use toscawidget's dbform. Any known reasons?

[Michael Bayer]

total_sales here is not a DB column here, it's just an attribute in your Python model.

the most legit way to see "total_sales" in your SQLite database would be to query for it.    select sum(artist.sales) from artist where album_id=<some album>.

Alternatively, a "denomalized" approach would be to copy the "total_sales" value directly into a new column in the SQLite DB, though this is not encouraged as it's more complicated, and allows for the database to be inconsistent (that is, total_sales might not match the actual total_sales, if a buggy application updated the DB incorrectly).   Usually this kind of thing is done when some particular data is needed very directly using only simple queries for performance reasons.

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/T3U9xjR-NS0J.

To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.