join - Flask-SQLAlchemy 查询连接关系表

Question

我正在使用 Flask 和 SQLAlchemy 构建一个应用程序。我基本上有 3 个表:users、friends 和 bestFriends:

一个用户可以有很多 friend ，但只有一个最好的 friend 。所以我希望我的模型是关系型的。 '用户'和'友谊'之间的关系'一对多'和'用户'和'最好 friend '之间的'一对一'。

这是我的模型:

from app import db
from sqlalchemy.orm import relationship, backref
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

class users(db.Model):

    __tablename__ = "Users"

    id = db.Column(db.Integer, primary_key=True)
    userName = db.Column(db.String, nullable=False)
    userEmail = db.Column(db.String, nullable=False)
    userPhone = db.Column(db.String, nullable=False)
    userPass = db.Column(db.String, nullable=False)

    friendsR = db.relationship('friendships', backref='friendships.friend_id', primaryjoin='users.id==friendships.user_id', lazy='joined')

    def __init__(self, userName, userEmail, userPhone, userPass):
        self.userName = userName
        self.userEmail = userEmail
        self.userPhone = userPhone
        self.userPass = userPass

    def __repr__(self):
        return '{}-{}-{}-{}'.format(self.id, self.userName, self.userEmail, self.userPhone)

class friendships(db.Model):

    __tablename__ = "Friendships"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, ForeignKey('Users.id'), nullable=False)
    friend_id = db.Column(db.Integer, ForeignKey('Users.id'), nullable=False)

    userR = relationship('users', foreign_keys='friendships.user_id')
    friendR = relationship('users', foreign_keys='friendships.friend_id')

    def __init__(self, user_id, friend_id):
        self.user_id = user_id
        self.friend_id = friend_id


    def __repr__(self):
        return '{}-{}-{}-{}'.format(self.user_id, self.friend_id)


class bestFriends(db.Model):

    __tablename__ = "BestFriends"

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, ForeignKey('Users.id'), nullable=False)
    best_friend_id = db.Column(db.Integer, ForeignKey('Users.id'), nullable=False)

    user = relationship('users', foreign_keys='bestFriends.user_id')
    best_friend = relationship('users', foreign_keys='bestFriends.best_friend_id')


    def __init__(self, user_id, best_friend_id):

        self.user_id = user_id
        self.best_friend_id = best_friend_id


    def __repr__(self):
        return '{}-{}-{}-{}'.format(self.user_id, self.best_friend_id)

我需要能够查询登录用户的 friend 列表以及该用户最好的 friend ，以防万一。我还需要对结果进行分页:

这是我的 app.py 函数，用于显示用户的 friend :

@app.route('/friendList<int:page>', methods=['GET', 'POST'])
@app.route('/friends')
def friendList(page=1):

if not session.get('logged_in'):
        return render_template('login.html')
    else:
        userID = session['user_id']

        userList = users.query.join(friendships).add_columns(users.id, users.userName, users.userEmail, friendships.user_id, friendships.friend_id).filter(users.id == friendships.friend_id).filter(friendships.user_id == userID).paginate(page, 1, False)

        return render_template(
            'friends.html', userList=userList)

这将是代码的 Jinja 方面:

{% extends "layout.html" %}
{% block body %}

<div id="pagination">
{% if userList.has_prev %}
        <a href="{{ url_for('friendList', page=userList.prev_num) }}">Back</a>
{% endif %} 

{% if userList.has_next %}
        <a href="{{ url_for('friendList', page=userList.next_num) }}">Next</a>
{% endif %}
</div>

<div style="clear:both;"></div>

<div id="innerContent">
{% if userList %}
    {% for friends in userList %}
                    <div class="contentUsers">
                        {{ friends.userName }}
                    </div>

                    <br><br><br><br>

    {% endfor %}{% else %}<div>No friends</div>
{% endif %} 

</div>
  {% endblock %}

如果我这样查询:

userList = db.session.query(users,friendships).filter(users.id == friendships.friend_id).filter(friendships.user_id == userID).paginate(page, 1, False)

我收到此错误:

InvalidRequestError: Could not find a FROM clause to join from. Tried joining to <class 'models.friendships'>, but got: Can't determine join between 'Users' and 'Friendships'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

如果我这样查询:

userList = users.query.join(friendships, users.id==friendships.user_id).add_columns(users.id, users.userName, users.userEmail, friendships.user_id, friendships.friend_id).filter(users.id == friendships.friend_id).filter(friendships.user_id == userID).paginate(page, 1, False)

我收到以下错误:

TypeError: 'Pagination' object is not iterable

我仍然认为以后的查询是正确的方法，但我认为我的表之间的关系/外键有问题!!!

如果在 Jinja 方面，我将 .items 添加到循环中:

{% if userList.items %}
{% for friends in userList.items %}

                <div class="contentUsers">
                    {{ friends.userName }}
                </div>

                <br><br><br><br>

{% endfor %}{% else %}<div>No friends</div>

{％ 万一 ％}

它根本不循环，只是显示“没有 friend ”的 else 语句

Answer 1

错误信息告诉你 SQLAlchemy 无法确定如何连接两个表 users和 friendships ，因为链接它们的外键不止一个。您需要明确定义连接条件。

尝试:

userList = users.query\
    .join(friendships, users.id==friendships.user_id)\
    .add_columns(users.userId, users.name, users.email, friends.userId, friendId)\
    .filter(users.id == friendships.friend_id)\
    .filter(friendships.user_id == userID)\
    .paginate(page, 1, False)