Following this
lead
https://stackoverflow.com/questions/34708963/error-missing-from-clause-entry-for-table
I discovered that if we add double quotes to the tablename, for all
instances of the tablename, like this:
"TABLE_of_sites"
Then the query will work.
On Friday, July 14, 2017 at 1:48:10 AM UTC+8, lyn2py wrote:
>
> Table code:
> db.define_table('TABLE_of_sites',
> Field('site_id', 'reference sites'),
> Field('title', 'string'),
> Field('date','datetime'),
> ...
> Field('updated','datetime', update=request.now, writable=False),
> format='%(site_id)s'
> )
>
>
> In controller:
> table = db['TABLE_of_sites']
> rows = db(table).select(distinct=table.title)
>
> Gives the error:
>
> ProgrammingError: missing FROM-clause entry for table "table_of_sites"
> LINE 1: SELECT DISTINCT ON (TABLE_of_sites.title) "TABLE_of...
>
> ^
>
>
> I printed the sql query that resulted from the above code. It has the
> FROM-clause, so I don't know why it would give the error. This is the
> printed query:
> SELECT DISTINCT ON (TABLE_of_sites.title) "TABLE_of_sites"."id",
> "TABLE_of_sites"."site_id", "TABLE_of_sites"."date",
> "TABLE_of_sites"."title" FROM "TABLE_of_sites" WHERE ("TABLE_of_sites"."id"
> IS NOT NULL);
>
> I need to select 1 row for entries that appear multiple times for a
> particular title, hence I'm using distinct (or aggregates like max() -
> details in my previous post) but both are not working.
>
> Thank you for your time and assistance!
>
>
>
>
> On Tuesday, July 11, 2017 at 5:40:08 AM UTC+8, Massimo Di Pierro wrote:
>>
>> It took a year. Thanks to everybody who contributed. There are a ton of
>> bug fixes and speed improvements.
>>
>> I would encourage everybody who contributed to add a comment in this
>> thread explaining the main features/improvements they have made.
>>
>> If anything does not work or was broken it is only fault and I will
>> remedy if reported.
>>
>> Massimo
>>
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
