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