Querying with Flask-SQLAlchemy

Question

I'm using Flask to build a RESTful api and use SQLAlchemy to connect my app to a MySQL database.

I have two models in databse : Order and Order_line. An order is made of several order lines. Each order lines has a status associated.

I'm having trouble translating my SQL request into a Flask-SQLAlchemy statement. I'm especially bugged by the join.

Here are my models:

class Order(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    date_created = db.Column(db.DateTime)
    lines = db.relationship('Order_line',
                            backref=db.backref('order',
                                               lazy='join'))
    def __init__(self, po):
        self.date_created = datetime.datetime.now()

class Order_line(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('order.id'))
    status_id = db.Column(db.Integer, db.ForeignKey('status.id'))

    def __init__(self, order_id):
        self.order_id = order_id
        self.status_id = 1

class Status(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    short_name = db.Column(db.String(60))
    description = db.Column(db.String(400))
    lines = db.relationship('Order_line',
                            backref=db.backref('status',
                                               lazy='join'))

    def __init__(self, short_name, description):
        self.short_name = short_name
        self.description = description

Basically, I want to retrieve all the orders (so retrieve the Order.id) which have one or more order_line's status_id different from 1.

The SQL query would be

SELECT id FROM `order`
INNER JOIN order_line
    ON order.id=order_line.order_id
WHERE
    order_line.status_id NOT LIKE 1
GROUP BY
    order.id

I didn't find a way to translate that SQL statement into a SQLAlchemy command. I'm especially confused by the difference between Flask-SQLAlchemy wrapper and 'vanilla' SQLAlchemy.

Answer 1

You can use .any():

Order.query.filter(Order.lines.any(Order_line.status_id != 1))