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 ?
On Friday, March 22, 2013 11:41:06 PM UTC+1, Tito Garrido wrote:
>
> 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
>
>
>
>
> On Fri, Mar 22, 2013 at 7:19 PM, Niphlod <[email protected]
> <javascript:>>wrote:
>
>> written as it is, in a view :P
>>
>> we need models, test data and the resultset you want returned, pleeeease
>> ^_^
>>
>>
>> On Friday, March 22, 2013 11:00:12 PM UTC+1, Tito Garrido wrote:
>>>
>>> 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).:_______
>>>
>> --
>>
>> ---
>> 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] <javascript:>.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
>
>
>
> --
>
> Linux User #387870
> .........____
> .... _/_õ|__|
> ..º[ .-.___.-._| . . . .
> .__( o)__( o).:_______
>
--
---
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/groups/opt_out.
