SQLFORM: many-to-many implementation?

[g-man]

I see from the Cookbook tutorial that web2py knows how to do a one-to-
many relationship natively.

By the inclusion of the foreign table 'Category' reference in the
declaration of the 'Recipe' table define_field 'category', we get an
SQLFORM that knows to make a select control (dropdown) in the form,
and knows to put the category.id value into the recipe.category_id
field of the saved record. So far so good.

Unless I'm missing something, I don't see the same functionality for a
many-to-many relationship (M2M). I admit I have not tested it, but if
this were the case, the Manual would have mentioned it, and the
example would have used it, I'm assuming, simply because of the
crucial importance of the M2M in almost every application.

From my understanding of web2py, it seems I have to do all the heavy
lifting to make an M2M work, like extracting ids, creating references
in several tables, inserting, etc. This does not fit with the rest of
web2py's programming paradigm, and perhaps there is a simple way for
SQLFORM to do these tasks automagically for an M2M as easily as it
does now for a one-to-many.

What got me thinking about this was, while designing a form for an M2M
app, I realized the form for a 'new' M2M record looked exactly like
the form for a 'new' Recipe from the Cookbook example:

Thing: [text field]
Tag: [dropdown select]

(Admittedly, that only assigns one Tag to the new Thing, but more
could be added [or removed] later on the 'edit' page.)

So, what happens on save for the one-to-many case is that the id from
the Tag gets put into the Thing.tag field:

Thing <-- Tag

web2py knows how to do this because of the 'Tag' table declaration in
the 'Thing' field of the define_field statement.

The only difference for an M2M save is that the ids from both records
get put into a shared table:

Thing --> JOIN <-- Tag

