User finden und deren Gruppen aus gleicher Tabelle

[mgutt]

Ich habe eine Tabelle in der die User und Gruppen gleichermaßen vorhanden sind.

Nun möchte ich eine Volltextsuche verwirklichen in der ich z.B. nach einem Namen suchen möchte.

Dabei möchte ich die Gruppen NICHT ausgeben, also filtere ich diese auf Grund des fehlenden Geschlechtes aus.

Aber jeder User kann Mitglied einer Gruppe sein und ich möchte in einer Spalte all diese Gruppen ausgeben lassen. Alternativ könnte ich mir vorstellen, dass nur die Gruppen angezeigt werden, die mit dem Suchbegriff übereinstimmen.

Hier erstmal die Abfrage:

    $sql = 'SELECT u.uid, u.gender, u.username, u.hobby, u.group_name, u.dob, p.pid, p.ext' . $select . '
                FROM users u' . $from . '
                LEFT OUTER JOIN pics p ON p.uid = u.uid
                WHERE u.active = 1
                AND u.gender <> 0
                AND u.uid <> ' . $userdata['uid'] . '
                ' . $where . '
                ' . $match . '
                ' . $order_by . '
            LIMIT 200'; 

Ich habe jetzt noch eine Tabelle mit den Verknüpfungen zwischen User ID und Gruppen ID.

Ich könnte jetzt wie folgt vorgehen. Gibt ein Nutzer ein Suchwort an, dann suche ich erst in den Gruppen, ob ich was finde. z.B. lese ich dann nur die IDs aus. Das gleiche mache ich dann mit den Usern. Dann habe ich alle in Frage kommenden User IDs und Gruppen IDs.

Jetzt müsste ich in der Gruppentabelle suchen, welche User Mitglieder der Gruppen IDs sind und addiere diese zu den bereits gefundenen User IDs.

Als letztes lese ich die detailierten Daten aus von allen User IDs plus pro User eine weitere Abfrage, um alle seine Gruppen zu erhalten?!

Damit hätte ich mal geschickt aus einer Datenbankabfrage mehrere gemacht und verliere meiner Meinung nach die Reihenfolge wegen der Relevanz.

Ich gebe mal ein Datensatzbeispiel:

User- und Gruppentabelle:
ID NAME
1 name1
2 gruppe1
3 name2
4 name3
5 gruppe2

Verknüpfungstabelle
UID GID
1 2
1 5
3 2
4 2

Das Ergebnis bei der Suche nach dem Wort "gruppe2" sollte dann so aussehen:
1 name1 "gruppe1, gruppe2"
4 name3 "gruppe2"

Ich hatte eigentlich erst geplant einfach nur die Gruppen auszugeben, aber es wäre logischer, wenn auch direkt die Mitglieder der Gruppe ausgegeben werden. Deswegen wollte ich ganz auf die Ausgabe der Gruppen in den Suchergebnissen verzichten.

Vielleicht erstmal die primäre Frage: Wie ermittle ich für einen User alle seine Gruppen und gebe diese in EINER Spalte aus?

EDIT:
Ich habe da was gefunden was sich "find_in_set() > 0" nennt. Ist das mein Erlöser :?:

EDIT2:
Ich konnte nun das finden:

SELECT  
  t1.lastname,  
  GROUP_CONCAT(t2.firstname ORDER BY t2.firstname SEPARATOR ', ') 
FROM tbl AS t1 INNER JOIN tbl AS t2 USING (id) 
GROUP BY t1.lastname; 

Im Moment verstehe ich daraus nicht wirklich viel, da ich GROUP_CONCAT noch nie benutzt habe.

EDIT3:
Stolz bin aufs erste Ergebnis

Diese Abfrage liefert mir für jeden User in einer Spalte alle seine Gruppen (IDs) mit Komma getrennt:

