Efficently map one to many relations with sqlx

help
1

I'm using sqlite with sqlx. I got two tables, Recordings and Listens, which I turned into structs:

struct Recording {
   id: i64
   ...
}

struct Listen {
   id: i64
   recording_id: i64
   ...
}

in my program, I have a list of listens that I want to associate with their recordings using this struct:

struct RecordingWithListens {
    recording: Recording,
    listens: Vec<Listens>
}

Since I got a lot (Easily goes up to 50 000) of listens, I made a SQL request that allow batch fetching the recordings from a json array of listen ids

SELECT
    listens.id AS original_id,
    recordings.*
FROM
    listens
    INNER JOIN recordings ON listens.recording_id = recordings.id
WHERE
    listens.id IN (
        SELECT
            value
        FROM
            JSON_EACH(?)
    )

Effectively giving me an array of tuples (i64, Recording).

But here's the part where I struggle. What is the most performant way to link the recordings to the listens in a RecordingWithListens?
My naive approach was to first convert it to a (Listen, Recording) tuple, then use an hashmap to collect the listens by their recording:

    let joins = tuples.into_iter().map(|tuple| {
        let listen = listens.iter().find(|l| l.id == tuple.original_id).unwrap();
        (listen.clone(), tuple.data)
    });

    let mut map = HashMap::new();

    for (listen, recording) in joins {
        map.entry(recording).or_insert(Vec::new()).push(listen);
    }

But running it on my ~1000 recording ~30000 listens test dataset still takes roughly 10 seconds. Any ideas on how to improve it?

2

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.