Not sure why you need to do that... I don't see how to you created your
"alias" though...
I guess you do : first = db.fqdn_word_part
Did you read about .with_alias()??
Richard
On Mon, Oct 7, 2013 at 7:29 PM, Alex W <alex...@gmail.com> wrote:
> 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.
>
--
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.
