alright then, this is really great. :) i'm happy that i could contribute to web2py. :)
thanks mr.freeze. On 13 Mart, 08:42, "mr.freeze" <[email protected]> wrote: > I think so. I tested on a similar query: > > db.define_table('post', > Field('title'),Field('body')) > > db.define_table('post_comment', > Field('post_id', db.post), > Field('comment', 'text')) > > def mengu(): > rows = > db(db.post_comment.post_id==db.post.id).select("COUNT(post.id) AS > postcount", > db.post.id, > db.post.title, > orderby="postcount > DESC", > groupby=db.post.id) > return dict(rows=rows) > > It outputs this SQL: > > SELECT COUNT(post.id) AS postcount, post.id, post.title FROM post, > post_comment WHERE post_comment.post_id=post.id GROUP BY post.id ORDER > BY postcount DESC; > > You can also get the same effect with: > > postcount = db.post.id.count().with_alias('postcount') > rows = db(db.post_comment.post_id==db.post.id)._select(postcount, > db.post.id, > db.post.title, > orderby='postcount > DESC', > groupby=db.post.id) > > On Mar 13, 12:21 am, Mengu <[email protected]> wrote: > > > > > mr. freeze, > > > does also the following query work like in my patch? > > > db(db.vote.question_id==db.question.id).select("COUNT(vote.id) AS > > votecount", db.question.id, db.question.title, orderby="votecount > > DESC", groupby=db.question.id) > > > thanks. you are welcome btw :) > > > On 13 Mart, 06:05, "mr.freeze" <[email protected]> wrote: > > > > I sent Massimo a patch for sql.py. I held off patching dal.py since I > > > am unfamiliar with the new structure. I figured Massimo would rather > > > do it himself based on the sql.py patch (which is very small) rather > > > than fix my code. > > > > It seems to work well, allowing you to cast any expression into a new > > > variable: > > > totals = > > > db(db.things.id>0).select(db.things.amount.sum().with_alias('total')) > > > print totals.first().total > > > > Thanks Mengu. > > > > On Mar 11, 2:57 pm, Mengu <[email protected]> wrote: > > > > > hi mr. freeze, > > > > > please go with it, i didn't start doing anything. :) > > > > > On 11 Mart, 22:52, "mr.freeze" <[email protected]> wrote: > > > > > > Mengu, one made one small change to your code for backwards > > > > > compatibility (putting the value back into _extra). Other than that, > > > > > I added a with_alias method to Expression so you can do: > > > > > > rows = > > > > > db(db.stuff.id>0).select(db.stuff.value.count().with_alias('mycount')) > > > > > print rows.first().mycount > > > > > > I will make patches for sql.py and dal.py that include your code > > > > > unless you have already started. > > > > > > On Mar 11, 10:16 am, mdipierro <[email protected]> wrote: > > > > > > > as is a reserved keyword in Python. We need to use with_alias > > > > > > instead > > > > > > (we do it for Table already). I'd take a patch but this needs to be > > > > > > tested, I am not sure what it may break. Also we need to patch both > > > > > > sql.py and dal.py and they are different. > > > > > > > Massimo > > > > > > > On Mar 11, 10:08 am, "mr.freeze" <[email protected]> wrote: > > > > > > > > I like this too. Could we go one step further and add an 'as' to > > > > > > > Field class? > > > > > > > > db.table.field.as('t') > > > > > > > > On Mar 11, 9:35 am, Thadeus Burgess <[email protected]> wrote: > > > > > > > > > I like it, much cleaner, and what I would expect. > > > > > > > > > -Thadeus > > > > > > > > > On Thu, Mar 11, 2010 at 9:18 AM, Mengu <[email protected]> > > > > > > > > wrote: > > > > > > > > > Anyone has any other way for writing that query? > > > > > > > > > > Massimo, wouldn't you accept this as a patch? > > > > > > > > > > -- > > > > > > > > > You received this message because you are subscribed to the > > > > > > > > > Google Groups "web2py-users" group. > > > > > > > > > To post to this group, send email to [email protected]. > > > > > > > > > To unsubscribe from this group, send email to > > > > > > > > > [email protected]. > > > > > > > > > For more options, visit this group > > > > > > > > > athttp://groups.google.com/group/web2py?hl=en. -- You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