SELECT u.uid, u.gender, u.username, u.hobby, u.dob, p.pid, p.ext, GROUP_CONCAT( g.gid
ORDER BY g.gid
SEPARATOR ', ' ) AS group_name
FROM users u
INNER JOIN groups g
USING ( uid )
LEFT OUTER JOIN pics p ON p.uid = u.uid
WHERE u.active = 1
AND u.gender <> 0
AND u.uid <> 9
AND (
MATCH (
u.username, u.firstname, u.lastname, u.group_name, u.hobby, u.postcode, u.city, u.website, u.enjoy, u.search, u.offer
)
AGAINST (
'gutt'
IN BOOLEAN
MODE 
)
)
GROUP BY u.uid
LIMIT 200 

Jetzt muss ich gerade mal sehen, wie ich stattdessen die Gruppennamen bekomme, aber das wird denke ich kein großes Problem werden.

Gruß

[mgutt]

Ok. Ich bekomme es doch nicht hin.

Dies ist meine Abfrage:

SELECT u.uid, u.gender, u.username, u.hobby, u.dob, p.pid, p.ext, 
GROUP_CONCAT( g.group_name ORDER BY g.group_name SEPARATOR ', ' ) group_name
FROM users u
LEFT OUTER JOIN pics p ON p.uid = u.uid
LEFT OUTER JOIN users g, groups ug ON u.uid = ug.uid AND ug.gid = g.uid
WHERE u.active = 1
AND u.gender <> 0
AND u.uid <> 1
GROUP BY u.uid
ORDER BY u.regtime DESC
LIMIT 200 

Mit der Abfrage bekomme ich jedes Mitglied mit allen Gruppen, die da sind. Und zwar auch die Gruppen, in denen das Mitglied gar nicht ist.

EDIT1:
Das soll mal einer verstehen. Wenn ich die Tabellen beim LEFT OUTER JOIN umdrehe (also statt "users g, groups ug" das "groups ug, users g"), dann gehts

SELECT u.uid, u.gender, u.username, u.hobby, u.dob, p.pid, p.ext, 
GROUP_CONCAT( g.group_name ORDER BY g.group_name SEPARATOR ', ' ) group_name
FROM users u
LEFT OUTER JOIN pics p ON p.uid = u.uid
LEFT OUTER JOIN groups ug, users g ON u.uid = ug.uid AND ug.gid = g.uid
WHERE u.active = 1
AND u.gender <> 0
AND u.uid <> 1
GROUP BY u.uid
ORDER BY u.regtime DESC
LIMIT 200 

EDIT2:
Also das ist nun meine finale Abfrage:

SELECT u.uid, u.gender, u.username, u.hobby, u.dob, p.pid, p.ext, 
GROUP_CONCAT( '<a href="./?p=groups&gid=', g.uid, '">', g.group_name, '</a>' ORDER BY g.group_name SEPARATOR ', ' ) group_name
FROM users u
LEFT OUTER JOIN pics p ON p.uid = u.uid
LEFT OUTER JOIN groups ug, users g ON u.uid = ug.uid AND ug.gid = g.uid
WHERE u.active = 1
AND u.gender <> 0
AND u.uid <> 1
GROUP BY u.uid
ORDER BY u.regtime DESC
LIMIT 200 

Wie man sieht lese ich nun alle Mitglieder aus und ihre Gruppen. Die Gruppen werden jeweils mit einem Link ausgestattet.

So.. den ersten Part hätte ich damit überwunden. Nun kommt der schwere Teil

Wie bereits in meinem ersten Beitrag erwähnt möchte ich nun die Volltextsuche auch auf die Gruppen auswirken lassen. Derzeit suche ich nur in den Feldern der Mitglieder wie folgt:

