Many to Many (sort of)

[mdipierro]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

thanks. Think I got them now. Fixed in 604/332.

Massimo

[mdipierro]

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]

BTW... it works great with this jquery plugin:

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

[Jon]

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]

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]

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]

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]

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]

Ah, yes - of course.... I knew in the back of my mind I was missing something like this in this thread ;-)