clean api is on DAL's side. Whenever a T-SQL query is easier to write (and read) than python code, exposing an harder-to-code-and-to-look-at python API is kinda a dealbreaker.
On Tuesday, December 9, 2014 8:24:30 AM UTC+1, Remco Boerma wrote: > > Thanks, i'll checkout the lateral joins. :) > > In the case of the clean API, the sql i produced is SQL-92 compatible, is > that clean enough? :) As for iterating over the results in python doesn't > seem like an all to valid option with some larger tables. (We use > Peoplesoft HR with years of history resulting in tables with millions of > rows like this) - Think i will stick to crafted SQL for now! > > Remco > > > > Op maandag 8 december 2014 19:20:20 UTC+1 schreef Niphlod: >> >> given that the aliases are not supported in master-sub selects (i.e. >> they're coded with the same level of "aliasing" in mind, whereas your >> select needs to propagate a carefully crafted alias in some place), the >> problem relies on the fact that subselect are only allowed to have multiple >> "conditions" to be specified. >> Moreover, what you're actually lacking (or, from another perspective, DAL >> is lacking) is access to window functions >> In this particular case it's not needed, and you could ,e.g., use a Set >> to specify a two-column resultset that needs to be matched with another >> table to give you the result, we'll be ok.... The first set would be a >> (emp_id, max_date) retrieved with a groupby on the emp_id and the second >> set to match would be the (id, date) tuple to use to match the name. >> >> You can do it with two selects and merging sets with python, or.... >> figure out a clean API that is supported on most of the backends :-P >> >> >> BTW: check the "LATERAL" joins that are available in postgresql 9.3 ... >> will probably blow your head >> > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.