//DEVGURU

Zaledwie wczoraj pisałem o GROUP_CONCAT() w SQL, o możliwości dodatkowej filtracji zwracanych rekordów przez HAVING aby stwierdzić, że odwoływanie się do konkretnej kolumny zamiast do wyniku funkcji agregacyjnej w HAVING (co jest możliwe w MySQL ale nie do końca zgodne ze specyfikacją SQL) nie działa tak jak powinno.

Wracając do przykładu z poprzedniego wpisu:

SELECT entries.*, channels.channel_name AS chname,
           GROUP_CONCAT(channels.channel_name) AS channel_names
FROM entry_channel
INNER JOIN entries ON entry_channel.entry_id = entries.entry_id
INNER JOIN channels ON entry_channel.channel_id = channels.channel_id
GROUP BY entry_channel.entry_id
HAVING chname = 'krzaki'
ORDER BY entries.created_at DESC
LIMIT 25

Wynik:

Otrzymaliśmy 1 rekord gdyż wiersz zawierający string ‘krzaki’ pochodził z ostatniego wiersza podlegającego agregacji za pomocą GROUP BY, gdyby w zapytaniu zamiast HAVING chname = ‘krzaki’ znalazło się HAVING chname = ’swieta’ otrzymalibyśmy pusty wynik (pomimo, że taka wartość występuje w odpowiedniej kolumnie jednego z agregowanych wierszy).

Czyli jak skutecznie przefiltrować wyniki odnosząc się do wartości uzyskiwanej przez GROUP_CONCAT()?

…..HAVING jest jak najbardziej przydatne (jest wykonywane jako jedna z ostatnich procedur przez zwróceniem wyniku) ale kluczowe jest użycie funkcji FIND_IN_SET()

Oto zmodyfikowane zapytanie:

SELECT entries.*,
           GROUP_CONCAT(channels.channel_name) AS channel_names,
           FIND_IN_SET('swieta', GROUP_CONCAT(channels.channel_name)) AS found
FROM entry_channel
INNER JOIN entries ON entry_channel.entry_id = entries.entry_id
INNER JOIN channels ON entry_channel.channel_id = channels.channel_id
GROUP BY entry_channel.entry_id
HAVING found != 0
ORDER BY entries.created_at DESC
LIMIT 25

Zmiany:
usunąłem: “channels.channel_name AS chname” – nie ma żadnego sensu – jako kryterium dla HAVING – jeśli agregacji podlega więcej niż 1 wiersz

dodałem: “FIND_IN_SET(’szukana_wartość_w_agregowanej_kolumnie’, GROUP_CONCAT(agregowana_kolumna)) AS found”
oraz
“HAVING found != 0″

FIND_IN_SET() zwraca 0 jeśli szukana fraza nie znajduje się w zestawie.

Święta to idealny czas na szukanie idealnych rozwiązań również w zakresie webdevelopmentu :)

Między tabelami “entries”, “channels” istnieje relacja wiele-do-wielu definiowana przez tabelę “entry_channel”.

Zadanie 1: Jak w jednym zapytaniu SQL wybrać rekordy z tabeli “entries” z przypisanymi kilkoma rekordami z “channels”… tak aby otrzymać unikalne rekordy “entries”?

Rozwiązanie 1: Rozwiązanie jest całkiem oczywiste – należy użyć grupowania po PK tabeli “entries”.

Zadanie 2: Jak w tym samym zapytaniu SQL otrzymać informacje na temat unikalnych komórek rekordów, które zostały zgrupowane (scalone)?

Rozwiązanie 2: Należy użyć funkcji GROUP_CONCAT(), która utworzy nam dodatkową kolumnę z danymi, które potrzebujemy (dane są domyślnie oddzielone “,”).

Prototyp GROUP_CONCAT()

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

Korzystając z HAVING możemy przefiltrować dane zachowując pełną informację z GROUP_CONCAT()

Przykładowe zapytanie MySQL*

SELECT entries.*, channels.channel_name AS chname,
           GROUP_CONCAT(channels.channel_name) AS channel_names
FROM entry_channel
INNER JOIN entries ON entry_channel.entry_id = entries.entry_id
INNER JOIN channels ON entry_channel.channel_id = channels.channel_id
GROUP BY entry_channel.entry_id
HAVING chname = 'krzaki'
ORDER BY entries.created_at DESC
LIMIT 25

Wynik:

*) – odwołanie w HAVING do kolumny zamiast do wyniku funkcji agregacyjnej nie jest do końca zgodne ze specyfikacją SQL, natomiast w MySQL jak najbardziej działa

UPDATE: GROUP_CONCAT zwraca wartości typu BLOB jeśli listowane są wartości liczbowe. Aby otrzymać string należy użyć funkcji CAST()……konkretnie: GROUP_CONCAT(CAST(MyID as CHAR)) AS MyIDs