Help on a web2py query

[Tito Garrido]

Folks,

How could I implement:

SELECT * FROM (SELECT * FROM capitulo WHERE data >= CURDATE() ORDER BY data) WHERE encerrada = false GROUP BY c.cod_anothertable

Regards,

Tito

--

Linux User #387870
.........____
.... _/_õ|__|
..º[ .-.___.-._| . . . .
.__( o)__( o).:_______

[Niphlod]

written as it is, in a view :P

we need models, test data and the resultset you want returned, pleeeease ^_^

[Tito Garrido]

db.define_table('novela',
    Field('nome', requires=IS_NOT_EMPTY()),
    Field('emissora', 'reference emissora', requires = IS_IN_DB(db,db.emissora.id,'%(nome)s')),
    Field('encerrada', 'boolean', default=False),
    Field('slug', requires=IS_SLUG()),
    Field('logo', 'upload', uploadseparate=True, autodelete=True),
    Field('banner', 'upload', uploadseparate=True, autodelete=True),
    Field('sinopse', 'text', requires=IS_NOT_EMPTY()),
    Field('data_de_inicio', 'date', requires = IS_DATE(format=T('%d/%m/%Y'), error_message='must be DD/MM/AAAA!')),
    Field('data_de_fim', 'date', requires = IS_DATE(format=T('%d/%m/%Y'), error_message='must be DD/MM/AAAA!')),
    )


db.define_table('capitulo',
    Field('novela', 'reference novela', requires = IS_IN_DB(db,db.novela.id,'%(nome)s')),
    Field('data_de_exibicao', 'date', default=datetime.date.today(), requires = IS_DATE(format=T('%d/%m/%Y'), error_message='must be DD/MM/AAAA!')),
    Field('titulo', requires=IS_NOT_EMPTY()),
    Field('resumo', 'text'),
    )

I would like to select the 3 last "capitulos" ordered by "data_de_exibicao", but it must be from different "novela"

This works:
db.executesql("SELECT * FROM (SELECT * FROM capitulo WHERE data_de_exibicao >= CURDATE() ORDER BY data_de_exibicao) AS c INNER JOIN novela n ON c.novela = n.id WHERE encerrada = false GROUP BY c.novela limit 0,3;",as_dict=True)

But I would lose the ability to get something like:

capitulo.novela.logo

Is there a way to execute the query above using DAL?

Regards,

Tito

--
 
---
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 web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Niphlod]

so, you want the last 3 capitulo rows, for each novela, and for each row you want to retrieve also all the data from the novela "attached" to each capitulo.....all in a single select ?

[Tito Garrido]

Yes, you got it...

[Niphlod]

your original query retrieves only 3 rows, not the last 3 rows for each capitulo....