Re: [web2py] many tyo many

[Richard Vézina]

I would suggest you to use things like Tag-it :
http://aehlke.github.io/tag-it/

You can then use validate_and_insert web2py function to make sure you are
not create security issue like sql injection if you insert ajax/post
directly into the db.

Richard

On Wed, May 25, 2016 at 11:21 AM, goome  wrote:

> Hello
> i have a table 'post'  and a table 'tag' and as there is a 'many to many'
> relationship between them, i also created a table 'post_tag' that only has
> reference to previous tables.
> But now, in the form
>  to create post (form = SQLFORM(db.post)) how can i add field to
> select/create tags? Is there any shortcut ora i have to build the form
> manually?
> Thanks
>
> --
> Resources:
> - http://web2py.com
> - http://web2py.com/book (Documentation)
> - http://github.com/web2py/web2py (Source code)
> - https://code.google.com/p/web2py/issues/list (Report Issues)
> ---
> You received this message because you are subscribed to the Google Groups
> "web2py-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to web2py+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] many tyo many

[goome]

Hello
i have a table 'post'  and a table 'tag' and as there is a 'many to many' 
relationship between them, i also created a table 'post_tag' that only has 
reference to previous tables.
But now, in the form
 to create post (form = SQLFORM(db.post)) how can i add field to 
select/create tags? Is there any shortcut ora i have to build the form 
manually?
Thanks

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] many to many fields representation

[icodk]

I made a form for editing  the auth_membership table using smart grid, it 
works fine.
The only issue I have is that the id of the user_id and the id of the 
group_id are appended to the user name and to the group name.
How can I get rid of this 'extra' addition ?
For example the user John Smith is shown as: John Smith(15) both in the 
create/edit and in the grid 
The group named Teachers is shown as Teachers(4) 

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Many to many to many?

[Gael Princivalle]

Hello.

I've make a many to many to many relationship but I've got problems on 
select results.
Is it the correct way to do it?
My DB:
db.define_table('ceo_schemes',
Field('name', type='string', requires=IS_NOT_EMPTY()),
format='%(name)s')
db.define_table('overrides',
Field('name', type='string', requires=IS_NOT_EMPTY()),
format='%(name)s')
db.define_table('override_codes',
Field('code1', type='string'),
Field('code2', type='string'),
format='%(code1)s %(code2)s')
db.define_table('ceo_schemes_and_overrides_and_override_codes',
Field('ceo_scheme', 'reference ceo_schemes'),
Field('override', 'reference overrides'),
Field('override_code', 'reference override_codes'))

My controller:
override = 
db((db.ceo_schemes_and_overrides_and_override_codes.ceo_scheme == 
ceo_scheme.id)  (db.ceo_schemes_and_overrides_and_override_codes.override 
== override_id)).select()

In my view I can see this result with BEAUTIFY(response._vars):
override:ceo_schemes_and_overrides_and_override_codes.id
ceo_schemes_and_overrides_and_override_codes.ceo_scheme
ceo_schemes_and_overrides_and_override_codes.override
ceo_schemes_and_overrides_and_override_codes.override_code302_DWithout
20 

But in the controller if I call:
override_code = 
override.ceo_schemes_and_overrides_and_override_codes.override_codes.code1

The ticket is :
'Rows' object has no attribute 
'ceo_schemes_and_overrides_and_override_codes'

Someone knows why? Is is the way that I've done the 3 tables relations ship 
that's wrong?

Thanks.

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] many to many with SQLFORM.grid

[Ben Lawrence]

Hi
if I have

