Advanced filtering and sorting with redis (part 1)
Set and sorted set are extremely powerful data types for filtering and sorting stuff with redis.
Let’s start with something simple. Usually filtering is just a matter of union and intersection. Let’s say: filter all hotels that are 3 or 4 star and have both spa and pool.
For this, we just have to create a set for each of the filter criteria and do union/intersection accordingly.
sadd hotel:star:3 1 2 3 4 sadd hotel:star:4 5 6 7 8 sadd hotel:spa 1 2 5 sadd hotel:pool 2 5
As with the above example, it would be [UNION of (3,4 star sets)] INTERSECTION [ INTERSECTION of [spa, pool]]
SUNIONSTORE 3or4star hotel:star:3 hotel:star:4 SINTERSTORE spaandpool hotel:spa hotel:pool SINTER 3or4star spaandpool # 2 5
And you got hotel id 2 and 5 as the result.
Mutliple columns sorting
Usually, in SQL, you can do multi columns sorting like this
SELECT * FROM mytable ORDER BY col1 ASC, col2 ASC, col3 DESC
How would you translate this logic to redis?
Actually, this is not my idea but Josiah Calrson’s (author of Redis in Action book). You can find his blog post about this and demo implementation there as well.
The basic idea is:
ZINTERSTORE command supports
WEIGHTS so we just have to calculate
the weight for each column base on their order and sorting direction (ASC, DESC).
If you know the range of the filter criteria in advance, you can save 1 round trip to redis to fetch it.
for sort_col in sort: pipe.zrange(sort_col, 0, 0, withscores=True) pipe.zrange(sort_col, -1, -1, withscores=True) ranges = pipe.execute()