Hello Martin, I agree this is allowed in SQL. If an expression is allowed in SQL you can wrap it in Expression (from gluon.dal import Expression) and pass it in place of any parameter. Yet if you chose to do it you introduce a dependency on the specific SQL dialect. The problem with passing “true” is exactly that.
If you have a boolean value instead of an expressions DAL wants you to resolve it at the Python level since there is no need to pass it to the database (it would work but would introduce extra un-necessary work). This is why DAL does not provide a way to pass boolean conditions but only queries. You suggest: > if boolean1: > q |= db.table.field1 > 1 > if boolean2: > q |= db.table.field2 > 1 That’s the best way to do it in my mind. Massimo On Jan 3, 2015, at 8:39 AM, Martin Weissenboeck <mweis...@gmail.com> wrote: > Hi Massimo, thank you very much for this detailed explanation. > > About the "boolean query": > You are right (of course :-)), that a query like > (db.mytable.field>'C') & b > with b as a boolean expression is not mentioned in the manual. > But I think it is not forbidden in SQL and it coulld be useful. > > What I want is to switch on or off some parts of a query. A simplified > example: > q = ((db.table.field1>1)&boolean1) | ((db.table.field2>1)&boolean2) ... > > This would also work: > if boolean1: > q |= db.table.field1 > 1 > if boolean2: > q |= db.table.field2 > 1 > .... > Maybe this would be a better solution and it does not need a "& boolean1"... > But I need an inital value for q and now I have the same problem: I need a > q = False > at the beginning. What shall I do? Use a silly query like db.table.id==0 > which would be always false? > > Or is there another solution without these problems? > > Therefore: maybe it would be possible to add two querys, one giving always > False, the other True? > > About groupby: > > sqlite does not like "distinct" (lines 1 and 3), but groupby works now as > expected (line 6). sqlite and postgres are now compatible to each other > (lines 2 and 6). > Testtables > > # Datebase Command Result > 0 sqlite db(db.mytable.id>0).select( orderby=db.mytable.field2, > groupby=db.mytable.field2) > mytable.id mytable.field1 mytable.field2 > 2 B x > 5 E y > 1 sqlite db(db.mytable.id>0).select( orderby=db.mytable.field2, > distinct=db.mytable.field2) near "ON": syntax error > 2 sqlite db(db.mytable.id>0).select(db.mytable.field2, > orderby=db.mytable.field2, groupby=db.mytable.field2) > mytable.field2 > x > y > 3 sqlite db(db.mytable.id>0).select(db.mytable.field2, > orderby=db.mytable.field2, distinct=db.mytable.field2) near "ON": syntax > error > 4 postgres db(db.mytable.id>0).select( orderby=db.mytable.field2, > groupby=db.mytable.field2) column "mytable.id" must appear in the GROUP > BY clause or be used in an aggregate function LINE 1: SELECT mytable.id, > mytable.field1, mytable.field2 FROM myta... ^ > 5 postgres db(db.mytable.id>0).select( orderby=db.mytable.field2, > distinct=db.mytable.field2) > mytable.id mytable.field1 mytable.field2 > 1 A x > 3 C y > 6 postgres db(db.mytable.id>0).select(db.mytable.field2, > orderby=db.mytable.field2, groupby=db.mytable.field2) > mytable.field2 > x > y > 7 postgres db(db.mytable.id>0).select(db.mytable.field2, > orderby=db.mytable.field2, distinct=db.mytable.field2) > mytable.field2 > x > y > > > 2015-01-03 14:03 GMT+01:00 Massimo Di Pierro <massimo.dipie...@gmail.com>: > These are not valid DAL expressions: > > db((db.mytable.field1>'C') & 1).select() > db((db.mytable.field1>'C') & (1==1)).select() > db((db.mytable.field1>'C') & 'True').select() > db((db.mytable.field1>'C') & 'TRUE').select() > > the argument of db(....) must be a query or a logical expression comprised of > queries. 1, True, 'True', 'TRUE' are not queries. The fact that occasionally > you do not get an error is purely accidental. You should not use these > expressions. Nowhere in the manual we say this is allowed. Future versions of > web2py may check (and should) and will block them. > > In the other case the problem is that the query should be: > > db(db.mytable.id>0).select(db.mytable.field2, orderby=db.mytable.field2, > groupby=db.mytable.field2) > > and it should work in both cases. > > If you do not specify that you are selecting only field2, they you are > selecting all fields and the query becomes ambiguous (which id should be used > for the grouped records?). Sqlite does not care and returns a random one. > postgresql checks and complains. Apparently this was solved in SQL3 but most > engines do not follow SQL3. > > http://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function > > The standard SQL distinct syntax (in DAL: db(...).select(...,distinct=True)) > does not solve this problem. So the postgresql folks introduced DISTINCT ON > (in DAL: db(...).select(..., distinct=field)) but this is a postgresql > specific API. > > http://www.postgresqlforbeginners.com/2010/11/sql-distinct-distinct-on-and-all.html > > Massimo > > > > On Saturday, 3 January 2015 04:42:14 UTC-6, mweissen wrote: > In the last hour I have written a small application which tests (1) the > boolean expressions and (2) distinct/groupby. You can find this app as attach > file. > > (1) sqlite allows True or 1 or '1' as boolean expression, postgres works only > with "TRUE" or 'TRUE' > > (2) sqlite does not understand distinct and postgres need some complex > orderby. > > > Testtables > > Datebase Command Result > sqlite db((db.mytable.field1>'C') & (1==1)).select() > mytable.id mytable.field1 mytable.field2 > 4 D y > 5 E y > sqlite db((db.mytable.field1>'C') & True).select() > mytable.id mytable.field1 mytable.field2 > 4 D y > 5 E y > sqlite db((db.mytable.field1>'C') & 1).select() > mytable.id mytable.field1 mytable.field2 > 4 D y > 5 E y > sqlite db((db.mytable.field1>'C') & '1').select() > mytable.id mytable.field1 mytable.field2 > 4 D y > 5 E y > sqlite db((db.mytable.field1>'C') & 'TRUE').select() no such column: > TRUE > sqlite db((db.mytable.field1>'C') & "TRUE").select() no such column: > TRUE > postgres db((db.mytable.field1>'C') & (1==1)).select() argument of AND > must be type boolean, not type integer LINE 1: ...le.field2 FROM mytable > WHERE ((mytable.field1 > 'C') AND 1); ^ > postgres db((db.mytable.field1>'C') & True).select() argument of AND > must be type boolean, not type integer LINE 1: ...le.field2 FROM mytable > WHERE ((mytable.field1 > 'C') AND 1); ^ > postgres db((db.mytable.field1>'C') & 1).select() argument of AND > must be type boolean, not type integer LINE 1: ...le.field2 FROM mytable > WHERE ((mytable.field1 > 'C') AND 1); ^ > postgres db((db.mytable.field1>'C') & '1').select() argument of AND > must be type boolean, not type integer LINE 1: ...le.field2 FROM mytable > WHERE ((mytable.field1 > 'C') AND 1); ^ > postgres db((db.mytable.field1>'C') & 'TRUE').select() > mytable.id mytable.field1 mytable.field2 > 4 D y > 5 E y > postgres db((db.mytable.field1>'C') & "TRUE").select() > mytable.id mytable.field1 mytable.field2 > 4 D y > 5 E y > sqlite db(db.mytable.id>0).select(orderby=db.mytable.field2, > groupby=db.mytable.field2) > mytable.id mytable.field1 mytable.field2 > 2 B x > 5 E y > sqlite db(db.mytable.id>0).select(orderby=db.mytable.field2, > distinct=db.mytable.field2) near "ON": syntax error > postgres db(db.mytable.id>0).select(orderby=db.mytable.field2, > groupby=db.mytable.field2) column "mytable.id" must appear in the > GROUP BY clause or be used in an aggregate function LINE 1: SELECT > mytable.id, mytable.field1, mytable.field2 FROM myta... ^ > postgres db(db.mytable.id>0).select(orderby=db.mytable.field2, > distinct=db.mytable.field2) > mytable.id mytable.field1 mytable.field2 > 1 A x > 3 C y > > > 2015-01-03 10:47 GMT+01:00 Marin Pranjić <marin.pran...@gmail.com>: > The latter one works only with postgres: > https://groups.google.com/forum/#!topic/web2py/1_DHUrrg6O8 > > What's the error message? > > On Sat, Jan 3, 2015 at 5:30 AM, Massimo Di Pierro > <massimo.dipie...@gmail.com> wrote: > > > On Friday, 2 January 2015 16:03:16 UTC-6, mweissen wrote: > I have tried to convert an application from SQLite to Postgresql and I have > found two differences: > > (1): > q = some query like db.table.field<3 > b = some boolean like a<3 > db(q & b) > > SQLite needs to convert b to '0' or '1', Postgresql to 'FALSE' or 'TRUE' > > yes but web2py does it for you. you should not need to change your code. > > > (2): orderby generates some difficult error messages. I have found the > following solution > SQLite > db(db.table.field==x).select(orderby=db.table.field2, groupby=db.table.field2) > > Postgresql: > db(db.table.field==x).select(orderby=db.table.field2, > distinct=db.table.field2) > > Are you sure? I think they both work on both databases. The problems arise > when you groupby some fields and records are not sorted by those fields. Some > engines allow it and some don't. > > Does somebody has similar experiences? > > Regards, Martin > > > 2015-01-02 16:29 GMT+01:00 Michele Comitini <michele.comit...@gmail.com>: > SQLite is by design an excellent embedded database, better suited for single > user implementation and targeted at small datasets. > For larger datasets there is no reason not to use PostgreSQL, it's free, well > supported by web2py, > handles very large datasets and it's easy to setup. > > Migration from SQLite to any other db supported by web2py is very easy and > explained on the web2py book > > 2015-01-02 6:49 GMT+01:00 Eric Taw <tawe...@gmail.com>: > From reading a lot of web2py tutorials and guides, they always say SQLite is > good for small implementations of databases, whereas other SQL flavors are > better suited for larger uses. Is there any reason why? Can I still use > SQLite if I have a lot of information to store? > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > 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/d/optout. > > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > 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/d/optout. > > > -- > Resources: > - http://web2py.com > - http://web2py.com/book (Documentation) > - http://github.com/web2py/web2py (Source code) > - https://code.google.com/p/web2py/issues/list (Report Issues) > --- > 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/d/optout. > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- 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/d/optout.
