Thanks Massimo. Looking over the cookbook examples, I realise now the example of the left parameter pretty much covers this, it just helps to have it pointed out :)
While I'm here, I thought I'd post a related solution that I found useful. Once I got my counts coming through to the view, I was stuck on how to actually access the results. This post was very useful: http://groups.google.com/group/web2py/browse_thread/thread/2a907a7823af0d71/8350d9fc42f24cff?lnk=gst Basically the count column is not a table field, so you need to access these values using the _extra dictionary of each row. On May 29, 11:10 pm, mdipierro <mdipie...@cs.depaul.edu> wrote: > On May 29, 3:41 am, eddie <eddie.sh...@gmail.com> 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 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 -~----------~----~----~----~------~----~------~--~---
