Searching an exact match for the attribute of a relation

Question

I have following SQLAlchemy DB models describing parts that go through several production steps:

class Part(db.Model):
    part_number = db.Column(db.Integer, primary_key=True) 
    production_steps = db.relationship("ProductionStep")


class ProductionStep(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    part_number = db.Column(db.Integer, db.ForeignKey('part.part_number'))
    name = db.Column(db.String)
    status = db.Column(db.String)

Now I'd like to query all parts that have a production step with a certain name and status through a Flask-Restless search query.

Is this possible with a Flask-Restless search query? If yes, how can I achieve the specified behaviour?

I'm using Flask-Restless version 0.17.0.

---

I have tried following filters:

q={"filters":[{"and":[{"name":"production_steps__name","op":"==","val":"cutting"},
    {"name":"production_steps__status","op":"any","val":"done"}]}]}

Which leads to following error:

sqlalchemy.exc.InvalidRequestError: Can't compare a collection to an object or collection; use contains() to test for membership.

Which sounds reasonable, so I also tried the following:

q={"filters":[{"and":
    [{"name":"production_steps","op":"any","val":{"name":"name","op":"eq","val":"cutting"}},
    {"name":"production_steps","op":"any","val":{"name":"status","op":"eq","val":"done"}}]
}]}

This query does work, but it does return parts that match only one of the criterions (e.g. parts with a production step "cutting" where the status is not "done")

Answer 1

As discussed in the comments, Flask-Restless does not seem to support queries like this.

Two possible workarrounds:

1. Do two search queries: First get all Ids of ProductionSteps with the correct name and status. Second query all Parts that have one of Ids in the production_steps array with the in operator.

2. Implement your own route that returns the Parts wanted. Code might look something like this:

   @app.route('/part/outstanding', methods=['GET'])
   def parts_outstanding():
   
       result = Part.query.join(Part.production_steps) \
           .filter_by(status='outstanding').all()
   
       #Custom serialization logic
       result_json = list(map(lambda part: part.to_dict(), result))
       return jsonify(
           num_results=len(result),
           objects=result_json,
           page=1,
           total_pages=1
       )
   

I'd advocate for doing two search queries. Implementing your own route seems kinda hacky.