Selecting tag names of item from toxi-style database-CodePudding

I'm sure this has been asked before, but I can't seem to find the right google phrase.

I have three tables:

Songs

| index   title     ...
-----------------------
| 'a001'  'title1'  ...
| 'a002'  'title2'  ...
...

Tagmap

| index   item_index   tag_index
--------------------------------
|     1       'a001'      't001'
|     2       'a001'      't003'
|     3       'a001'      't004'
|     4       'a002'      't003'
|     5       'a002'      't005'
...

Tags

| tag_index         name
------------------------
|    't001'        'foo'
|    't002'        'bar'
|    't003'     'foobar'
...

I'm struggling with coming up with a query, that will give me results like this:

[0]: {index: 'a001', title: 'title1', tags: ['foo', 'foobar']}
[1]: {index: 'a002', title: 'title2', tags: ['foobar', 'something']}

So what I want to achieve is:

query the releases table (something like WHERE title = "abc")
get all tag names returned in the same row

So far, I have achieved getting all tag indexes returned in the same row, but not the actual tag names. So I got:

[0]: {index: 'a001', title: 'title1', tags: ['t001', 't003']}

My query thus far is as follows:

SELECT s.index, s.title, s.licensable, GROUP_CONCAT(tm.tag_index as tags) FROM songs s
LEFT JOIN tagmap tm ON s.index = tm.item_index
WHERE s.is_public = 1 GROUP BY s.catalogue_index ORDER BY s.release_date DESC

I should note that there is not direct route songs->tags , the only link is the tagmap.

CodePudding user response：

in order to get the tag names instead of the tag indexes in the query provided, you need to join the tagmap table with the tags table. You can do this by adding another join clause to the query, like this:

SELECT s.index, s.title, GROUP_CONCAT(t.name) as tags
FROM songs s
LEFT JOIN tagmap tm ON s.index = tm.item_index
LEFT JOIN tags t ON tm.tag_index = t.tag_index
WHERE s.is_public = 1
GROUP BY s.index
ORDER BY s.release_date DESC