Web2py Postgresql Groupby issue

[Oasis Agano]

Hello

i have moved my connection to Postgres from sqlite and im facing this issue;

   ret = self.get_cursor().execute(command, *a[1:], **b)
ProgrammingError: column "water_flow_log.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  water_flow_log.id, water_flow_log.meterl, water_flow...

apparently its a well known issue;

my code looks like this

meter_fetch=db().select(db.meter.ALL,orderby=~db.meter.created_on)

s = db.water_flow_log.meterl
for mets in meter_fetch:
    per_meter=db(db.water_flow_log.meterl==mets.id).select(db.water_flow_log.ALL, orderby=db.water_flow_log.qty, groupby=s,limitby=(0,3))
    for sta in per_meter:
        s = db.water_flow_log.meterl
        per_meter_nog = db(db.water_flow_log.meterl == sta.meterl).select(db.water_flow_log.ALL,groupby=s,orderby=db.water_flow_log.date_made,limitby=(0,1))
        for stag in per_meter_nog:
            graph.add(stag.meter, stag.qty)


the error is on the red lines

any help please?

[Carlos Correia]

On 11-05-2017 15:22, Oasis Agano wrote:
> Hello
>
> i have moved my connection to Postgres from sqlite and im facing this issue;
>
> ret = self.get_cursor().execute(command, *a[1:], **b)
> ProgrammingError: column "water_flow_log.id" must appear in the GROUP BY clause or be used in an aggregate function
> LINE 1: SELECT water_flow_log.id, water_flow_log.meterl, water_flow...
>
> apparently its a well known issue;
>
> my code looks like this
>
> meter_fetch=db().select(db.meter.ALL,orderby=~db.meter.created_on)
>
> s = db.water_flow_log.meterl

Try to add the missing fields to 's':
s = db.water_flow_log.meterl + ',' + db.water_flow_log.id

> for mets in meter_fetch:
> per_meter=db(db.water_flow_log.meterl==mets.id).select(db.water_flow_log.ALL,
> orderby=db.water_flow_log.qty, groupby=s,limitby=(0,3))
> for sta in per_meter:
> s = db.water_flow_log.meterl
> per_meter_nog = db(db.water_flow_log.meterl ==
> sta.meterl).select(db.water_flow_log.ALL,groupby=s,orderby=db.water_flow_log.date_made,limitby=(0,1))
> for stag in per_meter_nog:
> graph.add(stag.meter, stag.qty)
>
>
> the error is on the red lines
>
> any help please?
>

> --
> 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+un...@googlegroups.com <mailto:web2py+un...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.


--
Com os melhores cumprimentos,

Carlos Correia
=========================
MEMÓRIA PERSISTENTE
Tel.: 218 471 841 (NOVO)
GSM: 917 157 146
e-mail: ge...@memoriapersistente.pt
URL: http://www.memoriapersistente.pt
XMPP (Jabber): car...@memoriapersistente.pt (NOVO)
GnuPG: wwwkeys.eu.pgp.net
URL Suporte: https://t5.m16e.com/gps

[Pierre]

here: http://web2py.com/books/default/search/29?search=groupby

s = db.table.field_a | db.table.field_b
groupby s

always obey the computer.....   :)