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.
