Wie kann ich eine durch Kommas getrennte Liste von verknüpften IDs in einem 3-Wege-MYSQL-Join abrufen?

Question

Ich erstelle ein Buch-Tagging-System (das sicherlich schon viele Male gemacht wurde) und ich möchte eine Ansicht erstellen, die mir jedes Buch mit den Namen seiner Tags liefert.

Ich habe drei Tabellen:

1. bücher ( id, name)

2. tags (id, name)

3. bookTags (id, buch, tag)

Und ich möchte eine Ansicht haben

1. booksInfo (bücher.id, bücher.name, [durch Kommata getrennte-tags.names], [tagids])

Mit meiner aktuellen Ansicht (im untenstehenden SQL-Fiddle), erhalte ich doppelte Zeilen, eine für jedes Tag-Buch-Paar.

Ich würde gerne etwas wie das hier bekommen:

   BUCHID          NAME            TAGS                    TAGIDS
   ------          -----------     --------------------    ---------
     1             1984            Dystopisch, Politisch      3, 4
     2             Weißer Fang      Hunde, Natur              5, 9
     3             Bibel           Religion, Geschichte        6, 10
     4             1776            Politisch, Geschichte       4, 10

Ich habe hier ein SQLFiddle erstellt: http://sqlfiddle.com/#!2/74b57/1/0

Ich könnte das mit PHP nach meinem select machen, dann durchgehen und ein separates Array erstellen, aber das scheint unnötig. Ich finde mit MySQL gibt es fast immer einen Weg, etwas abzufragen.

Answer 1

Verwenden Sie GROUP_CONCAT mit GROUP BY

select 
    b.id 'id',
    b.name 'name',
    GROUP_CONCAT(t.name) 'tag',
    GROUP_CONCAT(t.id) 'tagid'
from
    bookTags bt
        left join
    books b ON b.id = bt.book
        left join
    tags t ON t.id = bt.tag
GROUP BY bt.book;

Ergebnis für Ihr Fiddle

+------+------------------+------------------+-------+
| id   | name             | tag              | tagid |
+------+------------------+------------------+-------+
|    1 | 1984             | Government       | 2     |
|    2 | Huckelberry Finn | Adventure        | 1     |
|    3 | Die Bibel        | Religion         | 3     |
|    4 | Weißer Fang      | Abenteuer,Natur  | 1,4   |
+------+------------------+------------------+-------+
4 Zeilen in Set (0.00 Sek)

Answer 2

Überprüfen

create view booksInfo as
select 
b.id 'id',
b.name as 'name',
GROUP_CONCAT(t.name) as 'tag',
GROUP_CONCAT(t.id) 'tagid'
from bookTags bt
left join books b
on b.id = bt.book
left join tags t
on t.id = bt.tag
group by bt.book;

http://sqlfiddle.com/#!2/68cdd/1

Answer 3

Sie können die group_concat Funktion verwenden, um eine Reihe von Werten in verschiedenen Zeilen in Werte mit Komma-Trennzeichen umzuwandeln:

SELECT    b.id AS book_id, b.name AS book_name, 
          GROUP_CONCAT(t.name) AS tags, 
          GROUP_CONCAT(t.id) AS tag_ids
FROM      bookTags bt
LEFT JOIN books b ON b.id = bt.book
LEFT JOIN tags t  ON t.id = bt.tag
GROUP BY  b.id, b.name

Answer 4

GROUP_CONCAT(exp) Funktion ist für kommaseparierte Werte bestimmt

http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

SELECT b.id 'id',b.name 'name',

GROUP_CONCAT(t.name) 'tag',

GROUP_CONCAT(t.id) 'tagid'

FROM

bookTags bt

LEFT JOIN

books b ON (b.id = bt.book)

LEFT JOIN

tags t ON (t.id = bt.tag) GROUP BY bt.book;

Answer 5

Sie sollten Ihre Ergebnisse nicht in Ihrer Abfrage manipulieren. Es wäre viel schöner, Ihre Liste mit Duplikaten zurückzugeben und nach ID zusammenzufassen.

foreach ($rows as $key => $row) {
  $out[$row['id']]['id'] = $row['id'];
  $out[$row['id']]['name'] = $row['name'];
  $out[$row['id']]['tags'] .= ', '.$row['tags'];
  $out[$row['id']]['tagids'] = ', '.$row['name'];
}

Dies ist nur schnell, aber Sie könnten dies in eine nette kleine Funktion verpacken, die nicht direkt auf Schlüssel verweist, keine Kommas wiederholt usw. usw. (das heißt, dies ist wirklich hässlicher Code und ich entschuldige mich).

Aber sobald Sie versuchen, eine große Menge von Daten mit Gruppen zu skalieren, werden Sie ein weiteres Problem lösen müssen.