that psycopg error is a postgresql error. I find very strange that a query run through psycopg gives you an error while the "other tool" doesn't. However, as stated earlier, in a query with a group by you "must" (read, should, even if some tool outsmarts the relationships) fetch either aggregates or the columns in the group by clause. That's T-SQL.
Il giorno martedì 16 luglio 2013 06:21:03 UTC+2, Joe Barnhart ha scritto: > > 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.
