I don't understand why they didn't try to alter the ON condition. Instead of moving the JOIN into a per-field-subquery or a preliminary query, why not replace

    LEFT JOIN event_chunks as ec
      ON ec.chunk_id = lc.id

with

    LEFT JOIN event_chunks as ec
      ON case lc.type
        WHEN 'E' THEN ec.chunked_id = lc.id
        ELSE 0
        END

I don't know if it more performant with Sqlite's query planner (and it may depend on many conditions, e.g. strict tables), but it looked like the obvious fix to the initial query. And it's 100% SQL, no need for a Rust hashmap.