python - 无法使用 SQLAlchemy 获得多对多关系以正常工作

Question

我有 3 张桌子；用户、位置和 location_favorite。location_favorite 有点像关联表。我试图能够为使用该表的用户创建最喜欢的位置列表。

用户类如下所示：

import datetime
from sqlalchemy import Column, Integer, String, Date, ForeignKey
from sqlalchemy.orm import relationship

from model import Base
from model.address import Address
from model.group import Group
from model.location_favorite import LocationFavorite


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, nullable=False, unique=True, primary_key=True)
    username = Column(String, nullable=False, unique=True)
    password = Column(String, nullable=False)
    reset_token = Column(String, default=None)
    email = Column(String, nullable=False, unique=True)
    account_status = Column(Integer, nullable=False, default=0)
    user_group = Column(Integer, ForeignKey("user_groups.id"), \
    nullable=False, default=1)
    registration_date = Column(Date, nullable=False, 
    default=datetime.datetime.now())
    last_online = Column(Date, default=datetime.datetime.now())


    # Relationships
    address = relationship(Address, uselist=False, back_populates="user")
    group = relationship(Group, back_populates='users')
    spots = relationship("Location")
    favorite_spots = relationship('Location', secondary='location_favorite', 
    backref='favorited_by')

位置类如下所示：

class Location(Base):
    __tablename__ = 'location'
    id = Column(Integer, primary_key=True, nullable=False)
    title = Column(String, unique=True)
    description = Column(String, nullable=False)
    creator_id = Column(Integer, ForeignKey('users.id'))
    latitude = Column(Float)
    longtitude = Column(Float)
    creation_date = Column(Date, default=datetime.now)

    # Relationships
    creator = relationship('User', foreign_keys=creator_id, 
    back_populates='spots')
    events = relationship('Event')
    favorited_by = relationship('User', secondary='location_favorite', 
    backref='favorite_spots')

location_favorite 类如下所示：

class LocationFavorite(Base):
    __tablename__ = 'location_favorite'
    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    location_id = Column(Integer, ForeignKey('location.id'), 
    primary_key=True)
    creation_date = Column(Date, default=datetime.now)

现在这给了我以下例外：

Could not determine join condition between parent/child tables on 
relationship User.favorite_spots

我想这是一个合乎逻辑的响应，因为除了 user_id 之外，用户没有任何对 location_favorite 的引用，它是复合主键的一部分。我只是想不出建立这种关系的最佳方式是什么。

标签： pythonsqlalchemy