Many to Many Insert

25 views
Skip to first unread message

jfinke

unread,
Jul 4, 2008, 4:08:17 PM7/4/08
to web2py Web Framework
I have the following models:
db.define_table("teams",
SQLField("team","string"))

db.define_table("demo",
SQLField("map_id","reference map",default="0"),
SQLField("bomb_id","reference bomb",default="0"),
SQLField("league_id","reference league",default="0"),
SQLField("demo_date","date",notnull=True),
SQLField("demo_file", "upload", notnull=True),
SQLField("comp_id","reference comp",default="0"),
SQLField("ddate","datetime",notnull=True,default=now),
SQLField("games_id", "reference games", notnull=True),
SQLField("demo_desc", "text"))

#Many to Many table for teams in a demo
db.define_table("teamdemo",
SQLField("demo_id", "reference demo"),
SQLField("teams_id", "reference teams"))

#Linking teams and demos
played=((db.demo.id==db.teamdemo.demo_id) &
(db.teams.id==db.teamdemo.teams_id))

I have the following controller:
#Adds a new demos
def new_demo():
form=SQLFORM(db.demo, fields=['map_id', 'bomb_id', 'league_id',\
'demo_date', 'demo_file', 'comp_id',
'games_id',\
'demo_desc'])
if form.accepts(request.vars, session):
response.flash='Successfully uploaded a new demo!'
return dict(form=form)

How do I edit my SQLFORM to insert multiple items into the teamdemo
table while inserting the above information into the demo table? I
assume I need to use a FORM? For example, in addition to the
information in the form above, I also want to associate 2 teams. This
means I need to query the user for two teams and then insert them into
the teamdemo table, correct?

Thanks!

Massimo Di Pierro

unread,
Jul 4, 2008, 4:16:42 PM7/4/08
to web...@googlegroups.com
Yes, that's one way to do it. There is no way to automatically
generate a form that involves more than one table.

You can append INPUT fields in the SQLFORM and perform manual insert
into the secondary tables after accepts.

Massimo

jfinke

unread,
Jul 4, 2008, 4:50:06 PM7/4/08
to web2py Web Framework
Following up, because I couldn't find this in the documentation.

How, do I prepopulate the form. I know I need to do a select. But
what is the syntax within the FORM?

Massimo Di Pierro

unread,
Jul 4, 2008, 5:32:38 PM7/4/08
to web...@googlegroups.com
Answer in line 3

>>> from gluon.storage import Storage
>>> a=Storage()
>>> form=FORM(INPUT(_name='a',value='b'))
>>> form.accepts(a)
False
>>> print form
<form enctype="multipart/form-data" action="" method="post"><input
value="b" name="a" /><input value="default" type="hidden"
name="_formname" /></form>

jfinke

unread,
Jul 6, 2008, 12:30:44 PM7/6/08
to web2py Web Framework
I guess I am being dense... I don't see how this works. I read what
you posted and what is in the manual on pages 70, 71, and 72.

Where can I look for the code in web2py itself? This might give me an
example of how web2py does it through the SQLFORM interface.

Massimo Di Pierro

unread,
Jul 6, 2008, 12:45:47 PM7/6/08
to web...@googlegroups.com
accepts is defined in gluon/html.py but it is everywhere. What
specifically is unclear? I am updating the manual right now so I can
try to a better job.

jfinke

unread,
Jul 6, 2008, 2:48:52 PM7/6/08
to web2py Web Framework
Well, I am trying to get a drop down selection just like I do with a
SQLFORM.

Without a framework, I would normally do a select from the db to get
the options.

Then I would populate it like:
<select>
<option value ="1">Volvo</option>
<option value ="2">Saab</option>
<option value ="3">Opel</option>
<option value ="4">Audi</option>
</select>

Do, I need to do all of this manually still?

Thanks!

Massimo Di Pierro

unread,
Jul 6, 2008, 3:06:49 PM7/6/08
to web...@googlegroups.com

SELECT(*[OPTION(x,i+1) for i,x in enumerate
(['volvo','saab','open','audi'])],_name='myselect')

jfinke

