no probl. To elaborate this further for others having the same issue.... People (programmers, often) tend to forget that db engines are programs. They have their own algorithms too (and they take time). It's true that they have pretty rock-solid algos and do their work very fast, but having python filtering rows objects sometimes is even faster than specifying some super-duper "where" filters in the query itself. When the "super-duper" where is going to cut out the 90% of 10000 rows returned it can be safer to look into the database algos, but if you're going to fetch a (relatively small) set and cut it out (with filter() or search()) by the 20%, it's generally a good advice to do that in plain python. As a general "rule of thumb", condition based on a function like yours (the db should have been forced to calculate the date whose difference is now - the number of days in another column, always variable) is the best scenario where this kind of things is high valuable, i.e. probably faster in python than the db (assuming that you're able to write that in plain T-SQL).
I actually had this kind of problem and (I'm a professional db developer and not a python programmer) tried to write a super-optimized query (was a scoring system based on pretty complicated combined averages). When I implemented the same logic in plain python, other than being far more readable and maintainable, I cut off execution times from ~25 min to ~47 sec. On Thursday, July 26, 2012 6:03:24 PM UTC+2, jw wrote: > > cards2learn.exclude(lambda row: row.userCard.lastTimeLearned > date.today > () - timedelta(days=row.userCard.stage)) > > Niphlod, you made my day!! Thank you so much! And thank you all for the > advices! > > > On Thursday, July 26, 2012 5:52:29 PM UTC+2, Niphlod wrote: >> >> Currently you'd have to code a view in the database (or simply a "string" >> condition to pass on) to achieve that. >> >> date.today() - timedelta(something) is a python "fixed" value that >> doesn't get re-evaluated at runtime by the database substituting the >> "something" for each value of every row. >> >> If you need the structure to be portable and absolutely need to have a >> single query fetching all of this, you need to change your db structure. >> >> If you're going to run always on a "fixed" db engine you could write >> something exploiting the functionalities of that db engine. >> >> Remember that most of the times is easier (and the resources negligible) >> to fetch rows from the db and filter them with python. >> >> cards2learn = db((db.groupCards.card_id == db.card.id) \ >> & (db.groupCards.studyGroup_id == auth.user.studyGroup_id) \ >> & (session.chosenSubject_id == db.card.subject_id) \ >> & (db.userCard.card_id == db.card.id) >> & (db.userCard.stage <= 5) \ >> #& (db.userCard.lastTimeLearned < (date.today() - >> timedelta(days=(1)))) >> ).select(db.userCard.ALL,db.card.ALL,db.groupCards.ALL, left=db. >> userCard.on((db.userCard.card_id == db.card.id))) >> >> cards2learn.exclude(lambda row: row.usercard.lasttimelearned < request.now >> - timedelta(days=row.usercard.stage)) >> >> >> >> >> >> should work. (totally untested) >> >> On Thursday, July 26, 2012 5:23:51 PM UTC+2, jw wrote: >>> >>> Ok guys... I did step for step and now the query works for the stage >>> (progress). >>> The second thing (stage is multiplicator for time) I have no glue how to >>> move on. >>> >>> cards2learn = db((db.groupCards.card_id == db.card.id) \ >>> & (db.groupCards.studyGroup_id == auth.user.studyGroup_id) \ >>> & (session.chosenSubject_id == db.card.subject_id) \ >>> & (db.userCard.card_id == db.card.id) >>> & (db.userCard.stage <= 5) \ >>> & (db.userCard.lastTimeLearned < (date.today() - >>> timedelta(days=(1)))) >>> ).select(db.userCard.ALL,db.card.ALL,db.groupCards.ALL, >>> left=db.userCard.on((db.userCard.card_id == db.card.id))) >>> >>> timedelta(days=(1)) should be timedelta(days=(db.userCard.stage)) but it >>> says: >>> <type 'exceptions.TypeError'> unsupported type for timedelta days >>> component: Field >>> How do I use the db.userCard.stage in timedelta (stage is an integer >>> field in the table)? >>> >>> Many thanks for the previous answers! >>> >> --
