[web2py] Re: Write dynamic query in DAL without extra nested parentheses
On Thursday, June 28, 2018 at 10:34:47 AM UTC-4, Artem wrote: > > Dear Antony , > From your example : > query = ' OR '.join(str(db.product.id == i) for i in [list of ids]) > result type(query) is a string , could you tell me how to correctly > convert it to the pydal.objects.Query / Set ? > I'm trying query = db(query) , and get a pydal.objects.Set , but can't > use it , query.select() rise errors > You need some way to tell the DAL what table is involved. So, either: db((db.product.id > 0) & query).select() or: db(query).select(db.product.ALL) Anthony > -- 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.
[web2py] Re: Write dynamic query in DAL without extra nested parentheses
Dear Antony , >From your example : query = ' OR '.join(str(db.product.id == i) for i in [list of ids]) result type(query) is a string , could you tell me how to correctly convert it to the pydal.objects.Query / Set ? I'm trying query = db(query) , and get a pydal.objects.Set , but can't use it , query.select() rise errors Thanks! On Sunday, March 29, 2015 at 1:37:03 AM UTC+8, Anthony wrote: > > In this particular case, you should instead use .belongs(): > > query = db.product.id.belongs([list of ids]) > > It's an interesting problem, though. A somewhat hackish solution would be: > > query = ' OR '.join(str(db.product.id == i) for i in [list of ids]) > > The problem is that OR and AND operators always wrap the operands in > parentheses, even when not necessary. The result is the nesting you observe > when using reduce() or appending in a loop. Perhaps there should be a way > to suppress the parentheses when not needed. > > Anthony > -- 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.
[web2py] Re: Write dynamic query in DAL without extra nested parentheses
Thanks, Gary! Works perfectly. On Tuesday, March 31, 2015 at 4:27:12 PM UTC-6, Gray Kanarek wrote: You want belongs, I think: query = db.product.id.belongs((selected_ids)) On Saturday, March 28, 2015 at 12:14:44 PM UTC-4, gb wrote: What is the DAL Code to generate a query like this: SELECT * FROM product WHERE product.id=1 OR product.id=2 OR product.id=3 OR product.id product.id=4 OR product.id=5; or even: SELECT * FROM product WHERE (product.id=1) OR (product.id=2) OR ( product.id=3) OR (product.id=4) OR (product.id=5); I've tried both the DAL code approaches below and I always end up with crazy parentheses nesting which will eventually crash the DB driver e.g. in sqlite: class 'sqlite3.OperationalError' parser stack overflow Is there another way to use the DAL, or is my only option to write the SQL manually? db.define_table(product) selected_ids = [1,2,3,4,5] query = [] for pid in selected_ids: query.append(db.product.id == pid) query = reduce(lambda a,b:a|b,query) #Ouputs this : (product.id = 1) OR (product.id = 2)) OR ( product.id = 3)) OR (product.id = 4)) OR (product.id = 5)) selected_ids = [1,2,3,4,5] query = [] for pid in selected_ids: query |= db.product.id == pid #I get a bonus parenthesis with this method ((product.id = 1) OR ) OR (product.id = 2)) OR (product.id = 3)) OR (product.id = 4)) OR ( product.id = 5)) -- 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.
[web2py] Re: Write dynamic query in DAL without extra nested parentheses
I have a list of products (much less than 1000) that the users can select or unselect--I supposed I could only select on the unselected items. If it's above 80 or so sqlite throws class 'sqlite3.OperationalError' parser stack overflow, see code below: db.define_table(product) query = [] for i in xrange(90): query.append(db.product.id == i) query = reduce(lambda a,b:a|b,query) db(query).select() However, the suggested code works great! query = db.product.id.belongs([list of ids]) Thanks, Anthony! On Sunday, March 29, 2015 at 12:11:13 PM UTC-6, Niphlod wrote: btw: sqlite has a pretty deep default value that should handle 1000 of those or. but or the sake of your app is it reaally necessary to fetch records in a single query asking for 1000 specific values ? On Saturday, March 28, 2015 at 6:37:03 PM UTC+1, Anthony wrote: In this particular case, you should instead use .belongs(): query = db.product.id.belongs([list of ids]) It's an interesting problem, though. A somewhat hackish solution would be: query = ' OR '.join(str(db.product.id == i) for i in [list of ids]) The problem is that OR and AND operators always wrap the operands in parentheses, even when not necessary. The result is the nesting you observe when using reduce() or appending in a loop. Perhaps there should be a way to suppress the parentheses when not needed. Anthony -- 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.
[web2py] Re: Write dynamic query in DAL without extra nested parentheses
You want belongs, I think: query = db.product.id.belongs((selected_ids)) On Saturday, March 28, 2015 at 12:14:44 PM UTC-4, gb wrote: What is the DAL Code to generate a query like this: SELECT * FROM product WHERE product.id=1 OR product.id=2 OR product.id=3 OR product.id product.id=4 OR product.id=5; or even: SELECT * FROM product WHERE (product.id=1) OR (product.id=2) OR ( product.id=3) OR (product.id=4) OR (product.id=5); I've tried both the DAL code approaches below and I always end up with crazy parentheses nesting which will eventually crash the DB driver e.g. in sqlite: class 'sqlite3.OperationalError' parser stack overflow Is there another way to use the DAL, or is my only option to write the SQL manually? db.define_table(product) selected_ids = [1,2,3,4,5] query = [] for pid in selected_ids: query.append(db.product.id == pid) query = reduce(lambda a,b:a|b,query) #Ouputs this : (product.id = 1) OR (product.id = 2)) OR ( product.id = 3)) OR (product.id = 4)) OR (product.id = 5)) selected_ids = [1,2,3,4,5] query = [] for pid in selected_ids: query |= db.product.id == pid #I get a bonus parenthesis with this method ((product.id = 1) OR ) OR (product.id = 2)) OR (product.id = 3)) OR (product.id = 4)) OR ( product.id = 5)) -- 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.
[web2py] Re: Write dynamic query in DAL without extra nested parentheses
btw: sqlite has a pretty deep default value that should handle 1000 of those or. but or the sake of your app is it reaally necessary to fetch records in a single query asking for 1000 specific values ? On Saturday, March 28, 2015 at 6:37:03 PM UTC+1, Anthony wrote: In this particular case, you should instead use .belongs(): query = db.product.id.belongs([list of ids]) It's an interesting problem, though. A somewhat hackish solution would be: query = ' OR '.join(str(db.product.id == i) for i in [list of ids]) The problem is that OR and AND operators always wrap the operands in parentheses, even when not necessary. The result is the nesting you observe when using reduce() or appending in a loop. Perhaps there should be a way to suppress the parentheses when not needed. Anthony -- 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.
[web2py] Re: Write dynamic query in DAL without extra nested parentheses
from Massimo, in another reply today (https://groups.google.com/forum/#!topic/web2py/RqSBO2dZU4E) : query = db.flatpage.f==request.function is the same as query = query (db.flatpage.f==request.function) Build a query from another query On Saturday, March 28, 2015 at 11:14:44 AM UTC-5, gb wrote: What is the DAL Code to generate a query like this: SELECT * FROM product WHERE product.id=1 OR product.id=2 OR product.id=3 OR product.id product.id=4 OR product.id=5; or even: SELECT * FROM product WHERE (product.id=1) OR (product.id=2) OR ( product.id=3) OR (product.id=4) OR (product.id=5); I've tried both the DAL code approaches below and I always end up with crazy parentheses nesting which will eventually crash the DB driver e.g. in sqlite: class 'sqlite3.OperationalError' parser stack overflow Is there another way to use the DAL, or is my only option to write the SQL manually? db.define_table(product) selected_ids = [1,2,3,4,5] query = [] for pid in selected_ids: query.append(db.product.id == pid) query = reduce(lambda a,b:a|b,query) #Ouputs this : (product.id = 1) OR (product.id = 2)) OR ( product.id = 3)) OR (product.id = 4)) OR (product.id = 5)) selected_ids = [1,2,3,4,5] query = [] for pid in selected_ids: query |= db.product.id == pid #I get a bonus parenthesis with this method ((product.id = 1) OR ) OR (product.id = 2)) OR (product.id = 3)) OR (product.id = 4)) OR ( product.id = 5)) -- 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.
[web2py] Re: Write dynamic query in DAL without extra nested parentheses
In this particular case, you should instead use .belongs(): query = db.product.id.belongs([list of ids]) It's an interesting problem, though. A somewhat hackish solution would be: query = ' OR '.join(str(db.product.id == i) for i in [list of ids]) The problem is that OR and AND operators always wrap the operands in parentheses, even when not necessary. The result is the nesting you observe when using reduce() or appending in a loop. Perhaps there should be a way to suppress the parentheses when not needed. Anthony -- 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.
