本文介绍了建立相关模型的Model.query的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要建立一个列出所有用户的查询,那里有最好的朋友,还有朋友的总数。该列表必须由用户拥有的全部朋友订购。



我希望生成的查询具有以下结构:

  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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 12:32
查看更多