unread,
Jul 6, 2008, 3:23:32 PM7/6/08
to web2py Web Framework
I guess I am not being clear.

The volvo, saab, opel, audi are from the database, so I don't know
their values.

For instance, say I have a table cars:
car.id, car.name
which has the following records:
1, volvo
2, saab
3, open
4, audi

So, to do my select, I need to do:
car=db().select(db.cars.ALL)

So.. for my form, I would need to do something like this:
form=FORM(TABLE(TR("Car:", INPUT(_type="select", ***********)),\
TR("", INPUT(_type="submit",
_value="Submit Car"))))

I don't know what to put for ***********.

Is this even possible?

Thanks again!

yarko

unread,
Jul 6, 2008, 3:29:44 PM7/6/08
to web2py Web Framework
This is dense, and useful to read outloud!

For me, doing that makes this read like "prose" and takes away the
"magic".
I think this is probably what Massimo has been doing so long, all he
does is "just thinks in terms of the dense statement"

OPTION( item, number) # enumerate returns a value/number pair
starting at zero - if that's ok, you don't need to adjust i.e. i+1;

Compare this to http://www.w3schools.com/TAGS/tag_select.asp

now, above:

form=FORM(SELECT(.....))

Massimo Di Pierro

unread,
Jul 6, 2008, 3:37:50 PM7/6/08
to web...@googlegroups.com

***** = *[OPTION(x.name,value=x.id) for x in db().select(db.cars.ALL)]

anyway, do you have an other table that refrences this one? if so
there is a much cleaner way.

Massimo

yarko

unread,
Jul 6, 2008, 4:37:13 PM7/6/08
to web2py Web Framework
.... I should have followed along w/ a shell first....
First - what's wrong w/ this (IPython shell has syntax error w/ it):

SELECT(*[OPTION(x,i+1) for i,x in enumerate
(['volvo','saab','open','audi'])],_name='myselect')

Second: this is what I "put together" from the last posts (written so
I can actually try to read it) -

form=FORM( \
TABLE( \
TR("Car:",
INPUT(_type="select",\
*[OPTION(x.name,value=x.id) for x in
db().select(db.cars.ALL)] \
) \
) \
TR("", \
INPUT(_type="submit", _value="Submit Car") \
) \
) \
)

and getting syntax errors in web2py (but not if I copy-paste from
web2py ticket directly into IPython shell ...)

form=FORM(TABLE(TR("Car:",
INPUT(_type="select",*[OPTION(x.name,value=x.id) for x in
db().select(db.cars.ALL)])), TR("", INPUT(_type="submit",
_value="Submit Car"))))

ugh....

Massimo Di Pierro

unread,
Jul 6, 2008, 4:57:34 PM7/6/08
to web...@googlegroups.com
This works:


def cars():
    response.flash=T('Welcome to web2py')
    form=FORM(TABLE(TR("Car:", SELECT(_type="select",*[OPTION(x.name,_value=x.id) for x in db().select(db.cars.ALL)])), 
                                          TR("", INPUT(_type="submit", _value="Submit Car"))))
    if form.accepts(request.vars,session): response.flash='car stored'
    return dict(message=T('Select a car'),form=form)

jfinke

unread,
Jul 6, 2008, 6:34:18 PM7/6/08
to web2py Web Framework
Thanks! I will give it a try.

Well, I am just trying to duplicate what happens in SQLFORM. Mainly
the magic of one to many relationship. When I do a SQLFORM with
db.table.item.requires=IS_IN_DB(....)

The cars I gave was an example. My real program follows the model
above in my first price.

db.define_table("map",
SQLField("map_name","string",length=64,notnull=True,default=""))
db.define_table("demo",
SQLField("map_id","reference map",default="0"),
SQLField("bomb_id","reference bomb",default="0"),
SQLField("league_id","reference league",default="0"),
SQLField("demo_date","date",notnull=True),
SQLField("demo_file", "upload", notnull=True),
SQLField("comp_id","reference comp",default="0"),
SQLField("ddate","datetime",notnull=True,default=now),
SQLField("games_id", "reference games", notnull=True),
SQLField("demo_desc", "text"))
db.demo.map_id.requires=IS_IN_DB(db, 'map.id', 'map.map_name')

