SQLAlchemy:如何对关系字段上的查询结果(order_by)进行排序? [英] SQLAlchemy: How to order query results (order_by) on a relationship's field?
问题描述
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey
from sqlalchemy import Integer
from sqlalchemy import Unicode
from sqlalchemy import TIMESTAMP
from sqlalchemy.orm import relationship
BaseModel = declarative_base()
class Base(BaseModel):
__tablename__ = 'base'
id = Column(Integer, primary_key=True)
location = Column(Unicode(12), ForeignKey("locationterrain.location"), unique=True,)
name = Column(Unicode(45))
ownerid = Column(Integer,ForeignKey("player.id"))
occupierid = Column(Integer, ForeignKey("player.id"))
submitid = Column(Integer,ForeignKey("player.id"))
updateid = Column(Integer,ForeignKey("player.id"))
owner = relationship("Player",
primaryjoin='Base.ownerid==Player.id',
join_depth=3,
lazy='joined')
occupier= relationship("Player",
primaryjoin='Base.occupierid==Player.id',
join_depth=3,
lazy='joined')
submitter = relationship("Player",
primaryjoin='Base.submitid== Player.id',
join_depth=3,
lazy='joined')
updater= relationship("Player",
primaryjoin='Base.updateid== Player.id',
join_depth=3,
lazy='joined')
class Player(BaseModel):
__tablename__ = 'player'
id = Column(Integer, ForeignKey("guildmember.playerid"), primary_key=True)
name = Column(Unicode(45))
搜索
bases = dbsession.query(Base)
bases = bases.order_by(Base.owner.name)
这不起作用....我到处搜索并阅读了文档.但是我只是看不到如何根据他们的所有者"关系名称对我的(基础)查询进行排序.
This doesn't work .... I've searched everywhere and read the documentation. But I just don't see how I can sort my (Base) query on their 'owner' relationship's name.
它总是导致:
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'name'
这一定很容易...但是我看不到.还研究了比较器,这似乎很合逻辑,但是我看不到ORDER BY的查询部分在哪里生成或应该返回什么,因为所有内容都是动态生成的.为我的每个玩家"关系做一个比较器来做一件简单的事情似乎太复杂了.
This must be easy... but I don't see it. Also looked into Comparators, which seemed logical, but I don't see where the query part for the ORDER BY is generated or what I should be returning since everything is generated dynamically. And making a comparator for each of my 'player' relationships to do a simple thing seems over complicated.
推荐答案
SQLAlchemy希望您从SQL角度进行思考.如果您查询基本",则为:
SQLAlchemy wants you to think in terms of SQL. If you do a query for "Base", that's:
SELECT * FROM base
容易.那么,在SQL中,如何从基本"中选择行,并按完全不同的表(即玩家")中的名称"列进行排序?您使用联接:
easy. So how, in SQL, would you select the rows from "base" and order by the "name" column in a totally different table, that is, "player"? You use a join:
SELECT base.* FROM base JOIN player ON base.ownerid=player.id ORDER BY player.name
SQLAlchemy是否使用了相同的思维过程-join():
SQLAlchemy has you use the identical thought process - you join():
session.query(Base).join(Base.owner).order_by(Player.name)
这篇关于SQLAlchemy:如何对关系字段上的查询结果(order_by)进行排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!