I think this is what you want:
>>> db.define_table('words',SQLField('key'),SQLField('parent','integer'))
>>> w2=db.words.with_alias('w2')
>>> rows=db(db.words.key.belongs(('key1','key2','key3'))).select(db.words.key,w2.id.count(),left=w2.on(w2.parent==db.words.id))
which generates:
SELECT words.key, COUNT(w2.id) FROM words LEFT JOIN words AS w2 ON
w2.parent=words.id WHERE words.key IN ('key1','key2','key3');
and then you extract the data with
>>> for row in rows: print row.words.key, row._extra[w2.id.count()]
Massimo
On Dec 19, 11:17 am, mdipierro <[email protected]> wrote:
> You can do IN
>
> db(db.w1.key.belongs(('key1','key2','key3')).select()
>
> You can do nested selects
>
> db().select(db.w1.key,db(db.w2.parent==db.w1.id)._select
> (db.w2.id.count()))
>
> I suspect in your example w1 and w2 are the same table. I am not sure
> you can do this. I may need a minor change to the DAL. let me look
> into this.
>
> Massimo
>
> On Dec 19, 2:19 am, mmstud <[email protected]> wrote:
>
> > Im using mysql backend and like to do this sort of query, is it
> > possible with DAL?
>
> > SELECT w1.key, (SELECT COUNT(*) FROM words w2 WHERE w2.parent = w1.id)
> > AS childs
> > FROM words w1
> > WHERE w1.key IN ('key1', 'key2', 'key3')
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