So, to select all map names so that I can insert the correct map.id
into the demo table, I need to do??
form=FORM(TABLE(TR("Maps:", SELECT(_type="select",*[OPTION
(x.name,_value=x.id) for x
in db().select(db.map.ALL)])),
TR("", INPUT
(_type="submit",
_value="Submit Map"))))

yarko

unread,
Jul 6, 2008, 7:01:15 PM7/6/08
to web2py Web Framework
Ok - now I want to change this to radio buttons...

....
form=FORM(TABLE(TR("", *[x.name for x in
db().select(db.cars.ALL)]), \
TR("My Favorite Car:", \
*[INPUT(_type="radio",_name="value",_value=x.id) for x in
db().select(db.cars.ALL)])),\
TR("", INPUT(_type="submit", _value="Submit Car")) )
..........

Gives me a tabular sort of form....

Massimo Di Pierro

unread,
Jul 6, 2008, 7:18:24 PM7/6/08
to web...@googlegroups.com
The SELECT html tag has not type attribute (I think) and you need to
give it name.

You can do

form=FORM(TABLE(TR("Maps:", SELECT(_name='map_id',*[OPTION


(x.name,_value=x.id) for
x in db().select(db.map.ALL)])),
TR("", INPUT
(_type="submit",_value="Submit Map"))))

or simpler

form=SQLFORM(db.demo,fields=['map_id'])

In the second case you do not really needs validation. You can just user

if request.vars.map_id: .... use request.vars.map_id ...

Massimo

jfinke

unread,
Jul 6, 2008, 11:33:31 PM7/6/08
to web2py Web Framework
Thanks.. I had looked at that and figured I needed the name. Just
didn't know the syntax.

Massimo Di Pierro

unread,
Jul 6, 2008, 11:37:06 PM7/6/08
to web...@googlegroups.com
you never need to remember the syntax. If in html it is

<select name="whatever">...</select>

in web2py it is

SELECT('...',_name="whatever")

Massimo

jfinke

unread,
Jul 7, 2008, 12:19:01 AM7/7/08
to web2py Web Framework
How about dealing with other data types, such as the datepicker and
uploads?

Thanks again for your wonderful support.

jfinke

unread,
Jul 7, 2008, 12:29:18 AM7/7/08
to web2py Web Framework
I see that datepicker comes up automatically through the view, if the
class is date. Does that mean if the field is defined as a date field
in the DB, than it automatically comes up?

jfinke

unread,
Jul 7, 2008, 12:53:51 AM7/7/08
to web2py Web Framework
Got the datepicker working...

I had to do:

TR("Date:", INPUT(_type="text", _name='date', _class="date")),\

Thanks!

Kyle Smith

unread,
Jul 7, 2008, 1:07:34 AM7/7/08
to web...@googlegroups.com
If you are using the default application layouts you can look in web2py_ajax.html for how this is working:

try {$("input.date").datepicker({dateFormat: '{{=T("yy-mm-dd")}}',showOn: 'focus'});} catch(e) {};

This is taking any input with a class of date and showing the date picker on the focus event. This is done through jquery as part of $(document).ready()

Kyle

jfinke

unread,
Jul 7, 2008, 1:25:44 AM7/7/08
to web2py Web Framework
Well,

Getting back to the original topic.. :) I was able to do an insert
for a M2M relationship. I don't know if this is the correct way, but
it seems to work.

