Using the fragment approach, would there be any way to reuse that calculation on the related resource?
Probably not, right?
As the one that uses aggregates on those aggregates now becomes very ugly:
calculate :time_played_during,
:float,
expr(
fragment(
"""
COALESCE((
SELECT SUM(t.duration * (
SELECT SUM(ps.completion_ratio)
FROM play_stats ps
WHERE ps.track_id = t.id
AND (? IS NULL OR ps.inserted_at >= ?)
AND (? IS NULL OR ps.inserted_at <= ?)
))
FROM tracks t
WHERE t.album_id = ?
), 0) +
COALESCE((
SELECT SUM(wt.duration * (
SELECT SUM(ps.completion_ratio)
FROM play_stats ps
WHERE ps.web_track_id = wt.id
AND (? IS NULL OR ps.inserted_at >= ?)
AND (? IS NULL OR ps.inserted_at <= ?)
))
FROM web_tracks wt
WHERE wt.album_id = ?
), 0)
""",
^arg(:earliest_date),
^arg(:earliest_date),
^arg(:latest_date),
^arg(:latest_date),
id,
^arg(:earliest_date),
^arg(:earliest_date),
^arg(:latest_date),
^arg(:latest_date),
id
)
) do
argument :earliest_date, :naive_datetime
argument :latest_date, :naive_datetime
end
I’d really love to solve this at the ash_sqlite level, maybe you can give me some pointers on how I could approach contributing this feature?
