我需要构建一个查询,列出所有用户、最好的朋友和朋友总数。该列表必须按用户拥有的好友总数排序。
我希望生成的查询具有以下结构:
users.id | users.userName | users.userEmail | users.userPhone | totalFriends | bestFriends.userName | bestFriends.user_id
Example:
1 | Alex | [email protected] /cdn-cgi/l/email-protection | 900102030 | 2 | Carlos | 2
2 | Carlos | [email protected] /cdn-cgi/l/email-protection | 900102030 | 1 | Alex | 1
3 | Sara | [email protected] /cdn-cgi/l/email-protection | 900102030 | 1 | None | None
4 | Jack | [email protected] /cdn-cgi/l/email-protection | 900102030 | 0 | None | None
这是我的模型:
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)
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, db.ForeignKey('Users.id'), nullable=False)
friend_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
userR = db.relationship('users', foreign_keys='friendships.user_id')
friendR = db.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, db.ForeignKey('Users.id'), nullable=False)
best_friend_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
user = db.relationship('users', foreign_keys='bestFriends.user_id')
best_friend = db.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)
我不想使用 db.session,因为我想使用 Model.query 中的 .paginate。我如何构造这个查询?
我构建了一个查询,收集我想要的结构,但没有我确实需要的 bestFriends 名称:
userList = users.query.add_columns(bestFriends.best_friend_id, db.func.count(friendships.user_id).label("total")).outerjoin(friendships, users.id==friendships.user_id).group_by(users.id).outerjoin(bestFriends, users.id==bestFriends.user_id).order_by(db.func.count(friendships.user_id).desc()).paginate(page, 5, false)
这允许我在 jinja 方面执行以下操作:
<div id="innerContent">
{% if userList.items %}
{% for user in userList.items %}
<div class="contentUsers">
{{ user.users.userName }}|{{ user.total }}|{{ user.best_friend_id }}
这使:
id| name |Number friends| best friend id when exists
1 | Alex | 2 | 2
2 | Carlos | 1 | 1
3 | Sara | 1 |
4 | Jack | 0 |
如何定位 bestFriends.userName?