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

The last query has 2 separate EXISTS subquery expressions, so you do get what it asks for, but you'd need to include both predicates in a single EXISTS expression, which Flask-Restless would not seem to support at the moment, at least on a glance. — Ilja Everilä, Jan 10 '18 at 14:38
Yeah I guess your right. I haven't found any way to do this in the meantime, so I'd accept your explanations as an answer if you bother to write one. Thanks for looking into it anyway! — Phonolog, Jan 18 '18 at 09:25

score 1 · Accepted Answer · answered Feb 22 '18 at 16:42

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

Two possible workarrounds:

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.

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.

Searching an exact match for the attribute of a relation

1 Answers1