I have the following model defined with Flask-SQLAlchemy:

"""models.py"""from flask_sqlalchemy import SQLAlchemydb = 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)

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)

I tried the following :

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()

The aim was to filter all candidates for every skill and compose it with a and_ statement

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)

You could query all candidates with any of the skills in your list and then filter the result with a list comprehension. This may not be as performant as the relational division approach mentioned by @IljaEverilä, but it certainly simplifies the query aspect.

skill_ids = ['id_1', 'id_2']
candidates = session.query(Candidate).\filter(Candidate.skills.any(Skill.id.in_(skill_ids)).\all()candidates = [c for c in candidatesif set(s.id for s in c.skills).issuperset(skill_ids)
]