Tried your solution but unfortunately didn't work. But, even if I made it work, isn't it still using executesql in the end?
On Monday, February 29, 2016 at 7:17:24 PM UTC-5, Val K wrote: > > Yes, it's impossible by one dal-query, but still you can use web2py power: > > args = Storage( > num_q = db(db.question.quiz_id == > db.quiz.id)._select(db.quiz.ALL, > 'count(*) as num_q' , groupby=[db.quiz.id]), > num_passed = db(db.result.quiz_id == db.quiz.id)._select(db. > quiz.id, 'count(*) as num_passed', groupby=[db.quiz.id]), > sql_str = 'SELECT num_q.*, num_passed.num_passed FROM (%( > num_q)s) AS num_q JOIN (%(num_passed)s) AS num_passed ON num_q.quiz_id= > num_passed.quiz_id' > ) > > rows = db.executesql(args.sql_str % args, fields = list(db.tbl_holder)) > > > > > > > > > > > > > On Monday, February 29, 2016 at 9:33:31 PM UTC+3, 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.
