Thanks a lot. This is helpful.
On Fri, Oct 5, 2012 at 12:52 PM, Massimo Di Pierro <[email protected]> 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') >> >> > -- > > > --