SELECT u.uid, u.gender, u.username, u.hobby, u.dob, p.pid, p.ext, 
GROUP_CONCAT( '<a href="./?p=groups&gid=', g.uid, '">', g.group_name, '</a>' ORDER BY g.group_name SEPARATOR ', ' ) group_name
FROM users u
LEFT OUTER JOIN pics p ON p.uid = u.uid
LEFT OUTER JOIN groups ug, users g ON u.uid = ug.uid AND ug.gid = g.uid
WHERE u.active = 1
AND u.gender <> 0
AND u.uid <> 1
AND (
MATCH(
u.username, u.firstname, u.lastname, u.group_name, u.hobby, u.postcode, u.city, u.website, u.enjoy, u.search, u.offer)
AGAINST('test' IN BOOLEAN MODE)
)
GROUP BY u.uid LIMIT 200 

Doch wie suche ich so, dass er auch die Gruppendaten berücksichtigt? Mal rumprobieren...

EDIT3:
Ok, ich glaube das war es schon und das Ergebnis ist ziemlich genial. Erstmal zu dem was ich gemacht habe. Ich habe das "u.group_name" gegen "g.group_name" getauscht. Schon erhalte ich nur die Nutzer, die selbst was mit dem Suchbegriff zu tun haben oder wenn der Suchbegriff in einem der Vereinsnamen auftaucht.

Und als Sahnestück sozusagen habe ich gerade festgestellt, dass alle nicht zutreffenden Vereine wegfallen.

D.h. wenn ein Nutzer in 10 Gruppen ist und eine Gruppe davon deckt sich mit dem Suchbegriff, dann wir der Nutzer angezeigt und nur die eine Gruppe. Eigentlich genau das was ich wollte

Ich werde jetzt noch weitere Spalten der Gruppentabellen hinzufügen und mal sehen was bei Städtenamen und Co. rauskommt.

Vielleicht kann ja jemand mit meiner Doku was selbst mal anfangen.

Ach ja und vielleicht erbarmt sich jemand und erklärt mir warum die Tabellen beim LEFT OUTER JOIN in der richtigen Reihenfolge angegeben werden müssen?! (siehe 1. EDIT dieses Beitrages)

[ghostgambler]

Ich habs nur überflogen, aber das
LEFT OUTER JOIN groups ug, users g ON u.uid = ug.uid AND ug.gid = g.uid
ist mMn sowas ab 5. sonstwas nicht mehr möglich, es müsste
LEFT OUTER JOIN (groups ug, users g) ON u.uid = ug.uid AND ug.gid = g.uid
sein und rückt damit mehr an den SQL-Standard

und OUTER hat mMn generell keinen Sinn in mysql oO
... gerade kurz gegoogelt, macht das ganze wohl für ODBC kompatibel

[mgutt]

Aber warum die Reihenfolge der beiden Tabellen stimmen muss weißt Du nicht oder?

Bei FROM ist die Reihenfolge doch auch egal, daher hab ich einfach Glück gehabt, dass ich das rausgefunden habe.

[ghostgambler]

Eigentlich sollte das egal sein - aber nachdem die JOIN-Syntax ja auch ohne Klammern funktioniert, scheints du eine 4er-Version zu verwenden - vielleicht ist die schlichtweg buggy...

[mgutt]

Hallo!

Die scheint tatsächlich buggy zu sein.

Ich nutze die aktuellste 4er Version (4.1.22).

Aber ich habe ein Problem mit Umlauten.

Wenn ich SELECT group_name nutze gibt er mir alle Umlaute (ä, ö, ü)korrekt aus. Fasse ich aber group_name mit GROUP_CONCAT() zusammen erhalte ich z.B. für das "ü" das "ü".

Meine Collation ist utf8_general_ci. Es scheint als würde GROUP_CONCAT() das Ergebnis immer in Binary ausgeben.

Gruß
Marc

[wahsaga]

Original geschrieben von mgutt
Es scheint als würde GROUP_CONCAT() das Ergebnis immer in Binary ausgeben.

Ob Binary oder nicht, ist vollkommen Wurscht.

