librelist archives

« back to archive

n:m helper table with additional fields

n:m helper table with additional fields

From:
Gunter Vasold
Date:
2012-09-04 @ 15:35
Hi,

I'm new to Flask-SQLAlchemy  and came across a problem creating
n:m-relationships.
The manual for flask-sqlalchemy (

http://packages.python.org/Flask-SQLAlchemy/models.html#many-to-many-relationships)
states:

'For this helper table it is strongly recommended to *not* use a model but
an actual table.'

Using Table() works well for me, but now I have a case where the helper
table does not only contain the two Foreign keys but also some more
relationship information.

beziehung = db.Table('beziehung',
            db.Column('left_item_id', db.ForeignKey('left_item.id')),
            db.Column('right_item_id', db.ForeignKey('right_item.id')),
            db.Column('quality', db.String)
            )

From my point of view it would come in handy if I could use a model to
access the quality field. So 2 questions:

1) Why is it not recommended to use a model here? Is it only performance?
2) Is there a suggested solution for this problem?

Thanks in advance,

Gunter

Re: [flask] n:m helper table with additional fields

From:
Andres Rand
Date:
2012-09-04 @ 16:00
Hi!

1) From sqlalchemy Relationships API manual:
Secondary - for a many-to-many relationship, specifies the
intermediary table, and is an instance of Table. The secondary keyword
argument should generally only be used for a table that is not
otherwise expressed in any class mapping, unless this relationship is
declared as view only, otherwise conflicting persistence operations
can occur.
secondary may also be passed as a callable function which is evaluated
at mapper initialization time.
2) If you want to use the table as you described then you should look
into sqlalchemy Association Object
http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#association-pattern
It should be doable in Flask-SQLAlchemy too.

HTH,

--
Andres Rand
hrrand@gmail.com

2012/9/4 Gunter Vasold <gunter.vasold@gmail.com>:
> Hi,
>
> I'm new to Flask-SQLAlchemy  and came across a problem creating
> n:m-relationships.
> The manual for flask-sqlalchemy
> 
(http://packages.python.org/Flask-SQLAlchemy/models.html#many-to-many-relationships)
> states:
>
> 'For this helper table it is strongly recommended to not use a model but an
> actual table.'
>
> Using Table() works well for me, but now I have a case where the helper
> table does not only contain the two Foreign keys but also some more
> relationship information.
>
> beziehung = db.Table('beziehung',
>             db.Column('left_item_id', db.ForeignKey('left_item.id')),
>             db.Column('right_item_id', db.ForeignKey('right_item.id')),
>             db.Column('quality', db.String)
>             )
>
> From my point of view it would come in handy if I could use a model to
> access the quality field. So 2 questions:
>
> 1) Why is it not recommended to use a model here? Is it only performance?
> 2) Is there a suggested solution for this problem?
>
> Thanks in advance,
>
> Gunter

Re: [flask] n:m helper table with additional fields

From:
Gunter Vasold
Date:
2012-09-05 @ 12:06
Hi Andres,

using association objects works great. Thanks for pointing me in the right
direction.

Regards,

Gunter

On Tue, Sep 4, 2012 at 6:00 PM, Andres Rand <hrrand@gmail.com> wrote:

> Hi!
>
> 1) From sqlalchemy Relationships API manual:
> Secondary - for a many-to-many relationship, specifies the
> intermediary table, and is an instance of Table. The secondary keyword
> argument should generally only be used for a table that is not
> otherwise expressed in any class mapping, unless this relationship is
> declared as view only, otherwise conflicting persistence operations
> can occur.
> secondary may also be passed as a callable function which is evaluated
> at mapper initialization time.
> 2) If you want to use the table as you described then you should look
> into sqlalchemy Association Object
>
> http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#association-pattern
> It should be doable in Flask-SQLAlchemy too.
>
> HTH,
>
> --
> Andres Rand
> hrrand@gmail.com
>
> 2012/9/4 Gunter Vasold <gunter.vasold@gmail.com>:
> > Hi,
> >
> > I'm new to Flask-SQLAlchemy  and came across a problem creating
> > n:m-relationships.
> > The manual for flask-sqlalchemy
> > (
> 
http://packages.python.org/Flask-SQLAlchemy/models.html#many-to-many-relationships
> )
> > states:
> >
> > 'For this helper table it is strongly recommended to not use a model but
> an
> > actual table.'
> >
> > Using Table() works well for me, but now I have a case where the helper
> > table does not only contain the two Foreign keys but also some more
> > relationship information.
> >
> > beziehung = db.Table('beziehung',
> >             db.Column('left_item_id', db.ForeignKey('left_item.id')),
> >             db.Column('right_item_id', db.ForeignKey('right_item.id')),
> >             db.Column('quality', db.String)
> >             )
> >
> > From my point of view it would come in handy if I could use a model to
> > access the quality field. So 2 questions:
> >
> > 1) Why is it not recommended to use a model here? Is it only performance?
> > 2) Is there a suggested solution for this problem?
> >
> > Thanks in advance,
> >
> > Gunter
>