the new dal will also allow an alternate simple notation that does not require the _extra. ;-)
On May 31, 9:25 am, eddie <[email protected]> wrote: > 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/2a907a7823... > > 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 <[email protected]> wrote: > > > 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 -~----------~----~----~----~------~----~------~--~---
