Using django: here is some values and the query:
max_played, avg_days_last_played = get_next_song_priority_values()
# Calculate time since played using raw SQL
time_since_played_expr = RawSQL("(julianday('now') - julianday(main_song.played_at))", [])
query = Song.objects
# Annotate priority
songs_with_priority = query.annotate(
time_since_played=ExpressionWrapper(time_since_played_expr, output_field=FloatField()),
priority=(
F('rating')
- (F('count_played') / Value(max_played))
+ (F('time_since_played') / Value(avg_days_last_played))
),
).order_by('-priority')
my logging:
logger.info(f'Next Song: {next_song}')
calculated_priority = (
next_song.rating
- (next_song.count_played / max_played)
+ (next_song.time_since_played / avg_days_last_played)
)
logger.info(f'Next Song: priority {next_song.priority:.2f} vs calc {calculated_priority:.2f}')
logger.info(f'Next Song: rating {next_song.rating:.2f}')
playd = next_song.count_played / max_played
logger.info(f'Next Song: played {playd:.2f} ({next_song.count_played} / {max_played})')
tspd = next_song.time_since_played / avg_days_last_played
logger.info(
f'Next Song: days {tspd:.2f} ({next_song.time_since_played} / {avg_days_last_played})'
)
and I get:
INFO Next Song: <Song-1489 End of the World Cold>
INFO Next Song: priority 2.73 vs calc 2.56
INFO Next Song: rating 0.50
INFO Next Song: played 0.17 (1 / 6)
INFO Next Song: days 2.23 (4.043296354357153 / 1.8125720233656466)
So my calculated value is lower. All the values are there:
the rating of 0.5 is solid,
the play counts if 1 vs 6 is solid,
the time since is used from the result next_song.time_since_played.
I’m using the same values sqlite should be using, but my calc is different.
In SQLite, integer division produces an integer value, so 1 / 6 = 0:
sqlite> select 1 / 6;
0
You can multiply one of the value by 1.0 to convert it to a float value and then this should work:
sqlite> select 1 / 6.0;
0.166666666666667
priority=(
F('rating')
- (F('count_played') / Value(max_played * 1.0)) # <- this line
+ (F('time_since_played') / Value(avg_days_last_played))
)
or explicitly convert max_played into a float:
(F('count_played') / Value(float(max_played)))