On Fri, Mar 2, 2018 at 1:17 AM, <jens.troe...@gmail.com> wrote:
> Hi,
>
> I've been playing with the Many-to-Many relationship from the documentation.
> Suppose I have a student and teacher and would like to define a
> bidirectional “favorite” relationship between the two, i.e. if a student is
> a favorite of a teacher then the teacher is also a favorite of that student:
>
> favorite_association_table = Table("favorite_associations", Base.metadata,
> Column("student_id", Integer, ForeignKey("students.id"),
> nullable=False),
> Column("teacher_id", Integer, ForeignKey("teachers.id"),
> nullable=False),
> )
>
> and in the Teacher class:
>
> favs = relationship("Student", secondary=favorite_association_table,
> backref="favs")
>
> Now I observe the following:
>
>>>> teacher.favs
> []
>>>> student.favs
> []
>>>> student.favs.append(teacher)
>>>> teacher.favs.append(student)
>
> which creates two entries in the association table:
>
> mysql> select * from favorite_associations;
> +----------------------------------+----------------------------------+
> | student_id | instructor_id |
> +----------------------------------+----------------------------------+
> | 030ced9060d2460fa30936cffd2e0a0a | 08315ae48d574bc3ac29526c675e67fc |
> | 030ced9060d2460fa30936cffd2e0a0a | 08315ae48d574bc3ac29526c675e67fc |
> +----------------------------------+----------------------------------+
> 2 rows in set (0.00 sec)
>
> That is probably alright, but not really desirable. Now, when I attempt to
> delete either the student or the teacher from the other, I get an error:
the above table is relationally incorrect - you should never have
duplicate data inside of a table like that, and the student_id and
instructor_id columns should normally form the primary key, or at
least a unique constraint of some kind. But beyond that, is a
"favorite" inherently bidirectional? usually "favorites" have a
direction, A might be B's favorite, but B is not A's favorite.
>
>>>> student.favs.remove(teacher)
> […]
> sqlalchemy.orm.exc.StaleDataError: DELETE statement on table
> 'favorite_association' expected to delete 1 row(s); Only 2 were matched.
>
> I guess I could avoid the list.remove() and build a query like:
if you *do* want to add on both ends but it's "unique" anyway, try
using collection_class=set with your relationship. that will
eliminate dupes on the Python side and you can set up the table rows
as unique.
>
> delete from favorite_associations where student_id='...'
>
> but that seems clumsy in the context of the ORM.
>
> So my question is: what is the proper and recommended way of implementing
> and handling such a bidirectional relationship?
>
> Thank you!
> Jens
>
>
>
>
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