def new_demo():
# form=SQLFORM(db.demo, fields=['map_id', 'bomb_id', 'league_id',\
# 'demo_date', 'demo_file', 'comp_id',
'games_id',\
# 'demo_desc'])
# maps=db().select(db.map.ALL)
form=FORM(TABLE(TR("Maps:",
SELECT(_name='map_id',*[OPTION(x.map_name,_value=x.id) for x
in db().select(db.map.ALL)])),\
TR("Bomb Type:",
SELECT(_name='bomb_id',*[OPTION(x.btype,_value=x.id) for x
in db().select(db.bomb.ALL)])),\
TR("League:",
SELECT(_name='league_id',*[OPTION(x.league_name,_value=x.id) for x
in db().select(db.league.ALL)])),\
TR("Date:", INPUT(_type="text", _name='date',
_class="date")),\
TR("Team1:", SELECT(_name='team1',
*[OPTION(x.team,_value=x.id) for x
in db().select(db.teams.ALL)])),\
TR("Team2:", SELECT(_name='team2',
*[OPTION(x.team,_value=x.id) for x
in db().select(db.teams.ALL)])),\
TR("", INPUT(_type="submit", _value="Submit
Demo"))))
if form.accepts(request.vars, session):
demo_id=db.demo.insert(map_id=form.vars.map_id,
bomb_id=form.vars.bomb_id,\
league_id=form.vars.league_id,
demo_date=form.vars.date)
db.teamdemo.insert(demo_id=demo_id, teams_id=form.vars.team1)
db.teamdemo.insert(demo_id=demo_id, teams_id=form.vars.team2)
response.flash='Successfully uploaded a new demo!'
return dict(form=form)

On Jul 7, 12:07 am, "Kyle Smith" <kyletsm...@gmail.com> wrote:
> If you are using the default application layouts you can look in
> web2py_ajax.html for how this is working:
>
> try {$("input.date").datepicker({dateFormat: '{{=T("yy-mm-dd")}}',showOn:
> 'focus'});} catch(e) {};
>
> This is taking any input with a class of date and showing the date picker on
> the focus event. This is done through jquery as part of $(document).ready()
>
> Kyle
>

Massimo Di Pierro

unread,
Jul 7, 2008, 2:03:07 AM7/7/08
to web...@googlegroups.com
yes. and if the field is 'integer' or 'double' it will prevent you
from typing something that is not a valid int or double.

Massimo

Massimo Di Pierro

unread,
Jul 7, 2008, 2:04:37 AM7/7/08
to web...@googlegroups.com
Ideally there should be a date picker that works as datetime picker
too (and time on/off) as well, but I could not find one.

Massimo Di Pierro

unread,
Jul 7, 2008, 2:15:14 AM7/7/08
to web...@googlegroups.com
Excellent,
That's how I'd do it or you could use the form builder I sent last week:

def form_builder(labels=None,*a): return SQLFORM(SQDB
(None).define_table('mytable',*a),labels=labels)

def new_demo():
form=form_builder(SQLField('map_id',requires=IS_IN_DB
(db,'map.id','%(map_name)s')),
SQLField
('bomb_id',requires=IS_IN_DB(db,'bomb.id','%(btype)s')),
SQLField
('league_id',requires=IS_IN_DB(db,'league.id','%(league_name)s')),
SQLField
('team1',requires=IS_IN_DB(db,'team.id','%(team)s')),
SQLField
('team2',requires=IS_IN_DB(db,'team.id','%(team)s')))


if form.accepts(request.vars, session):
demo_id=db.demo.insert(map_id=form.vars.map_id,
bomb_id=form.vars.bomb_id,
league_id=form.vars.league_id,
demo_date=form.vars.date)
db.teamdemo.insert(demo_id=demo_id, teams_id=form.vars.team1)
db.teamdemo.insert(demo_id=demo_id, teams_id=form.vars.team2)
response.flash='Successfully uploaded a new demo!'
return dict(form=form)

Please tell us this about a game and we should not worry about the
use of the word bomb in here.

Massimo

Massimo Di Pierro

unread,
Jul 7, 2008, 2:17:17 AM7/7/08
to web...@googlegroups.com
errata

On Jul 7, 2008, at 1:15 AM, Massimo Di Pierro wrote:

def form_builder(labels=None,*a): return SQLFORM(SQLDB(None).define_table('mytable',*a),labels=labels)


Tito Garrido

unread,
Sep 21, 2008, 9:12:08 PM9/21/08
to web...@googlegroups.com
Great example yarko!
Do you know how can I place my radio options like this:

( ) | Description of an option
( ) | Description of an option
(x) | Description of an option

Thanks,

Tito
--
Linux User #387870
.........____
.... _/_õ|__|
..º[ .-.___.-._| . . . .
.__( o)__( o).:_______
Reply all
Reply to author
Forward
0 new messages