Say I have a database of songs. Every song may have more than one composer, and every composer may have more than one song: that’s a straightforward many-to-many relationship, which I’ve modelled in Sqlite like this:
CREATE TABLE songs(
id INTEGER PRIMARY KEY,
title TEXT,
otherStuff...
)
CREATE TABLE composers(
id INTEGER PRIMARY KEY,
name TEXT
)
CREATE TABLE songComposers(
songId INTEGER,
composerId INTEGER,
FOREIGN KEY (scoreId) REFERENCES songs(id)
FOREIGN KEY (composerId) REFERENCES composer(id)
)
Let’s say my database looks like this.
songs:
id | title | ...
-----------------------------------
0 | 'We are the champions' | ...
1 | 'Piano man' | ...
2 | 'Happy birthday' | ...
composers:
id | name
-----------------------
0 | 'Freddie Mercury'
1 | 'Brian May'
2 | 'Billy Joel'
songComposer:
songId | composerID
--------------------
0 | 0
0 | 1
1 | 2
The main view of my app is the list of all songs, where every element of the list is a card containing the song title and composer(s), if any. Basically, this:
We are the champions
Brian May, Freddie Mercury
Piano man
Billy Joel
Happy birthday
no composer
My first naive approach was to query the songs table first
SELECT id, title FROM songs
and then, when building each individual UI list element, retrieve the composers for any given specificSongId by running a query like
SELECT id, name FROM composers
INNER JOIN songComposer
ON composers.id = songComposer.composerId AND songComposer.songId = specificSongId
This does the job, for now, but even as a beginner it feels quite wrong to run a database query for every single item of a list. So I guess my question is twofold:
-
Is it possible to construct a single SQL query that would let me retrieve all the info at once? It feels like it should be, but I can’t quite figure it out.
-
Given that, anywhere in my app it may appear, a song will always be shown in a card with its composer(s), should I consider duplicating the composer data into an additional column of the songs table? Basically doing something like this.
songs:
id | title | composers | ...
-----------------------------------------------------------| ...
0 | 'We are the champions' | 'Brian May, Freddie Mercury'| ...
1 | 'Piano man' | 'Billy Joel' | ...
2 | 'Happy birthday' | null | ...
Avoiding the JOIN on the composers table on every single view of my app seems quite appealing from a performance (and simplicity) point of view, but is it worth the additional database complication?
You can use SQL GROUP_CONCAT to generate a json like list of composers then json parse it in dart to show in UI
SELECT
songs.id AS song_id,
songs.title AS song_title,
'[' || GROUP_CONCAT(
'{' ||
'"id": ' || composers.id || ', ' ||
'"name": "' || composers.name || '"'
|| '}', ', '
) || ']' AS song_composers
FROM
songs
LEFT JOIN
songComposer ON songs.id = songComposer.songId
LEFT JOIN
composers ON songComposer.composerId = composers.id
GROUP BY
songs.id;
If you just want the composer names then you can concat the names only
GROUP_CONCAT(composers.name, ', ') AS song_composers
5