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