问题描述
我需要建立一个列出所有用户的查询,那里有最好的朋友,还有朋友的总数。该列表必须由用户拥有的全部朋友订购。我希望生成的查询具有以下结构:
users.id | users.userName | users.userEmail | users.userPhone | totalFriends | bestFriends.userName | bestFriends.user_id
示例:
1 | Alex | alex@alex.com | 900102030 | 2 |卡洛斯| 2
2 |卡洛斯| carlos@carlos.com | 900102030 | 1 | Alex | 1
3 |萨拉| sara@sara.com | 900102030 | 1 |无|无
4 |杰克| jack@jack.com | 900102030 | 0 |无|无
这是我的模型:
从应用程序导入db
从sqlalchemy.orm导入关系,backref $ b $从sqlalchemy导入表,列,整数,外键$ b $从sqlalchemy.ext.declarative导入declarative_base
类用户(db.Model):
__tablename__ =Users
id = db.Column(db.Integer,primary_key = )
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)
$ b 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,s elf.userEmail,self.userPhone)
$ b类友谊(db.Model):
__tablename__ =友谊
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)
$ b $ 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
$ b def __repr __(self):$ b $格式(self.user_id,self.friend_id)
类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 =假)
user = db.relationship('users',foreign_keys ='bestFriends.user_id')
best_friend = db.relationship('users',foreign_keys ='bestFriends.best_friend_id')
$ b def __init __(self,user_id,best_friend_id):
self.user_id = user_id
self.best_friend_id = best_friend_id
def __repr __(self):
return'{} - {} - {} - {}'。我不想使用db.session,因为我想从Model.query中使用.paginate。
我已经构建了一个查询来收集我想要的结构,但没有我需要的bestFriends名称:
userList = users.query.add_columns(bestFriends.best_friend_id,db.func.count(friendships.user_id).label(total))。outerjoin友谊,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)
p>
< div id =innerContent>
{%if userList.items%}
{%for user in userList.items%}
< div class =contentUsers>
{{user.users.userName}} | {{user.total}} | {{user.best_friend_id}}
其中给出:
id |姓名|号码的朋友|最好的朋友ID存在时
1 | Alex | 2 | 2
2 |卡洛斯| 1 | 1
3 |萨拉| 1 |
4 |杰克| 0 |
如何定位bestFriends.userName?
class users(db。 (db.teger,primary_key = True)
userName = db.Column(db.String,nullable = False)
friends = association_proxy(
'_friends','friend',
creator = lambda v:friendships(friend = v),
)
best_friend = db.relationship(
'users',
secondary ='BestFriends',
primaryjoin ='users.id == bestFriends.user_id',
secondaryjoin ='users .id == bestFriends.best_friend_id',
uselist = False,
backref = db.backref('best_friend_of',uselist = False),
)
类友谊(db.Model):
__tablename__ =友谊
id = db.Column(db.Integer,pr (db.ForeignKey('Users.id'),nullable = False)
friend_id = db.Column(db.Integer,db.ForeignKey ('Users.id'),nullable = False)
user = db.relationship(users,foreign_keys = user_id,backref ='_ friends')
friend = db.relationship(users, foreign_keys = friend_id)
类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'))
增加了一些测试数据:
#add data
u1,u2,u3,u4 = _users = [
users(userName = _un)
for _un in('Alex','Carlos','Sara','Jack')
]
u1.friends.append(u2)
u1.friends.append(u3)
u2.friends.append(u4)
u3.friends.append( u1)
u1.best_friend = u2
u2.best_friend = u1
db.session.add_all(_users)
db.session.commit()
之后,查询变得非常简单:
#create query
user_bf = db.aliased(users,name ='user_bf')
userList =(
users.query
.add_column (db.func.count(friendships.user_id).label(total))
.add_column(user_bf.id.label(best_friend))
.add_column(user_bf.userName.label best_friend_name))
.outerjoin(friendships,users.id == friendships.user_id)
.outerjoin(user_bf,users.best_friend)
.group_by(users.id)
.order_by(db.func.count(friendships.user_id).desc())
.paginate(1,10,False)
)
userList.items中的用户:
print(user)
但是我可能会删除最好的朋友的表,直接添加用户
表。
I need to build a query that lists all the users, there best friend and there total number of friends. The list has to be ordered by totalFriends a user has.
I want the resulting query to have the following structure:
users.id | users.userName | users.userEmail | users.userPhone | totalFriends | bestFriends.userName | bestFriends.user_id
Example:
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
This is my model:
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)
I don't want to use db.session because I want to use .paginate from Model.query. How do I construct this query?
I have built a query that gathers the structure I want but without the bestFriends name which I do need:
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)
This allows me the following on the jinja side:
<div id="innerContent">
{% if userList.items %}
{% for user in userList.items %}
<div class="contentUsers">
{{ user.users.userName }}|{{ user.total }}|{{ user.best_friend_id }}
Which gives:
id| name |Number friends| best friend id when exists
1 | Alex | 2 | 2
2 | Carlos | 1 | 1
3 | Sara | 1 |
4 | Jack | 0 |
How do I target bestFriends.userName?
解决方案 I added relationship to your model as below:
class users(db.Model):
__tablename__ = "Users"
id = db.Column(db.Integer, primary_key=True)
userName = db.Column(db.String, nullable=False)
friends = association_proxy(
'_friends', 'friend',
creator=lambda v: friendships(friend=v),
)
best_friend = db.relationship(
'users',
secondary='BestFriends',
primaryjoin='users.id==bestFriends.user_id',
secondaryjoin='users.id==bestFriends.best_friend_id',
uselist=False,
backref=db.backref('best_friend_of', uselist=False),
)
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)
user = db.relationship(users, foreign_keys=user_id, backref='_friends')
friend = db.relationship(users, foreign_keys=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'))
Added some test data:
# add data
u1, u2, u3, u4 = _users = [
users(userName=_un)
for _un in ('Alex', 'Carlos', 'Sara', 'Jack')
]
u1.friends.append(u2)
u1.friends.append(u3)
u2.friends.append(u4)
u3.friends.append(u1)
u1.best_friend = u2
u2.best_friend = u1
db.session.add_all(_users)
db.session.commit()
After that getting the query is pretty easy:
# create query
user_bf = db.aliased(users, name='user_bf')
userList = (
users.query
.add_column(db.func.count(friendships.user_id).label("total"))
.add_column(user_bf.id.label("best_friend"))
.add_column(user_bf.userName.label("best_friend_name"))
.outerjoin(friendships, users.id == friendships.user_id)
.outerjoin(user_bf, users.best_friend)
.group_by(users.id)
.order_by(db.func.count(friendships.user_id).desc())
.paginate(1, 10, False)
)
for user in userList.items:
print(user)
But I would probably remove the table for best friend and add it directly to the users
table.
这篇关于建立相关模型的Model.query的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!