Many to Many Insert

[jfinke]

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]

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]

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]

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]

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]

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]

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]

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

[jfinke]

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]

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]

***** = *[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]

.... 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]

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]

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]

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]

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]

Thanks.. I had looked at that and figured I needed the name. Just
didn't know the syntax.

[Massimo Di Pierro]

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]

How about dealing with other data types, such as the datepicker and
uploads?

Thanks again for your wonderful support.

[jfinke]

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]

Got the datepicker working...

I had to do:

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

Thanks!

[Kyle Smith]

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]

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]

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]

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]

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]

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]

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).:_______