Binary macht lediglich bei der Suche etwas aus - im Gegensatz zu Text-Typen werden dabei wirklich nur die Bytes verglichen, und deshalb nicht Groß- mit Kleinbuchstaben gleichgesetzt.
Und ich bin mir gar nicht sicher, ob das in aktuellen Versionen immer noch so ist, oder sich mit der Einführung der Collations nicht erledigt hat - denn das ci steht schliesslich für case insensitive.

[...]erhalte ich z.B. für das "ü" das "ü".

Das ist UTF-8.
Kann es sein, dass du die Daten nicht in einem entsprechenden Kontext ausgibst?

[mgutt]

Das hängt irgendwie mit den zwei Tables zusammen. Ist doch nicht wegen GROUP_CONCAT() bzw. nicht alleine davon abhängig:

<?php
    // 1.
    $sql = 'SELECT GROUP_CONCAT(group_name) AS gcusername
                FROM users
                WHERE gender = 0
            GROUP BY uid
            LIMIT 5';
    $result = $db->sql_query($sql, false, __LINE__, __FILE__);
    while ( $row = $db->sql_fetchrow($result) )
    {
    echo($row['gcusername'] . '<br />');
    }
    // 2.
    $sql = 'SELECT GROUP_CONCAT(g.group_name) AS gcusername
                FROM users u, users g
                WHERE u.uid = g.group_owner
            GROUP BY u.uid
            LIMIT 5';
    $result = $db->sql_query($sql, false, __LINE__, __FILE__);
    while ( $row = $db->sql_fetchrow($result) )
    {
    echo($row['gcusername'] . '<br />');
    }
?>

1.) gibt aus:
Grüppchen

2.) gibt aus:
Grüppchen

Im Header der Seite habe ich UTF8 initialisiert:

<meta http-equiv="content-type" content="text/html; charset=UTF-8" /> 

Ich vermute im Moment einen Bug in MySQL.

Gruß

[mgutt]

Den Bug habe ich hier gemeldet:
http://bugs.mysql.com/30040

Aber ich habe wieder ein Problem mit dieser Abfrage:

SELECT DISTINCT(u.uid), u.gender, u.username, p.hobby, pic.pid, pic.ext, GROUP_CONCAT( '<a href="./?p=groups&gid=', g.uid, '">', g.group_name, '</a>' ORDER BY g.group_name SEPARATOR ', ' ) group_name, c.message
FROM contacts c, users u
LEFT JOIN pics pic ON pic.uid = c.cid
LEFT JOIN (groups ug, users g) ON u.uid = ug.uid AND ug.gid = g.uid
LEFT JOIN profiles p ON p.user_id = u.uid
WHERE c.uid = 1
AND c.cid = u.uid
AND c.uagree = 1
GROUP BY u.uid
ORDER BY u.username ASC 

Es tauchen plötzlich erneut User auf, deren Gruppen doppelt angezeigt werden.

Und zwar ist das, seit dem ich die Abfrage für die Tabelle "profiles" hinzugefügt habe.

Vielleicht zur Erklärung:
- jeder User KANN Mitglied einer Gruppe sein (groups)
- jeder User KANN ein Bild hochgeladen haben (pics)
- jeder User KANN ein Profil in einer anderen Sprache angelegt haben (profiles)

Derzeit ist es so, dass bei Mitgliedern, die mehrere Profile angelegt haben, in gleicher Anzahl Gruppen angezeigt werden. d.h. wenn er drei Profile angelegt hat, dann werden seine Gruppen alle jeweils drei mal ausgegeben. Ich kann es also "verstehen", aber weiß nicht, wie ich es unterbinden könnte

[ghostgambler]

Original geschrieben von mgutt
- jeder User KANN ein Profil in einer anderen Sprache angelegt haben (profiles)

Na wenn jeder User mehrere Profile haben kann, taucht auch jedes Profil kombiniert mit den vorigen Tabellen auf.