CODEWITHSUNDEEP
pythonsqlsqlalchemyflask-sqlalchemy
Thibault Martin
Thibault Martin

Reputation: 509

Querying with Flask-SQLAlchemy

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.

Upvotes: 3

Views: 21040

Answers (3)

univerio
univerio

Reputation: 20548

You can use .any():

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

Upvotes: 3

jackotonye
jackotonye

Reputation: 3853

Can also use 

db.session.query(Order.id).filter(Order.lines.status_id != 1 ).group_by(Order.id).all()

Upvotes: 1

ares1986
ares1986

Reputation: 538

I'm also new in Flask-SQLAlchemy but I've been learning a lot lately for my app. Then, some point that could help you:

The main difference between 'vanilla' and Flask-SQLAlchemy at the moment to do a query, it is the way Flas-SQLAlchemy handle the session variables. In the Flask version you have a db object that handle your session as in this case:

  db = SQLAlchemy()

With that object, you handle the query. In your case, your query could be performed in this way:

db.session.query(Order).filter(Order.id==Order_line.order_id).filter(Order_line.status_id!=1).group_by(Order.id).all()

This is not exactly the same query but it quite similar. It will return you all the fields from the Order table but if you want only the "id", you can change "Order" for "Query.id" in the query statement. The "like" filter that you have I'm not totally sure how to implement it in Flask-SQLAlchemy but I found this question that perform an answers for the "vanilla" SQLalchemy: how to pass a not like operator in a sqlalchemy ORM query

Upvotes: 2

Related Questions