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
> fromlistofexperiments
> 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')
>
>
>
--
