Many to Many (sort of)

14 views
Skip to first unread message

mdipierro

unread,
Dec 30, 2008, 6:37:49 PM12/30/08
to web2py Web Framework
New in trunk. Please give this a try:

db=SQLDB('sqlite://storage.db')
db.define_table('person',SQLField('name'))
db.define_table('dog',SQLField('name'),SQLField('person','text'))
db.dog.person.requires=IS_IN_DB
(db,'person.id','person.name',multiple=True)

This is not the same many2many as Django and Rails do it (because does
not use an intermediate table) but for practical purposes it works the
same, if not better, and works on GAE too. It will possible to change
the widget to make it look different.

The trick is to turn a reference into a 'text' field and set the
IS_IN_DB or IS_IN_SET validator to multiple=True.

If you like it and if nothing breaks, this will go in 1.55.

Mind that I had to make quite a lot of changes to make this work
seamlessly.

Massimo

mdipierro

unread,
Dec 30, 2008, 7:24:00 PM12/30/08
to web2py Web Framework
I made an improvement and re-posted it.

The references are stored as |1|3|...| i.e. as ids separated by | in a
text field. This way it is easy to edit them manually.
Invalid references are deleted when the field is edited, ignored
otherwise.
Values can be searched efficiently with db.table.field.like('%|4|%').
Too mad the LIKE operator is not supported (yet) on GAE.

We could store as 1,3,... instead of |1|3|...| but this would make
searching more difficult because 12 contains 1 as a substring but |12|
does not contain |1| as a substring.

Thoughts?

Massimo

Fran

unread,
Dec 30, 2008, 7:37:09 PM12/30/08
to web2py Web Framework
On Dec 30, 11:37 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> New in trunk. Please give this a try:

Looking promising :)

However fails if I try to use uuid instead of id:
db.define_table('owner',SQLField('uuid',length=64,default=uuid.uuid4
()),SQLField('name'))
db.define_table('dog',SQLField('name'),SQLField('owner','text'))
db.dog.owner.requires=IS_IN_DB
(db,'owner.uuid','owner.name',multiple=True)

Also, currently it does break other stuff as the new 'multiple' field
isn't optional - I get this in my other controllers:
if not field.requires.multiple:
AttributeError: 'IS_NULL_OR' object has no attribute 'multiple'

> If you like it and if nothing breaks, this will go in 1.55.

Shaping up to be a big release :)

F

Fran

unread,
Dec 30, 2008, 7:44:54 PM12/30/08
to web2py Web Framework
On Dec 30, 11:37 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> Thoughts?

I'd prefer the UI to be more like t2.tag_widget() in that select
should be multi- by default...not having to hold the Ctrl key down.

F

mdipierro

unread,
Dec 30, 2008, 7:45:43 PM12/30/08
to web2py Web Framework
1) try svn 602 or launchpad 330. They should work with uuid.

2) IS_NULL_OR(IS_IN_DB(...multiple=True))
is not supposed to work because if multiple=True IS_IN_DB does not
poses constraints on how many you select. You may select 0. Hence it
would be equivalent to IS_IN_DB(...multiple=True)

Fran

unread,
Dec 30, 2008, 7:47:16 PM12/30/08
to web2py Web Framework
On Dec 30, 11:37 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> New in trunk. Please give this a try:

No referential integrity.
If I delete an owner who was associated with a dog then the reference
to the deleted owner stays in the dog.owner field.

F

mdipierro

unread,
Dec 30, 2008, 7:48:47 PM12/30/08
to web2py Web Framework

All it takes is build a new widget that look like t2.tag_widget() and
acts like gluon.sqlhtml.MultipleOptionsWidget.widget since the logic
does not change.

Can you help with this? Can you do so that it is CSS neutral (perhaps
using checkboxes)?

Massimo

mdipierro

unread,
Dec 30, 2008, 7:51:41 PM12/30/08
to web2py Web Framework
No. But but it is done in such a way that does not break anything and
it is not visible when you edit the field. The price to pay to make it
fast and work on GAE.

Fran

unread,
Dec 30, 2008, 7:51:59 PM12/30/08
to web2py Web Framework
On Dec 31, 12:45 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> 1) try svn 602 or launchpad 330. They should work with uuid.

I still get this error:
value not in database!

The names show up fine but when I submit this error shows up.
I have:
db.define_table('owner',SQLField('name'),SQLField
('uuid',length=64,default=uuid.uuid4()))
db.define_table('dog',SQLField('name'),SQLField('owner','text'))
db.dog.owner.requires=IS_IN_DB
(db,'owner.uuid','owner.name',multiple=True)