db.define_table('meeting',Field('title','string'))
db.define_table('company',Field('name','string'))
db.define_table('co_meet',Field('ref_meeting','reference 
meeting'),Field('ref_company','reference company'))

What would the query be in SQLFORM.grid such that it would pick all the 
meetings for one company?
thanks in advance,
Ben



-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Many-to-Many relationship and multi SQLFORMs submission automatically

[Sihui Huang]

In my application, one has urls and tags. What I'm doing is when the user 
entering data for an url, he/she will also select relative tags.

I store urls and tags into two different tables and I plan to use a 
reference table which describe the many-to-many relationship between tags 
and urls.

I could tell which tags are selected by the user. (as each tag has a 
boolean variable for that purpose.)

So when the user creating 1 new url and select 5 tags, when he click the 
submit button, it seems a lot need to happen in the backend: submit 1 
SQLFORM for creating that url + creating 5 SQLFORMs each describes a 
relationship between a tag and the new url.

I have no idea how to accomplish this in web2py.

Am I doing it the right way?

Thanks for your helps in advance!!

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Many to many

[Gael Princivalle]

Hi.

I'm still making a website where I'm gone have to manage a lot of little 
image galleries.
An image could be shown inside more galleries, galleries have many images, 
so my relation is many to many.

Here is my db:
db.define_table('images',
Field('title_en'),
Field('title_it'),
Field('contents'),
Field('image_file', 'upload'),
Field('image_file_s', 'upload'))
db.define_table('galleries',
Field('title_en'),
Field('title_it'))
db.define_table('int_table_galleries_images',
Field('gallery', 'reference galleries'),
Field('image', 'reference images'))

Now in the company page, I want to show only images from the 1 category.

In the controller:
def company():
gallery_company = ?
return dict(gallery_company=gallery_company)

How can I select all fields from images and galleries where 
galleries.id==1  ?

Thanks.

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[web2py] Many to many skills matrix

[Angie Tawfik]

Hi all,

 I have a group of employees and a set of skills. One employee can have 
many skills and one skill can be had by many employees. What I want to do 
is to represent the skills of all employees in one table, where the score 
of each employee in each skill is given on a scale from 0 to 5 like:


Name |  UNIX | Apache | Python |

AT  |  4  |3   |4   |

MO |  5  |3   |4   |

MF |  4  |3   |3   |



Model:
--

db.define_table('online_team',
Field('first_name', notnull=True),
Field('last_name', notnull=True),
Field('email', requires=IS_EMAIL(), notnull=True, unique=True),
Field('initials', notnull=True, unique=True))
##format='%(first_name, last_name)s')

db.define_table('skills',
Field('skill', notnull=True, unique=True))

db.define_table('ownership',
Field('initials', 'reference online_team'),
Field('skill', 'reference skills'))

Now, in my controller I have a function to add records to the ownership 
table (combining both skills and employees) and view these records on the 
fly (supposedly the table I drew above without the scores):

def view_matrix():
# create an insert form from the table
form = SQLFORM(db.ownership).process()

# if form correct perform the insert
if form.accepted:
response.flash = 'New skills added to team member. '

my_rows = 
SQLTABLE(db().select(db.ownership.ALL),headers='fieldname:capitalize')
return dict(form=form, rows=my_rows)

The problem is that whatever I insert into the form of ownership table, I 
get something like the attached screen, no real initials, no real skills 
despite skills table and employees table are there and filled with sample 
data. I have the feeling I am doing something wrong in the model / 
controller.

Your help is much appreciated. Thanks in advance.

All the best,
Angie Tawfik

-- 

--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.


attachment: skills.png

[web2py] Many to many relationships

[Toby Wilson]

Hi all,

New to web development and web2py. Know a bit of Python.

As a learning experience I’ve written a food tracking program that works 
really well in Python and it just runs on my local PC. It just reads and 
writes values into text files.

Now I’m trying to transfer that into web2py as a next step learning 
experience. I’m trying to figure out the database side of it (models).

It seems what I’m dealing with is many-to-many relationships. I have a list 
of meals and each meal has a list of associated ingredients. So one 
ingredient can be in many meal and each meal has many ingredients.

So essentially, what the program I've written can do is add 3 x “Meal 1” to 
the quantity existing in the database for “Meal 1” and then after doing 
that prompt the user with:

“Do you want this to impact the level of the ingredient ‘carrot’?” Y/N

And then:

“Do you want this to impact the level of the ingredient ‘steak’? Y/N

etc.

And the program knows that “Meal 1” needs 3 carrots, so upon entering “Y” 
to the first question, 3 carrots are automatically subtracted from the 
stock level of carrots in the database.

So as meals are added, the user is prompted with associated ingredients and 
can choose to update or not update the level of that associated ingredient.

In the local Python program I’ve written, all of this is just done through 
listing things in dictionaries and using functions to manipulate the data 
and then read and write to text files. Now I want to use database tables 
and I’m not sure how much of the association should be in the model and how 
much (if any) should be in the controller (for example in the form of a 
dictionary).

I’ve read through the documentation on many to many relationships but 
haven’t got a full grasp of it yet.

I’ve coded a few different database structures but haven’t managed to get 
it right yet.

The way I've been thinking about it is, a table for meals, a table for 
ingredients, and perhaps a table for associations? I've used reference to 
get the drop down box associated, but I can't select more than ingredient. 
I had a look at the Django admin login and it visually made this look a bit 
easier - i.e. building the one to many relationship. How is this done in 
web2py?

Any help or advice would be much appreciated.

Toby

P.S. If I have a database file in web2py and open the file through the 
web2py IDE and replace the contents with a totally different database 
structure and save it, what happens? Does web2py just remove what was there 
in the database and replace the database tables with the new ones or does 
it manage it in a different way (i.e. only add new elements etc.)?

 

-- 

--- 
You received this message because you are subscribed to the Google Groups 
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[web2py] Many to Many relation

[__pyslan__ - Ayslan Jenken]

I have a relation many-to-many.

How to prevent the insertion or alterations of duplicate pairs?


Thanks...

-- 

Re: [web2py] Many to Many relation

[Bruno Rocha]

I can think on two options.

*1. Unique Key*

db.define_table(table,
Field(table_a, reference table_a),
Field(table_b, reference table_b),
Field(unikey, unique=True, notnull=True, compute=lambda row:
%(table_a)s-%(table_b)s % row)
)


*2. Form validator*

def check_unique(form):
if db((db.table.table_a == form.vars.table_a)  (db.table.table_b ==
form.vars.table_b)).count():
form.errors.table_a = You cannot insert or edit a duplicate
combination

form = SQLFORM(db.table).process(onvalidation=check_unique)

Mybe it can be implemented as a Field Validator, have to try.

-- 

Re: [web2py] Many to Many relation

[__pyslan__ - Ayslan Jenken]

Bruno, I like the second option, but I take a look:

def workers():
make_menu('Ventures')
venture_name = ''
venture_id = ''
fields = []
btn_back = None

if request.args(0):

if not session.url_back:
try:
btn_back = make_button_text(  + T(Ventures),
T(Ventures), request.wsgi.environ['HTTP_REFERER'], True, icon leftarrow
icon-arrow-left)
session.url_back = request.wsgi.environ['HTTP_REFERER']
except:
btn_back = make_button_text(  + T(Ventures),
T(Ventures), URL(venture, index), True, icon leftarrow
icon-arrow-left)
else:
btn_back = make_button_text(  + T(Ventures), T(Ventures),
session.url_back, True, icon leftarrow icon-arrow-left)

venture_id = request.args(0)
# Isso é o mesmo que fazer uma Query com Occurrence.venture ==
request.args(0) e passar a query para a grid ao invés da tabela
VentureWorker._common_filter = lambda query: VentureWorker.venture
== venture_id

venture_row = db(Venture.id == venture_id).select().first()
if venture_row:
venture_name = ' - ' + venture_row.name
fields=[VentureWorker.worker]

if request.args(1) == 'new':
if venture_row:
VentureWorker.venture.default = venture_id
else:
redirect(URL('venture', 'norms', args=[venture_id]))


createargs = {'fields':['worker']}
editargs = {'fields':['worker']}
viewargs = {'fields':['worker']}

return dict(form=SQLFORM.grid(VentureWorker,
  csv=False,
  user_signature=True,
  args=[venture_id],
  maxtextlength=100,
  createargs=createargs,
  editargs = editargs,
  viewargs = viewargs,
  fields=fields),
subtitle=T('Venture Norms') + venture_name,
btn_back=btn_back)


I have only one return... How can I use process? Sorry for the questions so
newbies, is that this is my first serious project using web2py...


Thanks again, man!


On Sat, Jan 26, 2013 at 3:35 PM, Bruno Rocha rochacbr...@gmail.com wrote:

 I can think on two options.

 *1. Unique Key*

 db.define_table(table,
 Field(table_a, reference table_a),
 Field(table_b, reference table_b),
 Field(unikey, unique=True, notnull=True, compute=lambda row:
 %(table_a)s-%(table_b)s % row)
 )


 *2. Form validator*

 def check_unique(form):
 if db((db.table.table_a == form.vars.table_a)  (db.table.table_b ==
 form.vars.table_b)).count():
 form.errors.table_a = You cannot insert or edit a duplicate
 combination

 form = SQLFORM(db.table).process(onvalidation=check_unique)

 Mybe it can be implemented as a Field Validator, have to try.

 --





-- 

Re: [web2py] Many to Many relation

[Bruno Rocha]

the grid has onvalidation argument.


SQLFORM.grid(..., onvalidation=check_unique)

-- 

Re: [web2py] Many to Many relation

[__pyslan__ - Ayslan Jenken]

Work, man.

Thanks a lot!!


On Sat, Jan 26, 2013 at 4:12 PM, Bruno Rocha rochacbr...@gmail.com wrote:

 the grid has onvalidation argument.


 SQLFORM.grid(..., onvalidation=check_unique)

 --





-- 

Re: [web2py] Many to Many relation

[Anthony]

Option 3: https://groups.google.com/d/msg/web2py/CRPySzABQTk/8jylUabyFTQJ

Anthony

On Saturday, January 26, 2013 12:35:26 PM UTC-5, rochacbruno wrote:

 I can think on two options.

 *1. Unique Key*

 db.define_table(table,
 Field(table_a, reference table_a),
 Field(table_b, reference table_b),
 Field(unikey, unique=True, notnull=True, compute=lambda row: 
 %(table_a)s-%(table_b)s % row)
 )


 *2. Form validator*

 def check_unique(form):
 if db((db.table.table_a == form.vars.table_a)  (db.table.table_b == 
 form.vars.table_b)).count():
 form.errors.table_a = You cannot insert or edit a duplicate 
 combination

 form = SQLFORM(db.table).process(onvalidation=check_unique)

 Mybe it can be implemented as a Field Validator, have to try.


-- 

Re: [web2py] Many to Many relation

[__pyslan__ - Ayslan Jenken]

Very good, Anthony, very good!

Thanks!!


On Sat, Jan 26, 2013 at 7:17 PM, Anthony abasta...@gmail.com wrote:

 Option 3: https://groups.google.com/d/msg/web2py/CRPySzABQTk/8jylUabyFTQJ

 Anthony


 On Saturday, January 26, 2013 12:35:26 PM UTC-5, rochacbruno wrote:

 I can think on two options.

 *1. Unique Key*

 db.define_table(table,
 Field(table_a, reference table_a),
 Field(table_b, reference table_b),
 Field(unikey, unique=True, notnull=True, compute=lambda row:
 %(table_a)s-%(table_b)s % row)
 )


 *2. Form validator*

 def check_unique(form):
 if db((db.table.table_a == form.vars.table_a)  (db.table.table_b ==
 form.vars.table_b)).count():
 form.errors.table_a = You cannot insert or edit a duplicate
 combination

 form = SQLFORM(db.table).process(**onvalidation=check_unique)

 Mybe it can be implemented as a Field Validator, have to try.

  --





-- 

[web2py] Many to Many?

[Derek]

Let's say I am designing a database.

It has these elements:
Companies, Locations, Servers

Each company has one location, and one or more servers.
Each server has one and only one location.

So, company Telefónica de España has location Madrid.
They have four servers, Pablo, Alejandro, Guido, and Franco.

There are three locations in the database:
Madrid (Spain), Paris (France), and Riccone (Italy). Obviously, Pablo and 
Alejandro are in Spain, Guido is in Italy, and Franco is in France.

So, I want it so that if I change the name of the location (such as 
changing 'Madrid' to 'al-Mudaina') it will be updated on both the companies 
and servers.

To set this up, do I need to have two extra tables location to server and 
location to company or is there a better way to do it?

Re: [web2py] Many-to-many relations with CRUD Insert

[Christian Winterhager]

Yes, thats clear. Until now i use a program, which is based  on INFORMIXDB 
with Python und CGI. In this program (respectively programs) i do exactly 
that what you described above in your mail. It is not difficult to write 
these programs but it is slow and boring.

But now i know web2py and i wanted to use this tool and all of its benefits 
to create more elegant forms and views. So my Question is again: Is there 
any method to create an INPUT-Form (not for output, SELECT is easy!) with 
CRUD or SQLFORM for the many-to-many Relation book-author-verb?  

Or more precisely: is it possible to make a form with CRUD or SQLFORM, 
which includes all fields of book and a listbox with autors, so that i can 
select one or more authors and then klick the *only *submit-button of the 
form to insert the book and the corresponding ids in table verb? I think 
the problem is that we have 2 tables for CRUD/SQLFORM, which are not 
directly connected via foreign keys (namely book and author).

Re: [web2py] Many-to-many relations with CRUD Insert

[Johann Spies]

On 17 January 2012 11:03, Christian Winterhager 
christian.winterha...@onlinehome.de wrote:

 Yes, thats clear. Until now i use a program, which is based  on INFORMIXDB
 with Python und CGI. In this program (respectively programs) i do exactly
 that what you described above in your mail. It is not difficult to write
 these programs but it is slow and boring.

 But now i know web2py and i wanted to use this tool and all of its
 benefits to create more elegant forms and views. So my Question is again:
 Is there any method to create an INPUT-Form (not for output, SELECT is
 easy!) with CRUD or SQLFORM for the many-to-many Relation
 book-author-verb?

 Or more precisely: is it possible to make a form with CRUD or SQLFORM,
 which includes all fields of book and a listbox with autors, so that i can
 select one or more authors and then klick the *only *submit-button of the
 form to insert the book and the corresponding ids in table verb? I think
 the problem is that we have 2 tables for CRUD/SQLFORM, which are not
 directly connected via foreign keys (namely book and author).


In the 'verb' table you can use the type 'reference'  for 'book'  and
'list:reference' for authors (with the multiple-option).  Then you can have
a form using crud for input where you can select the book and the list of
authors.

See http://www.web2py.com/books/default/chapter/29/6?search=list%3Areference
.

I am working with a biliometric database and I have inserted constraints on
database level to prevent the same combination of appearing in the linking
table ('verb' in your case). I am working with Postgresql and found that
the web2py-communications with the database breaks when this constraint
complains.  So I had to build in code to check for the existence of that
combination of fields in the table before inserting it.

Regards
Johann



-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: [web2py] Many-to-many relations with CRUD Insert

[Johann Spies]

On 17 January 2012 11:27, Johann Spies johann.sp...@gmail.com wrote:

 I am working with a biliometric database


Sorry that must be bibliometric database
-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

Re: [web2py] Many-to-many relations with CRUD Insert

[Christian Winterhager]

I have also constraints on database level, but this is necessary for normal 
logical consistency of the database model. The problem is for me again - 
even if i use that reference or list.reference - that i cannot have *
ONLY* these fields

book.title
author.name

in my form on the screen and *only one submit-Button*, because CRUD accepts 
only one table and so there were 2 submit-buttons and 2 CRUD.create forms. 
As i said i want to insert the book-data (for simplicity here the title) 
without clicking the submit button, then select some author (better: one or 
more authors), then click submit, and the web2py-System must then insert 
the book, look for the new book-id and insert this book-id together with 
the author-id in the
connection-table verb. On a very low-level programming view with CGI, 
Python and informix (or just MySQL) this can be done without
great effort, but you have to write too much code. That is the reason why i 
want to have a simple form, created by CRUD or SQLFORM. 

By the way, i searched the Newsgroup here and i did'nt find anyone, who had 
this same problem. I'm wondering about that, because it is a standard 
situation in many commercial databases: you have producers and articles, 
but the connection between the two ist stored in a new table- say 
prod_art in form of a tupel (producer_id, article_id). How to put 

Producer-, 
Article-Data and 
informations about who produces which article (Table prod_art)

in only one FORM with only ONE Submit Button?

P.S. Naturally i have one solution, but i think it is a dirty one: 


   1. With CRUD make 2 INSERT-Forms for tables author and book.
   2. If FORM for the book is accepted, and we therefor know the new 
   book_id.
   3. go to the NEXTURL, i.e. the CRUD.create - FORM for the table verb
   4. In the Controller, before accepting the form, let verb.book_id = 
   book_id from step 2.
   5. Choose an author from the table verb. Thanks to this CRUD-Feature!
   6. Insert verb.

Thats all, but it is too complicated for Users, wo want to insert thousends 
of Books: 2 Screen, 2 Submit-Buttons, switching

between the screens. As in said, i build a very user-friendly solution with 
CGI, but i think, it must be done also with web2py

[web2py] Many-to-many relations with CRUD Insert

[Christian Winterhager]

One of the standard examples  of many-to-many Relations is a library. For 
simplicity i choose only 3 Tables:

db.define_table ('author',
Field ('name', 'string', length=100))

db.define_table ('verb',
   Field ('bnr', db.book ),  
   Field ('anr', db.author ), 
)

db.define_table ('book',
Field('title', 'string', length=250))

It is very easy to insert a new book, one or more author and the 
corresponding record
in the table verb. But i found no way to insert a book with the 
CRUD-Method. The 
complexityhttp://dict.leo.org/ende?lp=endep=DOKJAAsearch=complexitytrestr=0x8001
is that i first must insert a row into  table book, then one into table 
author and last i have to connect
the inserted rows with the corresponding numbers 'bnr' and 'anr' in table 
verb. Does anyone know if there
is a way to do that with CRUD?


 

Re: [web2py] Many-to-many relations with CRUD Insert

[Johann Spies]

On 16 January 2012 22:35, Christian Winterhager 
christian.winterha...@onlinehome.de wrote:

 One of the standard examples  of many-to-many Relations is a library. For
 simplicity i choose only 3 Tables:

 db.define_table ('author',
 Field ('name', 'string', length=100))

 db.define_table ('verb',
Field ('bnr', db.book ),
Field ('anr', db.author ),
 )

 db.define_table ('book',
 Field('title', 'string', length=250))

 It is very easy to insert a new book, one or more author and the
 corresponding record
 in the table verb. But i found no way to insert a book with the
 CRUD-Method. The 
 complexityhttp://dict.leo.org/ende?lp=endep=DOKJAAsearch=complexitytrestr=0x8001
 is that i first must insert a row into  table book, then one into table
 author and last i have to connect
 the inserted rows with the corresponding numbers 'bnr' and 'anr' in table
 verb. Does anyone know if there
 is a way to do that with CRUD?


I don't think you can do this in one step.

I would do the following:

1. Find the author in the author table and if it is not there, insert.  For
each author, collect the id of the record.
2. Then insert the book and get the id
3. Then for each author create a record in 'verb'. I would do this in the
background without any form involved.

Regards
Johann



-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)

[web2py] Many-to-many admin form

[Bruce Wade]

Hello, I know django has this ability so I am assuming web2py also does.
(Here is to hoping :D)

First the models:
db.define_table('adreports',
Field('reporttype', unique=True)
)

db.define_table('ads',
Field('member_id', db.auth_user, default=auth.user_id),
Field('points', 'integer', default=0),
Field('url'),
Field('language'),
Field('location'),
Field('accepted', 'boolean', default=False),
Field('viewable', 'boolean'),
Field('updated_at','datetime', default=request.now),
Field('added_at','datetime', default=request.now)
)

db.define_table('keywords',
Field('isapproved', 'boolean', default=False),
Field('key')
)

db.define_table('ad_report',
Field('ad_id', db.ads),
Field('reportad_id', db.adreports)
)

db.define_table('ad_keyword',
Field('ad_id', db.ads),
Field('keyword_id', db.keywords)
)

db.ads.url.requires = (IS_URL(), IS_NOT_IN_DB(db, db.ads.url))

db.ad_report.ad_id.requires = IS_IN_DB(db, db.ads.id, %(url)s)
db.ad_report.reportad_id.requires = IS_IN_DB(db, db.adreports.id,
%(reporttype)s)

db.ad_keyword.ad_id.requires = IS_IN_DB(db, db.ads.id, %(url)s)
db.ad_keyword.keyword_id.requires = IS_IN_DB(db, db.keywords.id, %(key)s)

--
Now I would like to be able, from the admin create a new ad and on the same
page add multiple keywords and/or reports for the ad. This would have to
happen with creating a new ad as well as when editing an existing ad.

With Django you use: admin.TabularInline and inlines on admin.ModelAdmin
classes
https://docs.djangoproject.com/en/dev/ref/contrib/admin/#inlinemodeladmin-objects

Has something similar being developed for web2py admin application?
-- 
Regards,
Bruce Wade
http://ca.linkedin.com/in/brucelwade
http://www.wadecybertech.com
http://www.warplydesigned.com
http://www.fitnessfriendsfinder.com

[web2py] Many to many query with two left joins - how to do in Web2py?

[Cliff]

Here is the model, simplified:

db.define_table('purchase_orders',
Field('issue_date', 'date',
  comment = SPAN(a_comment, _style='color:orange;')),
Field('number', length=24, requires=IS_NOT_EMPTY(),
  required=True, notnull=True,
  comment=SPAN('Required', _style='color:orange;')),
Field('closed', 'boolean', readable=False, writable=False,
default=False),
format = '%(number)s'
)
db.define_table( 'products',
Field('name', length=256, required=True, notnull=True),
Field('internal_item_number', length=36),
format = '%(name)s'
)
db.define_table('purchase_order_products',
Field('purchase_order_id', db.purchase_orders),
Field('product_id', db.products),
Field('quantity', 'decimal(12,3)'),
)

in SQL I can make this query:
SELECT issue_date, number, name FROM purchase_orders LEFT JOIN
purchase_order_products ON purchase_order_products.purchase_order_id =
purchase_orders.id LEFT JOIN products ON
purchase_order_products.product_id = products.id

How do I do this in Web2py?

[web2py] Many to Many on GAE

[K.R.Arun]

I'm working on a project in which I have several tables with many to
many relationship.
So I started to try it with example given in book like this.

Models
-
db.define_table('person', Field('name'))
db.define_table('dog',Field('name'))
db.define_table('ownership',Field('person', db.person),Field('dog',
db.dog))

Controller

def index():
form1 = FORM(INPUT(_name=person), INPUT(_name=dog),
INPUT(_type=submit))
if form1.accepts(request.vars,session):
 id1 = db.person.insert(name=request.vars.person)
 id2 = db.dog.insert(name=request.vars.dog)
 db.ownership.insert(person=id1, dog = id2)
form3 = SQLFORM(db.ownership)
persons_and_dogs = db((db.person.id==db.ownership.person) 
(db.dog.id==db.ownership.dog))

x= dict(message=T('Hello World'), form1=form1, form3=form3,
persons_and_dogs = persons_and_dogs,zform1 = zform1, zform3 = zform3,
zpersons_and_dogs = zpersons_and_dogs)
return response.render(x)

view
--
{{=form1}}
{{=form3}}
{{for row in persons_and_dogs.select():}}
{{=XML(row.person.name)}}
{{=XML( row.dog.name)}}br
{{pass}}
--

It works ok. But I've to host my project on GAE.
Does this work on GAE (because of JOINS)?

I read in the book, about listtype
Do anyone know how to port above code using those (list:reference)?

 here is the snippet from book 
 db.define_table('tag',Field('name'),format='%(name)s')
 db.define_table('product',
Field('name'),
Field('tags','list:reference tag'))
===

Any help appreciated :-)

