If you want to see what the dal did with the query, do 'print db._lastsql' in your controller.
On Friday, October 5, 2012 3:13:54 PM UTC-4, curiouslearn wrote: > > Thanks a lot. This is helpful. > > > On Fri, Oct 5, 2012 at 12:52 PM, Massimo Di Pierro > <[email protected] <javascript:>> wrote: > > I would do: > > > > rows = > > > db(db.teams.experimentid.belongs(db(db.listofexperiments.experimentname=='ug2').select( > db.listofexperiments.id))).select(db.teams.teamname) > > > > or with a shorter syntax: > > > > rows = > > > db(db.teams.experimentid.belongs(db.listofexperiments.experimentname=='ug2')).select(db.teams.teamname) > > > > > > > > On Friday, 5 October 2012 11:32:44 UTC-5, curiouslearn wrote: > >> > >> Hello, > >> > >> I want to implement the following SQL query: > >> > >> select teamname from teams where experimentid = (select id from > >> listofexperiments where experimentname = 'ug2'); > >> > >> > >> I can think of the following way of doing this: > >> > >> (1) Get the experiment id > >> > >> > >> exptidquery = db(db.listofexperiments.experimentname == 'ug2') > >> experimentid = (exptidquery.select())[0].id > >> > >> > >> > >> (2) Get team names and ids corresponding to the experimentid > >> > >> > >> relevantteams = db(db.teams.experimentid == > >> experimentid).select(db.teams.teamname) > >> > >> > >> > >> But I suppose the above raw SQL connects to the database only once to > do > >> the query. > >> The DAL method above needs to connect to the database twice. Am I > >> understanding this correctly? > >> If so, is there a better way to perform the above raw SQL query using > DAL. > >> > >> > >> Thanks for your help. The relevant table definitions are given below. > >> > >> > >> > >> The relevant table definitions are: > >> > >> # Table containing a list of experiments > >> db.define_table('listofexperiments', > >> Field('experimentname', 'string', length=40, > unique=True), > >> format='%(experimentname)s') > >> > >> > >> # Table containing teamnames > >> db.define_table('teams', > >> Field('teamname', 'string', length=40, required=True, > >> notnull=True), > >> Field('passwd', 'password'), > >> Field('role', 'string', length=20, required=True, > >> default='NA'), > >> Field('lastround', 'integer', default=0), > >> Field('experimentid', 'reference listofexperiments'), > >> format = '%(teamname)s', > >> migrate='teams.table') > >> > >> > > -- > > > > > > > --
