如何通过对相关对象进行过滤来获取具有不同项目的SQL Alchemy对象 [英] How to get a SQL Alchemy object of distinct items with filter on related objects
问题描述
我正在尝试对下面的相关对象进行过滤的不同项目的SQL Alchemy查询,等效于以下查询:
I am trying to get a SQL Alchemy query of distinct items below filtering on related objects, the equivalent of the below query:
SELECT distinct items.item_id, items.item_name
FROM items
INNER JOIN categories as cat on items.category_id = cat.category_id
INNER JOIN stores on cat.store_id = stores.store_id
WHERE store.store_id = 123
我创建了以下包含外键的模型,但当我运行下面的查询时,它无法正确过滤。
I have created the models as below with foreign keys included but when I run the query below it does not filter correctly.
items_query = (db.session.query(Store, Item)
.filter(Store.store_id == 123)
).all()
#SQL Alchemy Models
class Store(db.Model):
__tablename__ = 'stores'
store_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
store_name = db.Column(db.String(100), unique=True, nullable=False)
def __repr__(self):
return '<Store>'+str(self.store_name)
class Category(db.Model):
__tablename__ = 'categories'
category_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
category_name = db.Column(db.String(100), unique=True, nullable=False)
store_id = db.Column(db.Integer, db.ForeignKey('stores.store_id'))
store = db.relationship('Store', backref=db.backref('categories', lazy='dynamic'))
def __repr__(self):
return '<Category>'+str(self.category_name)
class Item(db.Model):
__tablename__ = 'items'
item_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
item_name = db.Column(db.String(150), unique=True, nullable=False)
category_id = db.Column(db.Integer, db.ForeignKey('categories.category_id'))
category = db.relationship('Category', backref=db.backref('items', lazy='dynamic'))
def __repr__(self):
return '<Item>'+str(self.item_name)
有人可以帮助我更好地形成查询吗?
Could anyone assist me to form the query better?
推荐答案
使用
(db.session.query(Store, Item)
.filter(Store.store_id == 123)
).all()
您将获得一个隐式 商店
和商品
之间的交叉联接,这显然不是您想要的。
you'll get an implicit cross join between Store
and Item
, which is clearly not what you want.
杉木st构建所需的加入要么显式使用以下关系:
First build the required joins either explicitly using the relationships:
query = db.session.query(Item.item_id, Item.item_name).\
join(Item.category).\
join(Category.store)
或简化形式:
query = db.session.query(Item.item_id, Item.item_name).\
join("category", "store")
然后应用您的WHERE子句:
Then apply your WHERE clause:
query = query.filter(Store.store_id == 123)
然后 distinct()
:
query = query.distinct()
总结一下:
query = db.session.query(Item.item_id, Item.item_name).\
join("category", "store").\
filter(Store.store_id == 123).\
distinct().\
all()
此外,由于您对有独特的约束Item.item_name
,并且由于一对多关系( Item
产生多行> distinct()应该是不必要的。
Also since you've unique constraint on Item.item_name
and the joins should not produce multiple rows per Item
because of the direction of the one to many relationships, the distinct()
should be unnecessary.
这篇关于如何通过对相关对象进行过滤来获取具有不同项目的SQL Alchemy对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!