Now we just need to see if Massimo will take the patch!
On Mar 13, 12:50 am, Mengu <whalb...@gmail.com> wrote:
> 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" <nat...@freezable.com> 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 <whalb...@gmail.com> 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" <nat...@freezable.com> 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 <whalb...@gmail.com> wrote:
>
> > > > > hi mr. freeze,
>
> > > > > please go with it, i didn't start doing anything. :)
>
> > > > > On 11 Mart, 22:52, "mr.freeze" <nat...@freezable.com> 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 <mdipie...@cs.depaul.edu> 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" <nat...@freezable.com> 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 <thade...@thadeusb.com>
> > > > > > > > wrote:
>
> > > > > > > > > I like it, much cleaner, and what I would expect.
>
> > > > > > > > > -Thadeus
>
> > > > > > > > > On Thu, Mar 11, 2010 at 9:18 AM, Mengu <whalb...@gmail.com>
> > > > > > > > > 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
> > > > > > > > > > web...@googlegroups.com.
> > > > > > > > > > To unsubscribe from this group, send email to
> > > > > > > > > > web2py+unsubscr...@googlegroups.com.
> > > > > > > > > > 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 web...@googlegroups.com.
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
