Here it is: <class 'psycopg2.ProgrammingError'> column "meet_stage.name" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...es_stage.name), COUNT(DISTINCT times_stage.club), meet_stage... ^
I guess this is a psycopg2 error. Which is kinda weird because when I take the SQL and feed it directly into the Postgres sql tool it figures out that "meet_stage.name" has a 1:1 relationship and runs fine. I found that putting all the columns in the "group by" was not as toxic as it first seemed and it is working OK for me now. The query is still very slow, but this is a seldom-used housekeeping part of the site and speed is not as important... Joe On Monday, July 15, 2013 4:09:23 PM UTC-7, Niphlod wrote: > > I don't remember any DAL code that prevents something like that. From a > theoretical standpoint though, every column that is not aggregated in the > returned resultset in a group by clause must be included in the groupby > itself. > Can you post the DAL error ? > > On Tuesday, July 16, 2013 12:24:51 AM UTC+2, Joe Barnhart wrote: >> >> I find I want to do SQL queries that I can't figure out the DAL commands >> for. For example, using a Postgres database I want to do a three table >> join where: >> >> Table A <--1:1--> Table B <--1:N --> Table C >> >> I'm using a "group by" on the Table B/C join and aggregating the Table C >> columns for my final table. My problem is that, even though Table A/B is >> 1:1, the DAL is insisting on aggregation functions on it as well, or >> demanding I put its columns in the "group by" which for some reason makes >> the query die (i.e. take too long to be useful). >> >> I know from entering the sql directly into Postgres that it figures out >> the Table A columns don't need any aggregation, but web2py is more >> restrictive. (Possibly because other databases aren't as forgiving as >> postgres.) Since it is a query and returns rows, I can't use executesql() >> on it. I can do it as two queries and "join" in memory but that seems like >> a hassle when SQL was designed to handle this very situation. >> >> The query I want is something like this: >> >> SELECT A.col1, B.col1, count(C.col1) >> FROM A, B, C >> WHERE A.b_id=B.id AND C.b_id=B.id >> GROUP BY B.id >> >> When i try the equivalent in DAL I always get the error that I need to >> put A.col1 in the "group by" clause, but that kills the query on the >> psycopg side. Plus, the query actually has many more columns than this but >> the underlying structure is the same. >> >> Do I need to resort to two queries (Table A/B and Table B/C) and do the >> final table joining from Python? Or is there some DAL-fu I'm missing? >> >> -- Joe B. >> >> -- --- 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.
