From: Philippe N. <sw...@fr...> - 2004-06-02 12:08:23
|
Hi, I need to sort and count SQLObjects per date. An example might really show what I want :) class Article(SQLObject): date = DateTimeCol() ... I have many many Articles, and I'd like to build a Calendar showing how many articles were published by month and/or year. For instance: 10 articles on 05/22 3 articles on 05/14 I see one durty solution which would like this (untested) : calendar = {} # keys: day numbers of the month for article in Article.select(orderBy=Article.q.date): if article.getYear() != 2004 or article.getMonth() != 5: continue try: calendar[article.getDay()] += 1 except KeyError: calendar[article.getDay()] = 1 it isn't really efficient because it fetches all articles. Is there a clean way to select SQLObjects given a specific date interval on DateTimeCol ? Philippe |
From: Neil Blakey-M. <nb...@mi...> - 2004-06-02 12:14:17
|
On Wed 2004-06-02 (14:05), Philippe Normand wrote: > I see one durty solution which would like this (untested) : > > calendar = {} # keys: day numbers of the month > for article in Article.select(orderBy=Article.q.date): > if article.getYear() != 2004 or article.getMonth() != 5: > continue > try: > calendar[article.getDay()] += 1 > except KeyError: > calendar[article.getDay()] = 1 > > > it isn't really efficient because it fetches all articles. Is there a clean > way to select SQLObjects given a specific date interval on DateTimeCol ? I just compare them to a datetime, and that works just fine for me. Although, there's a bug where the datetime conversion uses '%s' instead of '%S' for seconds, which probably wouldn't affect your usage. a = sql.table.acct if start_date: criteria.append(a.stamp_inserted >= start_date) if end_date: criteria.append(a.stamp_inserted < end_date) Neil -- Neil Blakey-Milner nb...@mi... http://mithrandr.moria.org/ |
From: Philippe N. <sw...@fr...> - 2004-06-02 13:18:34
|
On Wed, Jun 02, 2004 at 02:13:58PM +0200, Neil Blakey-Milner wrote: > > > > > > it isn't really efficient because it fetches all articles. Is there a clean > > way to select SQLObjects given a specific date interval on DateTimeCol ? > > I just compare them to a datetime, and that works just fine for me. > Although, there's a bug where the datetime conversion uses '%s' instead > of '%S' for seconds, which probably wouldn't affect your usage. > > a = sql.table.acct > if start_date: > criteria.append(a.stamp_inserted >= start_date) > if end_date: > criteria.append(a.stamp_inserted < end_date) > But it doesn't change the problem, too many objects are fetched from the db. Or am I missing something ? I'd like to compare dates directly in select() and not in select() results. Following your code snippet, I'll try something like: table.select((Table.q.stamp_inserted >= start_date) & (Table.q.stamp_inserted < end_date)) Cheers, Philippe |
From: Neil Blakey-M. <nb...@mi...> - 2004-06-02 13:24:59
|
On Wed 2004-06-02 (15:15), Philippe Normand wrote: > > a = sql.table.acct > > if start_date: > > criteria.append(a.stamp_inserted >= start_date) > > if end_date: > > criteria.append(a.stamp_inserted < end_date) > > > > But it doesn't change the problem, too many objects are fetched from the db. > Or am I missing something ? > > I'd like to compare dates directly in select() and not in select() results. > Following your code snippet, I'll try something like: > > table.select((Table.q.stamp_inserted >= start_date) & > (Table.q.stamp_inserted < end_date)) Sorry, I tend to build up a criteria list, and go: table.select(AND(*criteria)) So yes, it will work for select queries just fine. Just mind the bug if seconds are at all important to you. Neil -- Neil Blakey-Milner nb...@mi... http://mithrandr.moria.org/ |