I have a simple use case as follows: Given an employee table containing names and salaries, I want to retrieve the highest and lowest salaries *AND* the corresponding employee info. Ideally I'd like to do this in a single database request since in the real world we like to minimize the number of round trips to the database and associated db connection requests.
(BTW: getting a db connection can incur significant overhead unless some form of connection pooling is provided). My solution (see test code below) creates a raw sql query that includes a 'UNION' clause to combine 2 separate min/max queries and does in fact require just one db connection. I'm wondering if it is possible to build this type of query using the DAL but I suspect it may not be possible (AFAIK the DAL and other ORM's do not support UNION's). I see 2 issues here that make this difficult with the DAL: 1) In addition to the min/max values I also want the corrsponding record info in the result set 2) At best the DAL may require 2 separate db requests Any DAL guru's out there care to comment on this and provide an alternative DAL solution here. TIA, BrendanC ***************************************** DAL Sample Code *************************************** ''' Example: Find min & max emp salaries and related name using SQL and Web2py DAL ''' import os, sys sdir = '/home/web2py' sys.path.append(sdir) try: from gluon import DAL, Field except ImportError as err: print('gluon path not found') # target db - sqlite db = DAL("sqlite://emptest.db") # define emp table db.define_table('emp', Field('name', type='string'), Field('salary', type='double'), migrate=True) # define dict with test data empdict = {'adams': 10, 'gates': 5, 'jobs': 20, 'david': 12, 'williams': 35} def populate_table(): # insert recs from dict into db rcnt = 0 for (k,v) in empdict.iteritems(): ddict ={} ddict['name'] = k ddict['salary'] = v print '>>> Inserting New Rec <<<' try: db.emp.insert(**ddict) db.commit() rcnt += 1 except: #raise RuntimeError, 'Script error' print '**** Insert Failed ****' print sys.exc_info() db.rollback() print '\nAdded %s recs to emp table' %rcnt if __name__ == "__main__": import datetime dtstart = datetime.datetime.now() print '\n>>>>Starting at: %s <<<<\n' %dtstart.ctime() print '\n>>>>DB Trace Info: Db uri: %s; DB Name: %s\n' %(db._uri, db._dbname) #check rec count cnt = db(db.emp.id>0).count() #print '>>> Emp Rec Count: %s' %cnt #reload table if cnt>0: # deleting previous records db(db.emp.id>0).delete() populate_table() #select min emp, salary strsql_min = 'select name, salary from (select min(salary) as minsal from emp) as x ' + \ 'inner join emp as t on t.salary = x.minsal' strsql_max = 'select name, salary from (select max(salary) as maxsal from emp) as x ' + \ 'inner join emp as t on t.salary = x.maxsal' # Build a Union Query; returns a pair of dicts with min&max values strsql = strsql_min + ' UNION ' + strsql_max rows = db.executesql(strsql, as_dict=True) mindict = rows[0] print '\n**** Lowest Salary: Name: %s; \t Salary: %s' %(mindict['name'], mindict['salary']) maxdict = rows[1] print '**** Highest Salary: Name: %s; \t Salary: %s\n' %(maxdict['name'], maxdict['salary']) dtstop = datetime.datetime.now() print '\n>>>>Stopping at: %s <<<<\n' %dtstop.ctime()