it may be a little discouraging but please don't waste any more time on getting it with DAL syntax and use an executesql right away. We'll probably never reach the point where we'll be able to express such intricacies with a DAL syntax that is compatible to every supported backend. When those kind of requirements resurface, I just use executesql or a view with a "fake" definition if in proper need of some kind of abstraction.
On Monday, February 29, 2016 at 7:33:31 PM UTC+1, Bill Black wrote: > > // I've left out fields that are irrelevant to the problem > > Table quiz | name (varchar) > Table question | quiz_id (fk) > Table result | quiz_id (fk), passed (boolean) > > quiz - question, quiz - result are one to many relations. > > I'm trying to get the 'quiz name, # of questions in the quiz, # of passed > (result) for the quiz' > > > On Monday, February 29, 2016 at 12:43:41 PM UTC-5, Val K wrote: >> >> Hi! >> There are many aliases with sub select in your query, it's difficult to >> understand tables structure. >> Post tables definition and some comment about what do you want to do, >> please >> >> >> On Monday, February 29, 2016 at 8:11:09 AM UTC+3, Bill Black wrote: >>> >>> SELECT a.*,b.num_passed >>> FROM (SELECT q.id,q.name,count(t.id) AS num_q >>> FROM quiz AS q >>> JOIN question AS t ON q.id=t.quiz_id GROUP BY q.id) >>> AS a >>> JOIN (SELECT quiz_id,count(passed) AS num_passed FROM result GROUP BY >>> quiz_id) >>> AS b ON a.id=b.quiz_id; >>> I need to join these two selects. >>> I've spent all morning trying to get it to work in dal. But I don't >>> think it can be done... >>> Just posting here in case there is a way to do this before resorting to >>> executesql. >>> Thanks. >>> >> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
