Thanks Michael. I'll go through your solutions one by one and will try to understand them.
For synonym_for as per I understood from the examples it just provides a convenient name (sort of alias) for querying. I have the spin off advantage using it that it shows up in toscawidget sqlajqgrid as total_sales itself and shows the values of real total_sales. The only problem is that I can's sort/search over them. The reason may be that it is a python defined function instead of actual column. On using column_property, actually my total_sales is sort of complex function. I have to use a full python function in it because it contains some if-else comparisons based on conditions depended on some other table's attributes. All column_property examples that I searched on net use on statement only. Is it possible to use a full blown python function instead of simple aggregate of relationship tables. As for hybrid_property, its really the thing. But it requires an explicit join statement in query. And as you rightly pointed out, most probably tw2 query do not support joins. I'll try to implement your suggestions one by one in my code and see which one solves my problems. ps:I haven't yet checked the comparable_property. On Sunday, 26 August 2012 21:33:28 UTC+5:30, Michael Bayer wrote: > > One key error in your paste is that you're referring to a name that has no > meaning, "_total_sales". The name here would be an existing mapped > column. That you have no existing mapped column for "total_sales" is part > of the fact that there's a SQL expression that computes "total sales" > missing, which is what you're looking for if you're trying to use > Artist.total_sales in a SQL query. This SQL expression would need to use > the SQL SUM() function against the rows in the related table to come up > with the aggregated sales. > > To have a single attribute do an aggregation against another table, > without requiring any changes to the structure of the enclosing statement, > requires a correlated subquery. The technique is extremely similar to > what you'd see here: > http://docs.sqlalchemy.org/en/rel_0_7/orm/mapper_config.html#using-column-property. > > > You could in fact replace your whole total_sales attribute, to just use a > column_property() structured almost the same as what you see there. You > can add deferred=True to it to prevent it from loading until accessed. > You'd add expire_on_flush=True to it, so that after new Album info is > flushed, the attribute would be expired, and would load the new number on > next access. It's a MapperProperty so toscawidgets would pick it up. It > would cut about 30 lines of code from this example. special property, > event stuff, etc., all unnecessary. > > The other way would be to use a @hybrid, which would maintain the > "in-memory" calculation in combination with the SQL expression. The > advantage here is that you can get the latest "total_sales" without a flush > to the database and without a SELECT statement, when you work in memory. > It would be extremely helpful if the toscawidgets developers could be > convinced to support @hybrid attributes, since that's the recommended > pattern attributes that split between Python calculations and SQL > calculations. > > Finally the third approach is the comparable_property(), which is an older > system superseded by hybrids. This is the one where you'd need to build a > Comparator, which as you've noticed is not something I'm encouraging new > developers to deal with, in conjunction with the whole @property approach > and all that to maintain the "in-memory" version. There's no reason to > use comparable_property() nowadays, except for tools like toscawidgets > which only understand MapperProperty objects. > > I think for the moment you need to decide between "it works" > (column_property()) and "it's much more complicated, but saves the need for > a flush() + a SELECT statement when in memory (comparable_property() > imitating @hybrid to workaround TW not supporting @hybrid)". > > > On Aug 26, 2012, at 7:11 AM, jeetu wrote: > > Here is the paste. > > http://pastebin.com/MzLNWUw2 > > 1) rational for using synonym_for: the sqlajqgrid of toscawidgets2 ( > http://tw2-demos.threebean.org/module?module=tw2.jqplugins.jqgrid) uses > > sqlalchemy.orm.class_mapper(cls.entity).iterate_properties which does not show > defined attributs/memoized_property column (like total_sales in code). But > when I use synonym_for > it displays that column. > > 2) It uses default sorters/comparators of sqlalchemy but those does not work > on total_sales field > producing the same traceback just as you get when running this example. > > It seems like it searches for some comparator sort of things for total_sales > but I am not able to > get the hang of it. > > On Sunday, 26 August 2012 07:32:59 UTC+5:30, Michael Bayer wrote: >> >> >> On Aug 25, 2012, at 4:48 PM, jeetu wrote: >> >> > I have a sqlalchemy table defined like follows >> > >> > class Album(DeclarativeBase): >> > >> > # Name of mapped db table >> > __tablename__ = 'material' >> > id = Column(Integer, primary_key=True) >> > name = Column(Unicode(50), nullable=False, unique=True) >> > >> > @synonym_for("_voltage") >> > @memoized_property >> > def total_sales(self): >> > do some calculation here >> > return result >> > >> > For some reasons(showing it in sqlajqgrid of tw2) I am using >> synonym_for. Now when I try to use the sorting/comparisons which inturn >> calls for same functions of sqlalchemy. it does not work. It gives a long >> traceback with statements like >> > >> > return getattr(self.comparator, attribute) >> > File >> "/home/jeetu/LEARN/turbogears/tg22_venv/local/lib/python2.7/site-packages/SQLAlchemy-0.7.8-py2.7-linux-x86_64.egg/sqlalchemy/util/langhelpers.py", >> >> line 485, in __get__ >> > obj.__dict__[self.__name__] = result = self.fget(obj) >> > File >> "/home/jeetu/LEARN/turbogears/tg22_venv/local/lib/python2.7/site-packages/SQLAlchemy-0.7.8-py2.7-linux-x86_64.egg/sqlalchemy/orm/attributes.py", >> >> line 203, in comparator >> > self._comparator = self._comparator() >> > File >> "/home/jeetu/LEARN/turbogears/tg22_venv/local/lib/python2.7/site-packages/SQLAlchemy-0.7.8-py2.7-linux-x86_64.egg/sqlalchemy/orm/descriptor_props.py", >> >> line 67, in <lambda> >> > lambda: self._comparator_factory(mapper), >> > File >> "/home/jeetu/LEARN/turbogears/tg22_venv/local/lib/python2.7/site-packages/SQLAlchemy-0.7.8-py2.7-linux-x86_64.egg/sqlalchemy/orm/descriptor_props.py", >> >> line 376, in _comparator_factory >> > prop = self._proxied_property >> > RuntimeError: maximum recursion depth exceeded while calling a Python >> object >> > >> > As to my understanding it is searching for comparator function but not >> able to find one. I tried implementing some sort of PropComparator, >> ColumnComparator etc, but not able to get it rigth. Can somebody help me to >> get the sorting function implemented. >> >> >> I'd need a full example that reproduces this (which means, a single .py >> file I can copy locally, and run it. ). the above snippet doesn't include >> any detail, such as what "_voltage" is, what the comparator looks like, >> etc. >> >> >> > -- > 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/-/5IO5C_zCQ2QJ. > To post to this group, send email to sqlal...@googlegroups.com<javascript:> > . > To unsubscribe from this group, send email to > sqlalchemy+...@googlegroups.com <javascript:>. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > -- 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/-/PFyQtSPNu-0J. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
