列表属性上的SQLAlchemy过滤器 [英] SQLAlchemy filter on list attribute
问题描述
我用Flask-SQLAlchemy定义了以下模型:
I have the following model defined with Flask-SQLAlchemy:
"""models.py"""
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
skill_candidate = db.Table(
'SkillCandidate',
db.Column('skill_id', db.String, db.ForeignKey('skill.id')),
db.Column('candidate_id', db.Integer, db.ForeignKey('candidate.id')))
class Candidate(db.Model):
id = db.Column(db.Integer, primary_key=True)
skills = db.relationship("Skill", secondary=skill_candidate)
class Skill(db.Model):
id = db.Column(db.String, primary_key=True)
name = db.Column(db.String, nullable=False, unique=True)
要实现的目标如下:我想返回所有具有列表输入(甚至理想情况下,skill_id列表)中提供的技能的候选人.
What am trying to achieve is the following : I want to return all the candidates who possess skills provided in a list input (even ideally, a list of skill_id)
我尝试了以下方法:
def get_skilled_candidates(skill_ids):
return Candidate.query.join(skill_candidate).\
filter(and_(*[skill_candidate.c.skill_id == skill_id for skill_id in skill_ids])).\
all()
目的是为所有技能筛选所有候选人,并用and_语句组成
The aim was to filter all candidates for every skill and compose it with a and_ statement
如果我使用1个项目的列表(返回具有该技能的所有候选者),则效果很好,但是如果我在输入列表中添加了更多技能(即使我在基础中有符合条件的候选者),也不能奏效
It works well if I use a list of 1 item (it returns all candidates that possess the skill) but does not if I add more skills in the input list (even tho I have candidates in base that fit the criteria)
推荐答案
如注释中所述,您需要执行 FORALL
操作(关系划分.
As noted in the comments, what you'd need is a FORALL
operation (universal quantifier), or relational division.
FORALL x ( p(x) )
可以表示为
NOT ( EXISTS x ( NOT ( p(x) ) ) )
如果您不了解 FORALL
及其关系,则这有点笨拙且难以推理.给定您的模型,它看起来像:
which is a bit unwieldy and hard to reason about, if you don't know about FORALL
and their relationship. Given your models it could look like:
def get_skilled_candidates(skill_ids):
# Form a temporary derived table using unions
skills = db.union_all(*[
db.select([db.literal(sid).label('skill_id')])
for sid in skill_ids]).alias()
return Candidate.query.\
filter(
~db.exists().select_from(skills).where(
~db.exists().
where(db.and_(skill_candidate.c.skill_id == skills.c.skill_id,
skill_candidate.c.candidate_id == Candidate.id)).
correlate_except(skill_candidate))).\
all()
当然,还有其他表达相同查询的方法,例如:
There are of course other ways to express the same query, such as:
def get_skilled_candidates(skill_ids):
return Candidate.query.\
join(skill_candidate).\
filter(skill_candidate.c.skill_id.in_(skill_ids)).\
group_by(Candidate.id).\
having(db.func.count(skill_candidate.c.skill_id.distinct()) ==
len(set(skill_ids))).\
all()
它实际上是通过计数检查所有技能ID是否匹配的.
which essentially checks by count that all skill ids were matched.
如果使用Postgresql,您也可以这样做:
If using Postgresql you could also do:
from sqlalchemy.dialects.postgresql import array_agg
def get_skilled_candidates(skill_ids):
# The double filtering may seem redundant, but the WHERE ... IN allows
# the query to use indexes, while the HAVING ... @> does the final filtering.
return Candidate.query.\
join(skill_candidate).\
filter(skill_candidate.c.skill_id.in_(skill_ids)).\
group_by(Candidate.id).\
having(array_agg(skill_candidate.c.skill_id).contains(skill_ids)).\
all()
这与其他答案中的部分Python解决方案等效.
This is somewhat equivalent with the partly Python solution from the other answer.
此外,可以使用汇总 EVERY
:
def get_skilled_candidates(skill_ids):
# Form a temporary derived table using unions
skills = db.union_all(*[
db.select([db.literal(sid).label('skill_id')])
for sid in skill_ids]).alias()
# Perform a CROSS JOIN between candidate and skills
return Candidate.query.\
join(skills, db.true()).\
group_by(Candidate.id).\
having(db.func.every(
db.exists().
where(db.and_(skill_candidate.c.skill_id == skills.c.skill_id,
skill_candidate.c.candidate_id == Candidate.id)).
correlate_except(skill_candidate))).\
all()
这篇关于列表属性上的SQLAlchemy过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!