> 2) IS_NULL_OR(IS_IN_DB(...multiple=True))
> is not supposed to work because if multiple=True IS_IN_DB does not
> poses constraints on how many you select. You may select 0. Hence it
> would be equivalent to IS_IN_DB(...multiple=True)

I'm not trying to add Multiple= to the IS_NULL_OR().
I'm finding that this new functionality has broken other tables which
use plain IS_NULL_OR() without having anything specified for
Multiple=.
Hence this breaks backward compatibility.

F

Fran

unread,
Dec 30, 2008, 7:59:47 PM12/30/08
to web2py Web Framework
On Dec 30, 11:37 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> New in trunk. Please give this a try:


Another thing which doesn't work, but would be good to have possible
is this kind of thing:
db.dog.owner.display=lambda id: (id and [db(db.owner.id==id).select()
[0].name] or ["None"])[0]

i.e. be able to display the name in t2.display()

(Of course, I want this working with uuids instead of ids too...as it
does in other areas)

Many thanks,
Fran.

mdipierro

unread,
Dec 30, 2008, 8:02:03 PM12/30/08
to web2py Web Framework
thanks. Think I got them now. Fixed in 604/332.

Massimo

mdipierro

unread,
Dec 30, 2008, 8:06:25 PM12/30/08
to web2py Web Framework
try something like this...

db.dog.owner.display=lambda x: ', '.join([db(db.person.uuid=id).select
()[0].name for id in x[1:-1].split('|')])

Massimo

mdipierro

unread,
Dec 31, 2008, 8:57:54 AM12/31/08
to web2py Web Framework
BTW... it works great with this jquery plugin:

http://lab.arc90.com/tools/jquery_multiselect/

Jon

unread,
Jan 2, 2009, 2:42:32 PM1/2/09
to web2py Web Framework
Awesome! I think this may stem from an earlier discussion we had on
the IS_IN_DB and my 'noob request for a many-to-many? You rock
Massimo.

sudhakar m

unread,
Jan 2, 2009, 9:51:42 PM1/2/09
to web...@googlegroups.com
Referencing the table before definition causes error. Following test case fails.

db.define_table("Ground",
      SQLField("won_by", db.Team),
      SQLField("Match_ids", db.Match))

db.define_table("Match",
      SQLField("Ground_id", db.Ground),
      SQLField("win_margin", "string"),
      SQLField("no", "integer"))

db.Match.Ground_id.requires=IS_IN_DB(db, 'Ground.id')
db.Ground.Match_ids.requires=IS_IN_DB(db, 'Match.id', multiple=True)

I am using many-to-many feature introduced in 1.55 rc3. I think we may have to scan the model file fully before creating the relationship between them.

Correct me if I am wrong.

Sudhakar.M

sudhakar m

unread,
Jan 2, 2009, 10:03:27 PM1/2/09
to web...@googlegroups.com
My mistake. It should be


db.define_table("Ground",
      SQLField("won_by", db.Team),
      SQLField("Match_ids", "text"))


db.define_table("Match",
      SQLField("Ground_id", db.Ground),
      SQLField("win_margin", "string"),
      SQLField("no", "integer"))

db.Match.Ground_id.requires=
      IS_IN_DB(db, 'Ground.id')
      db.Ground.Match_ids.requires=IS_IN_DB(db, 'Match.id', multiple=True)

Thanks,

2009/1/3 sudhakar m <sudha...@gmail.com>

Yarko Tymciurak

unread,
Jan 2, 2009, 11:07:03 PM1/2/09
to web...@googlegroups.com
On Fri, Jan 2, 2009 at 9:03 PM, sudhakar m <sudha...@gmail.com> wrote:
My mistake. It should be


db.define_table("Ground",
      SQLField("won_by", db.Team),
      # was: SQLField("Match_ids", db.Match)
      SQLField("Match_ids", "text"))

I think you mean to have SQLField("Match_ids", integer)  as the reference to an id is an integer.  This is a good way to get around that db.Match is not yet defined when db.Ground is being defined...
 
Regards,
Yarko

sudhakar m

unread,
Jan 3, 2009, 3:59:57 AM1/3/09
to web...@googlegroups.com
In my case Match_ids column contains collection of match_id's seperated by '|' in text field. This allows me to have many-to-many relation between Ground & Match with out defining additional join table.

Thanks,
Sudhakar.M

Yarko Tymciurak

unread,
Jan 3, 2009, 2:55:16 PM1/3/09
to web...@googlegroups.com
Ah, yes - of course.... I knew in the back of my mind I was missing something like this in this thread ;-)
Reply all
Reply to author
Forward
0 new messages