我需要建立一个查询,列出所有用户,那里最好的朋友和那里的朋友总数。列表必须由用户的好友排序。你知道吗
我希望生成的查询具有以下结构:
users.id | users.userName | users.userEmail | users.userPhone | totalFriends | bestFriends.userName | bestFriends.user_id
示例:
1 | Alex | alex@alex.com | 900102030 | 2 | Carlos | 2
2 | Carlos | carlos@carlos.com | 900102030 | 1 | Alex | 1
3 | Sara | sara@sara.com | 900102030 | 1 | None | None
4 | Jack | jack@jack.com | 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)
我不想用数据库会话因为我想用。从模型.查询. 如何构造此查询?你知道吗
我构建了一个查询,它收集了我想要的结构,但没有我确实需要的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)
这使我可以在金贾方面做到以下几点:
<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 |
我如何瞄准目标好友。用户名?你知道吗
我将关系添加到您的模型中,如下所示:
添加了一些测试数据:
在此之后,获取查询非常简单:
但是我可能会删除bestfriend的表并将其直接添加到
users
表中。你知道吗相关问题 更多 >
编程相关推荐