TypeORM SUM Operator on Relation's field

Question

I am a student trying to develop a music library just to try out new technologies.

Currently I use NestJS together with TypeORM as my backend technologies (as well as a MySQL database).

What am I trying to achieve? I want to get the total duration of a playlist. So a playlist consists of many songs and songs can appear in multiple playlists. So we have a many-to-many relationship at this point. Now for getting the totalDuration I thought using some kind of SUM operator is a great idea. Exactly here is my problem: How would I do that using TypeORM?

I tried following querybuilder:

const playlist = await this.playlistRepository.createQueryBuilder("playlist")
        .where("playlist.id = :playlistId", { playlistId })

        // This is for relations
        .leftJoinAndSelect("playlist.author", "author")
        .leftJoinAndSelect("playlist.artwork", "artwork")
        .leftJoinAndSelect("playlist.collaborators", "collaborators")
        .leftJoin("playlist.songs", "songs")

        // Counting the songs
        .loadRelationCountAndMap("playlist.songsCount", "playlist.songs")

        // SUM up the duration of every song to get total duration of the playlist
        .addSelect('SUM(songs.duration)', 'totalDuration')
        .groupBy("playlist.id")
        .getOne()

But that gave me an error I don't know how to solve:

QueryFailedError: Expression #16 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'soundcore_dev.collaborators.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The resulting query that is build by TypeORM looks like this:

SELECT 

        `playlist`.`id` AS `playlist_id`, 
        `playlist`.`createdAt` AS `playlist_createdAt`, 
        `playlist`.`authorId` AS `playlist_authorId`, 
        `playlist`.`artworkId` AS `playlist_artworkId`, 

        `author`.`id` AS `author_id`, 
        `artwork`.`id` AS `artwork_id`, 

        `collaborators`.`id` AS `collaborators_id`,
 
        SUM(`songs`.`duration`) AS `totalDuration` 

    FROM 
        `sc_playlist` `playlist` 
        LEFT JOIN `sc_sso_user` `author` ON `author`.`id`=`playlist`.`authorId`
        LEFT JOIN `sc_artwork` `artwork` ON `artwork`.`id`=`playlist`.`artworkId`  
        LEFT JOIN `sc_collaborators2playlist` `playlist_collaborators` ON `playlist_collaborators`.`playlistId`=`playlist`.`id` 
        LEFT JOIN `sc_sso_user` `collaborators` ON `collaborators`.`id`=`playlist_collaborators`.`ssoUserId`  
        LEFT JOIN `sc_song2playlist` `playlist_songs` ON `playlist_songs`.`playlistId`=`playlist`.`id` 
        LEFT JOIN `sc_song` `songs` ON `songs`.`id`=`playlist_songs`.`songId` 

    WHERE 
        `playlist`.`id` = ? 
    GROUP BY 
        `playlist`.`id`

I did some research only and found how to sum up using typeORM, but I didn't get it to work for me. The above code and so on is everything I've found. I think I'm justing missing something quite important...

Maybe some one can support me on this. I would really appreciate this. Have a great day! And thanks in advance for your kind support.

user_13557716 · Answer

After reading your question, I'm not sure why you're loading all the relationships and grouping them by the playlist id,
Anyway,The first thing you need to know is when we want to get the sum,max,avg,.. we use getRawOne or getRawMany

For your query you just need to do:

const playlist = await this.playlistRepository.createQueryBuilder("playlist")
    .where("playlist.id = :playlistId", { playlistId })

    // This is for relations
    .leftJoin("playlist.songs", "songs")

    // Counting the songs
    //.select('COUNT(songs.id)', 'numbersongs') in case you want count of songs

    // SUM up the duration of every song to get total duration of the playlist
    .addSelect('SUM(songs.duration)', 'totalDuration')
    .getRawOne()

TypeORM SUM Operator on Relation's field

1 Answers