Skip to content

Instantly share code, notes, and snippets.

@miguelgrinberg
Last active October 18, 2022 06:51
Show Gist options
  • Save miguelgrinberg/dbeb1a51231921d5fa8b3de218d0c449 to your computer and use it in GitHub Desktop.
Save miguelgrinberg/dbeb1a51231921d5fa8b3de218d0c449 to your computer and use it in GitHub Desktop.
A little SQLAlchemy challenge. See blog post at https://blog.miguelgrinberg.com/post/nested-queries-with-sqlalchemy-orm for details!
#!/usr/bin/env python
# Before you run this script make sure Flask-SQLAlchemy is installed in
# your virtual environment
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' # in-memory
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
example_data = [
(1, '2018-01-01'),
(1, '2018-01-05'),
(3, '2018-01-07'),
(1, '2018-02-06'),
(3, '2018-01-31'),
(2, '2018-02-01'),
(3, '2018-02-01'),
(3, '2018-01-20'),
(2, '2018-02-07'),
]
class Order(db.Model):
__tablename__ = 'orders'
id = db.Column(db.Integer, primary_key=True)
customer_id = db.Column(db.Integer, index=True)
order_date = db.Column(db.String, index=True)
def load_data():
db.create_all()
for row in example_data:
order = Order(customer_id=row[0], order_date=row[1])
db.session.add(order)
db.session.commit()
def main():
load_data()
# TODO: create a query that returns the rows of the table sorted by
# customer_id, with the most recent customer first, according to
# last order. The entries of a customer need to be sorted with the
# most recent order first.
# See blog post for details!
query = Order.query
# print results
for row in query:
print(row.id, row.customer_id, row.order_date)
if __name__ == '__main__':
main()
@Amir1Mohammad
Copy link

I have question . if I want to sort customers by order counter ? For example, anyone who has the highest number of orders comes first and the smaller the order the less priority.
Or if I want to have the average number of orders in the last month? Then what should I do?
Thanks for your helping ...

@miguelgrinberg
Copy link
Author

This is the topic of the blog article referenced in the description of this gist. Have a look at the article.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment