CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
Destaq
Destaq

Reputation: 795

Randomly shuffle query results where values are the same

Performing an order_by operation on an SQLAlchemy query, where the objects being sorted have the same value, always returns them in the order that they are in the database.

Is there a function to additionally shuffle those results that are the same?

Practical example:

ITEM         PRICE

hamburger    20
burrito      10
sandwich     10
quesadilla   15

Running db.session.query(FoodItem).order_by(FoodItem.price).all() would return them in the following order:

  1. Hamburger
  2. Quesadilla
  3. Burrito
  4. Sandwich

burrito would always precede sandwich as it is earlier in the database, no matter how many times you ran the function.

I've tried to randomly sort the results before ordering, as so:

from sqlalchemy.sql.expression import func

db.session.query(FoodItem).order_by(func.random()).order_by(FoodItem.price).all()

However, this doesn't have the desired effect - the final order is still retained according to the database. Is there a way - sqlalchemy or a Python lambda expression/function - to achieve the desired result of sorted items, with items having equal values shuffled?

Upvotes: 0

Views: 1218

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1122582

You need to use the correct order_by() call order when passing in multiple criteria; the criteria are ordered from most-significant to least, the first order_by option applies to all rows, the second only to rows where the first option is equal.

So, if only items with the same price need to be randomised, put the random order last:

db.session.query(FoodItem).order_by(FoodItem.price).order_by(func.random())

Note that Query.order_by() takes multiple criteria, you don't have to use multiple calls:

db.session.query(FoodItem).order_by(FoodItem.price, func.random())

So rows are first sorted by price, then if two rows have the same price, the next criteria is used and so are randomised within the same price group only.

For future reference: print your query object (without the .all()) to get an idea what SQL is being generated; e.g. using my best guess as to what your model might look like, your query produces:

SELECT food_item.id AS food_item_id, food_item.name AS food_item_name, food_item.price AS food_item_price
FROM food_item ORDER BY random(), food_item.price

and by swapping the criteria you get:

SELECT food_item.id AS food_item_id, food_item.name AS food_item_name, food_item.price AS food_item_price
FROM food_item ORDER BY food_item.price, random()

Side note: random() is specific to PostgreSQL and SQLite, if you need to support other databases you'll need to adjust the expression.

Upvotes: 4

Related Questions