Hi Massimo -- Maybe it's the psycopg2 adapter, but it's throwing the error in the message above because there's no aggregate function on A.col1. It forces me to put the column into the "group by" clause. I'm thinking maybe my tables aren't as clean as I thought and maybe postgres is telling me that Table A and Table B are not 1:1 as I thought.
Every day is an education! -- Joe On Monday, July 15, 2013 5:05:33 PM UTC-7, Massimo Di Pierro wrote: > > This query > > 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 > > in DAL-ese is > > A, B, C = db.A, db.B, db.C > rows = > db((A.b_id==B.id)&(C.b_id==B.id)).select(A.col1,B.col1,C.col1.count(),groupby=B.id) > > If it works with executesql than it works with DAL-ese. > > > > On Monday, 15 July 2013 17:24:51 UTC-5, 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.