[web2py] many to many inline form

[Joe Wakefield]

So I'm trying to migrate an old rails app that broke a while back, and
I came across a rather difficult situation. I boiled the problem down
to this simplified mockup: http://yfrog.com/ghbeanjarsp

The real app has nothing to do with jelly beans, but this is the issue
at hand. Basically there are tables joined in a many to many
relationship, with an extra column for amount (because no bean is
unique). Creating/updating jars must be done inline with the rest of
the jar's form.

I was reading stuff about custom widgets, and that seemed a likely
direction. However, I'm hoping for a more direct suggestion on how you
guys would achieve this.

[web2py] many-to-many, use list:reference or separate table?

[Carlos]

Hi all,

I am wondering, with respect to many-to-many relationships, is it
better to use list:reference or a separate table (with foreign
keys)?.

I refer to 'simple' many-to-many where no extra attributes need to be
stored (otherwise the separate table is the only choice).

Thanks,

   Carlos

[web2py] Many to Many TypeError: int() argument must be a string or a number, not 'list'

[johntynan]

I would like to create a many-to-many relationship in the following
model, where many stories can appear in many different
roadtrips (think songs to mixtapes).

db.define_table(
'roadtrip',
Field('name'),
Field('description', 'text'),
Field('created_by',default=me,writable=False,readable=False),
 
Field('created_on','datetime',default=request.now,writable=False,readable=False)
)

