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 [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.
