我正在使用Flask和SQLAlchemy构建一个应用程序。 我基本上有3个表：用户，友谊和最好的朋友：
用户可以有很多朋友，但只有一个最好的朋友。 所以我希望我的模型是关联的。 “一对多”是“用户”和“友谊”之间的关系，“一对一”是“用户”和“最好的朋友”之间的关系。
这是我的模型：
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)
我需要能够查询已登录用户的好友列表，以及如果存在的话，该用户最好的朋友。我还需要对结果进行分页：
这是我的app.py函数，用于显示用户的好友：
@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>
{% endif %}
它根本没有循环，只是显示“没有朋友”的else语句。