SQLite, Many to many relations, How to aggregate?

Question

I have the classic arrangement for a many to many relation in a small flashcard like application built using SQLite. Every card can have multiple tags, and every tag can have multiple cards. This two entities having each a table with a third table to link records.

This is the table for Cards:

CREATE TABLE Cards (CardId INTEGER PRIMARY KEY AUTOINCREMENT,
                    Text TEXT NOT NULL,
                    Answer INTEGER NOT NULL,
                    Success INTEGER NOT NULL,
                    Fail INTEGER NOT NULL);

This is the table for Tags:

CREATE TABLE Tags (TagId INTEGER PRIMARY KEY AUTOINCREMENT,
                   Name TEXT UNIQUE NOT NULL);

This is the cross reference table:

CREATE TABLE CardsRelatedToTags (CardId INTEGER,
                                 TagId INTEGER,
                                 PRIMARY KEY (CardId, TagId));

I need to get a table of cards with their associated tags in a column separated by commas. I can already get what I need for a single row knowing its Id with the following query:

SELECT Cards.CardId, Cards.Text,
       (SELECT group_concat(Tags.Name, ', ') FROM Tags
           JOIN CardsRelatedToTags ON CardsRelatedToTags.TagId = Tags.TagId
           WHERE CardsRelatedToTags.CardId = 1) AS TagsList
           FROM Cards
           WHERE Cards.CardId = 1

This will result in something like this:

CardId | Text                          | TagsList
1      | Some specially formatted text | Tag1, Tag2, TagN...

How to get this type of result (TagsList from group_concat) for every row in Cards using a SQL query? It is advisable to do so from the performance point of view? Or I need to do this sort of "presentation" work in application code using a simpler request to the DB?

Answer 1

Answering your code question:

SELECT
    c.CardId, 
    c.Text, 
    GROUP_CONCAT(t.Name,', ') AS TagsList
FROM
    Cards c
    JOIN CardsRelatedToTags crt ON
        c.CardId = crt.CardId
    JOIN Tags t ON
        crt.TagId = t.TagId
WHERE
    c.CardId = 1
GROUP BY c.CardId, c.Text

Now, to the matter of performance. Databases are a powerful tool and do not end on simple SELECT statements. You can definitely do what you need inside a DB (even SQLite). It is a bad practice to use a SELECT statement as a feed for one column inside another SELECT. It would require scanning a table to get result for each row in your input.