your original query retrieves only 3 rows, not the last 3 rows for each
capitulo....
On Saturday, March 23, 2013 7:14:01 PM UTC+1, Tito Garrido wrote:
>
> Yes, you got it...
>
>
> On Sat, Mar 23, 2013 at 9:33 AM, Niphlod <nip...@gmail.com
> <javascript:>>wrote:
>
>> 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 <nip...@gmail.com> 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 web2py+un...@**googlegroups.com.
>>>>
>>>> For more options, visit
>>>> https://groups.google.com/**groups/opt_out<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 web2py+un...@googlegroups.com <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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
