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.
