By the way, this work for queries like:
top_votes = db(db.vote.question_id==db.question.id).select("title AS
t", db.question.title, groupby=db.question.id)
so you can access t with top_votes[0].t
On 11 Mart, 06:38, Mengu <whalb...@gmail.com> wrote:
> Hi all,
>
> I have two write this query:
>
> "SELECT COUNT( vote.id ) AS votecount, question.id, question.title
> FROM vote, question
> WHERE vote.question_id = question.id
> GROUP BY question.id
> ORDER BY votecount DESC"
>
> And I have came up with:
>
> top_votes =
> 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)
>
> This generates the sql above with no problem. however "COUNT(vote.id)
> AS votecount" is becoming a part of _extra attribute and in order to
> access it I have to write, for example,
> top_votes[0]._extra["COUNT(vote.id) AS votecount"] which is not
> pretty. So I have made an addition to sql.py:
>
> it was like this before: (from line 3068 to 3072)
> if not table_field.match(colnames[j]):
> if not '_extra' in new_row:
> new_row['_extra'] = Row()
> new_row['_extra'][colnames[j]] = value
> continue
>
> and it is now like this:
> if not table_field.match(colnames[j]):
> if not '_extra' in new_row:
> new_row['_extra'] = Row()
> # if there is a selection like "COUNT(vote.id) AS
> votecount", parse it and make votecount
> # an attribute of the row itself.
> select_as_parser = re.compile("\s+AS\s+(\S+)")
> new_column_name =
> select_as_parser.search(colnames[j])
> if new_column_name is None:
> new_row['_extra'][colnames[j]] = value
> else:
> column_name = new_column_name.groups(0)
> setattr(new_row, column_name[0],
> value)
> continue
>
> so in my case, "COUNT(vote.id) AS votecount" in the selection,
> votecount is becoming an attribute of my rows. i'm happy with it.
>
> please let me know if there is any other way to write this query.
--
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.
