Hi all,
I've been getting stuck in to web2py for a few weeks now, and really
enjoying it. I've hit a bit of a road block with this issue and though
I would post it here to get ideas on the cleanest solution.
Basically I am trying to combine a left outer join with a count in a
sql query. I can't see a combination of the built in ORM query methods
that allows me to to it.
An example of the type of query I am trying to implement is:
select message.id, author.id, count(comment.id)
from
message inner join author
on message.author_id = author.id
left outer join comment
on message.id = comment.message_id
group by message.id, author.id
So I need to:
- inner join message and author, to merge this data
- left outer join with comment, to get all related comments
- take a count of the comments
I end up with each message, the author of the message, and the count
of comments on each message
when using the db(query).select(...) syntax, there doesn't seem to be
a way to get a left outer join into the "query" component, only inner
joins (a.id == b.id etc). The "left" syntax must be part of the select
(...), but so must the "count".
There are a few alternatives to doing this all in a single query. I
could:
- Use db.executesql(), but to me that seems terribly fragile, as
accessing the result tuples is all order dependent (I would love to
hear about another way of using tuple results that is more robust)
- Do one query to take care of the inner join, and then dynamically
attach the comment count to each SQLRow object. Seems ugly as well,
and needs more database connections
- Link everything up in the view from two seperate row collections, by
matching IDs, but this seems like polluting the view
I think that pretty much covers the problem I am facing. I am happy to
post up clarifications if it will help people find an answer for this.
Thanks,
Eddie
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@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
-~----------~----~----~----~------~----~------~--~---