db.define_table(
'story',
Field('user_id', db.users,readable=False,writable=False),
Field('roadtrip_id', db.roadtrip),
Field('story_id', 'integer'),
Field('title'),
Field('latitude'),
Field('longitude'),
Field('comment', 'text'),
Field('created_by',default=me,writable=False,readable=False),
 
Field('created_on','datetime',default=request.now,writable=False,readable=False)
)

db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'roadtrip.id','%
(name)s')

I am able to create a multiple-select element on the CRUD form by
simply adding multiple=True to this

db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'roadtrip.id','%
(name)s', multiple=True)

However, I am receiving the error:

TypeError: int() argument must be a string or a number, not 'list'

I posted the traceback here: http://pastie.textmate.org/1187996

(as an aside, is there a better way to post a traceback that is more
readable, should I just copy/paste from the html error page, as
opposed to the logged file under errors?)

[web2py] Many To Many Select

[Al]

Given a many-to-many relationship between table1 and table2 and a row
in table1, I am trying to figure out the fastest way to select all the
rows in table2 that are associated with the row. (I'm still trying to
pick up web2py, so thanks for everyone's patience if I've missed the
blatently obvious.)

From the web2py book documentation, the best I can figure out is
something like this:
db.define_table('link_table',
  Field('table1', db.table1),
  Field('table2', db.table2)
  )

# Pick the row from table 1
r = db(db.table1.name == Example).select()[0]

linked_view = db(db.table1.id == db.link_table.table1)  (db.table2.id
== db.linked_table.table2))

for row in linked_view(db.table1.id == r.id).select([fields from
table2 I want])
  # Do something clever for the matching rows I've found
  pass

But I keep thinking there ought to be something cleverer that I can
do, especially given that r has a set containing the rows of
link_table that contain the entries of table2 that I want. But I can't
figure out how to leverage that set into a shorter query.

Thanks,
Al