Yes, you got it...
On Sat, Mar 23, 2013 at 9:33 AM, Niphlod <[email protected]> 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 <[email protected]> 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 [email protected]. > 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.
