Thanks Massimo, that's a great help. I'm starting to get the hang of it. I had trouble with the following:
>>> r9=db(q2)(db.Section.id==7767|db.Section.id==7540).select() but realized that I needed to enclose the filters in parentheses: >>> r9=db(q2)( (db.Section.id==7767) | (db.Section.id==7540) ).select() The web2py book certainly helped with basic concepts but now that I've ventured into real-world coding on our classroom management application, I'm finding it difficult to extrapolate the basics to the more complex tasks. Hence, I really appreciate your help. I'm hoping the new version of the book will contain more advanced examples so I can work through problems more independently. :) BTW, it was at this exact point that I gave up on django last summer. I couldn't find a way to generate very complex joins using their DAL. Eventually, I turned to web2py. It was the right decision for our school.. -Michael On Aug 14, 12:46 pm, mdipierro <[email protected]> wrote: > if q1, q2, q3, ... are elementary queries like > > db.table.field==value > db.table.field<value > db.table.field==db.table.otherfield+1 > etc. > > a SQLSet is > > db(q1) > db(q1&q2) same as db(q1)(q2) > db(q1|q2) > > "&" and "|" are the logical operators. You should not use "and" and > "or". > > On a SQLSet you can call .select(...) > > db(q1|q2)(q3|q4).select(....) > > On Aug 14, 10:46 am, Michael <[email protected]> wrote: > > > In my last post I said r2=f1(db.Section.schoolYear==2009).select() - > > works fine. > > > But I'm still struggling with SQLSets. I've read through the web2py > > book and looked at examples online. Unfortunately I can't find > > examples that clarify complex SQLSet syntax. I don't know how best to > > present all the scenarios I've tried so I'll just list a few below. > > > Example 1: Trying to filter a SQLSet (f1) using multiple conditions: > > > r2=f1(db.Section.schoolYear==2009 and db.Section.id==7540).select() > > - results in: > > > "ProgrammingError: The cursor's connection has been closed." > > > Example 2: Tried to create a subset of the original set: > > > >>>subset2=db(f1)(db.Section.id==7540) > > >>> r6=subset2.select() > > > Traceback (most recent call last): > > File "C:\web2py\applications\ajaxShell/controllers/default.py", line > > 88, in talk > > exec(request.vars.s) in {},d > > File "<string>", line 1, in <module> > > File "C:\web2py\gluon\sql.py", line 2210, in select > > query = self._select(*fields, **attributes) > > File "C:\web2py\gluon\sql.py", line 2117, in _select > > fields = [self._db[table].ALL for table in self._tables] > > File "C:\web2py\gluon\sql.py", line 524, in __getitem__ > > return dict.__getitem__(self, str(key)) > > KeyError: 'gluon' > > > Example 3: Tried to select specific fields from the original SQLSet: > > > >>>r3=f1(db.Section.id==7540).select(db.Employee.name,db.Section.name,db.Cell.row,db.Cell.col,db.Cell.block,db.Period.periodStart,db.Period.periodEnd) > > > "ProgrammingError: The cursor's connection has been closed." > > > Example 4: Tried to create a subset from a new. more complex set: > > > >>>set=db((db.Employee.id==db.EmployeeSection.employeeID) & \ > > > (db.Section.id==db.EmployeeSection.sectionID) & \ > > (db.Employee.id==9000) & \ > > (db.Cell.id==db.SectionCell.cellID) & \ > > (db.Section.id==db.SectionCell.sectionID) & \ > > (db.Period.id==db.SectionCell.periodID)) > > > >>> subset=set(db.Section.id==7540) > > >>> subset.select() results in: > > > "ProgrammingError: The cursor's connection has been closed." > > > Thanks in advance for your help. > > > -Michael --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
