On May 29, 3:41 am, eddie <[email protected]> wrote:
> 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
I cannot try it without tables but I think you want this
db(db.message.id==db.comment.message_id).select
(db.message.id,db.author.id,db.comment.id.count(),left=db.author.on
(db.message.author_id==db.author.id),groupby=db.comment.id|
db.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 [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
-~----------~----~----~----~------~----~------~--~---
