Hmm, ok, the fragment approach doesn’t seem too bad. I used a bit of AI help to translate it, seems to work:
calculate :time_played_during,
:float,
expr(
duration *
fragment(
"""
(SELECT SUM(completion_ratio)
FROM play_stats
WHERE play_stats.track_id = ?
AND (? IS NULL OR play_stats.inserted_at >= ?)
AND (? IS NULL OR play_stats.inserted_at <= ?))
""",
id,
^arg(:earliest_date),
^arg(:earliest_date),
^arg(:latest_date),
^arg(:latest_date)
)
) do
argument :earliest_date, :naive_datetime
argument :latest_date, :naive_datetime
end
Would be nice if I could pass in an expr() as an argument to a fragment to simplify that last condition to: fragment(".. AND ?", id, expr(...)). However that does not seem to work.
Also, I’m wondering, if it works with sqlite if using a subquery instead of a lateral join, could this be how to implement aggregates support in ash_sqlite?