web2py could know how to do this by a special 'Thing_Tag' table name
(signifying a join table, with the compound name matching each
contributor table's name), along with corresponding table declarations
in each of the join table's define_field statements.

Rails does this by reading 'has_many' declarations in the model class,
but web2py could know just by the special form of the join table name,
reinforced by the field qualifiers as is done now in a similar way for
the one-to-many .

The hassle for doing this programmatically is that, for every Tag
reference, I have to:
- Find the Tag.id from the SELECT and remember it;
- Insert the new Thing record (to get an id);
- Insert a new Thing_Tag (JOIN table) record with the new Thing.id
along with the remembered Tag.id.
- Only after the last step is the transaction complete, and if there
is a bomb somewhere, the Tag may not get saved, there might have to be
rollbacks and error messages, etc.

Sure, I can do that (just like you do in your dog purchases example),
but it seems like only a small step to handle this in the SQLFORM,
which does so much magic already... I would just feel a whole lot
better if all this housekeeping was done by a nicely tested
infrastructure, rather than by my own invention for every new case.

If I missed something, and this is the way web2py works already, just
chalk it up to my learning curve!

[Massimo Di Pierro]

It is true that web2py does not have a form widget to handle m2m.
The reason is that there is not a single way of doing it and I do not
want to lock the user in one solution and it would be too much magic.

Here is an example of m2m:

(thing) user has name
(tag) product has name and price
(thing_tag) purchase references user, product and has a quantity and
date

think_tag in general can have more attributes than just a reference
to thing and a reference to tag. If I am looking at a user, I cannot
figure just from the m2m how to combine product info with purchase
and building a form.

Yet web2py does something for you:

db.define_table('user',SQLField('name'))
db.define_table('product',SQLField('name'),SQLField('price','double'))
db.define_table('purchase',SQLField('buyer',db.user),SQLField
('product',db.product'),SQLField('qty','integer'),SQLField
('date','date'))

def purchased_by(user_id): return db((db.purchase.buyer==user_id)&
(db.purchase.product==db.product.id))

for row in purchased_by(user_id).select():
print 'you bought %i of %i at $%f' % (row.purchase.qty,
row.product.name, row.product.price)

Anyway if people have thought about this and want to propose such
widget (made of a helper + validator) I would like to see it.

Massimo

[Tito Garrido]

Would be great an Ajax widget to work with M2M fields... django have one in admin area...

Here is one great M2M widget, but it´s in Portuguese, sorry:
http://marinho.webdoisonline.com/blog/p/153/

--
Linux User #387870
____
_/_õ|__|
º[ .-.___.-._| . . . .
_(o).__(o).:_____

[voltron]

Just looking at that gives me shivers down my spine :-). I coded
something like that from scratch using Jquery. My biggest problem was
the management of the ids generated for the fields for identification
by the backend, it did not work properly. Plone and ToscaWidgets has
them too.

On May 22, 2:26 pm, "Tito Garrido" <titogarr...@gmail.com> wrote:
> Would be great an Ajax widget to work with M2M fields... django have one in
> admin area...
>
> Here is one great M2M widget, but it´s in Portuguese, sorry:http://marinho.webdoisonline.com/blog/p/153/
>

> On Thu, May 22, 2008 at 1:29 AM, Massimo Di Pierro <mdipie...@cs.depaul.edu>

[Massimo Di Pierro]

Could you describe what you want it to do?

[g-man]

Good, Massimo ... much food for thought. I shall experiment and see
what happens!

[g-man]

OK, your example clarified much ... the little function with a passed-
in criteria and returned SQLRows object is simple and clean.

Am I correct in thinking that your example works like this?

# All purchases by 'buyer'--
def purchased_by(user_id):
return db(db.purchase.buyer==user_id).select()

# All purchases of 'product' by 'buyer'--
def purchased_by(user_id, product_id):
return
db((db.purchase.buyer==user_id)&(db.purchase.product==product_id)).select()

For my understanding, I had to add few .select() and the product_id
arg... am I right? If so, this still requires messing with obtaining
the field.id for some attribute we query. It would be nice to have the
db().select be able to use something like
id=db.table.field.find_id('name') to cut out one preliminary step of
assignment... which I guess I could create in the model.

I do see how adding additional attributes to the join table provides
more useful information related to a 'purchase'. My naive baby-step
was only concerned with tagging for now, and in that case an
automagical solution would make sense, but as soon as we start adding
additional dimensions to the problem, the complexity grows
exponentially.

Python has certain design principle 'Golden Rules' which you have
quoted, and I support them. One such guiding principle of Rails is
'Convention over Configuration', meaning that the most common use case
(the 80% of Pareto's Law, or 2 standard deviations) becomes the
default, and the oddball (long tail) can always be made to work with
some extra effort on the programmer's part.

So, why not have an SQLFORM that does the default (conventional)
behavior of simply saving references for the containing table and
partner back to the generic join table? That would handle the majority
of use cases, and remove some ugly hand-work of doing all the DB
manipulation (saving the target, returning the id, then creating the
join with both ids). It would also be a mirror of what SQLFORM does
already, which is why I was kind of thinking it did that already.

Then, once the basic work is done by form.accept and we have a join
reference, the programmer can add other functionality as needed.

An example...
---
company_db:

- worker:
- name
- department_id
# works in one department

- department:
- name
- worker_id
# only one worker manages the department

- project:
- name
- department_id
# is assigned to one department

- project_worker:
- project_id
- worker_id
- date
- hours
# date worker started on project, how many hours worked
# m2m workers to projects
# naming convention tells web2py which tables join

---
department new or edit SQLFORM:
- default SELECT dropdown allows change worker (manager)
- just like the Cookbook example - nothing new here

worker new or edit SQLFORM:
- default SELECT dropdown allows change department of worker
- and
- custom SELECT dropdown allows add or remove projects for worker
- (pass project.name field to SQLFORM, web2py parses table names
to deduce join table name)

project new or edit SQLFORM:
- default SELECT dropdown allows change department of project
- and
- custom SELECT dropdown allows add or remove worker for project
- (pass worker.name field to SQLFORM, web2py parses table names to
deduce join table name)

So that makes the basic functionality. I suppose one could do more
with join table fields, like the datestamp thing, but I can't see that
far right now.

Suppose you visit a worker edit page and add a project. If you went
over and visited the project next, you would then see the worker, so
the assignments are actually mirror images of each other.

I did something like this for my Rails app, and using AJAX, I made a
little area that showed all the workers for a project, so as you made
updates, the list would change. Cute!

In conclusion, it seems that without this 'balancing' of SQLFORM for
the m2m case, its utility is somewhat limited. We, the programmers,
are forced to get our hands dirty with all the db resulting
manipulation, which web2py has spoiled us for by doing so much magic
already!

Here is some sophisticated discussion on how one other web framework
is solving problems like this:
http://blog.hasmanythrough.com/2006/8/19/magic-join-model-creation

On May 21, 9:29 pm, Massimo Di Pierro <mdipie...@cs.depaul.edu> wrote:

[mdipierro]

I am digesting this. I will be flying in the next couple of days so I
may be slow.

[g-man]

Of course, we could always tell web2py what the name of the join table
is (if has an arbitrary name), as another framework does with:

assignments:
- worker_id
- project_id
- date
- duration
worker:
(has many 'projects' through 'assignments')
project:
(has many 'workers' through 'assignments')