I'm seeing an odd (bug?) issue with performing selects with alias'd joins, using a postgres db.
db.define_table('fqdn_word_part', Field('word', 'string', unique=True) ) db.define_table('fqdn_word_combine', Field('fqdn_id', 'reference fqdn'), Field('fqdn_word_part_id', 'reference fqdn_word_part'), Field('place', 'integer') ) aliases created; >>> first <Table first (id,fqdn_id,fqdn_word_part_id,place)> >>> second <Table second (id,fqdn_id,fqdn_word_part_id,place)> >>> firstp <Table firstp (id,word)> >>> secondp <Table secondp (id,word)> ...selecting a record: db(firstp.word == 'google').select(firstp.word, join=(first.on(first.fqdn_word_part_id==firstp.id))) ...generates the correct SQL: SELECT firstp.word FROM fqdn_word_part AS firstp JOIN fqdn_word_combine AS first ON (first.fqdn_word_part_id = firstp.id) WHERE (firstp.word = 'google'); ...selecting a record using a self-join: db(firstp.word == 'google')(secondp.word == 'com').select(firstp.word, secondp.word, join=(first.on(first.fqdn_word_part_id==firstp.id),second.on(second.fqdn_word_part_id==secondp.id))) ...generates SQL with a syntax error: SELECT firstp.word, secondp.word FROM fqdn_word_part AS firstp, fqdn_word_part AS secondp JOIN fqdn_word_combine AS first ON (first.fqdn_word_part_id = firstp.id) JOIN fqdn_word_combine AS second ON (second.fqdn_word_part_id = secondp.id) WHERE ((firstp.word = 'google') AND (secondp.word = 'com')); ProgrammingError: ('ERROR', '42P01', 'invalid reference to FROM-clause entry for table "firstp"') I can re-arrange the SQL to work correctly: SELECT firstp.word, secondp.word FROM fqdn_word_part AS firstp JOIN fqdn_word_combine AS first ON (first.fqdn_word_part_id = firstp.id), fqdn_word_part AS secondp JOIN fqdn_word_combine AS second ON (second.fqdn_word_part_id = secondp.id) WHERE ((firstp.word = 'google') AND (secondp.word = 'com')); Is there a more effective way to do this with the DAL? -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- 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.