konto usunięte

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

Witam.

Mam takie oto 3 tabelki:
CREATE TABLE `notes` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `tags` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `note_tag_xref` (
`noteId` int(11) NOT NULL,
`tagId` int(11) NOT NULL,
PRIMARY KEY (`noteId`,`tagId`)
);

INSERT INTO `notes` (`id`, `title`, `content`) VALUES
(1, 'hello', 'world'),
(2, 'ala', 'co ma kota'),
(3, 'tomek', 'co ma psa');

INSERT INTO `note_tag_xref` (`noteId`, `tagId`) VALUES
(1, 2),
(2, 1),
(2, 2),
(3, 1),
(3, 3);

INSERT INTO `tags` (`id`, `name`) VALUES
(1, 'aaa'),
(2, 'bbb'),
(3, 'ccc');


Pierwsza zawiera dane (tu akurat notatki), druga tagi, po których chce wyszukiwać notatki, a trzecia jest tabelką łączącą.

Wyszukanie wszystkich notatek z posiadających tag:
SELECT n . *
FROM notes n, tags t, note_tag_xref x
WHERE x.tagId = t.id AND x.noteId = n.id AND t.name = 'aaa'


lub posiadający jeden z wielu tagów:

SELECT n . *
FROM notes n, tags t, note_tag_xref x
WHERE x.tagId = t.id AND x.noteId = n.id AND t.name
IN (
'aaa', 'bbb'
)

nie jest niczym trudnym.

Problemem dla mnie jest skonstruowanie takiego zapytania, abym mógł wyszukiwać notatki posiadające wszystkie przekazane tagi (np. 'aaa' oraz 'bbb').
Fakt, mogę napisać coś takiego:

SELECT n . *
FROM notes n, tags t1, tags t2, note_tag_xref x1, note_tag_xref x2
WHERE (
x1.tagId = t1.id AND x1.noteId = n.id AND t1.name = 'aaa'
) AND (
x2.tagId = t2.id AND x2.noteId = n.id AND t2.name = 'bbb'
)


Ale z każdym kolejnym tagiem, po którym chce szukać, to zapytanie staje się coraz dłuższe i bardziej zagmatwane, a możliwe, że i niewydajne.

Wymyśliłem jeszcze jeden sposób:
SELECT n. * , count( * ) AS __q
FROM notes n, tags t, note_tag_xref x
WHERE t.name
IN (
'aaa', 'bbb'
) AND x.tagId = t.id AND x.noteId = n.id
GROUP BY n.id
HAVING __q =2


ale wydaje mi się on "strasznie na około", a poza tym nie pozwala mi na bardziej skomplikowane zapytania o tagi, typu: pobierz posiadające (aaa oraz bbb) lub ccc .

Może ktoś z was spotkał się z takim problemem. Może rozwiązanie jest banalne, ale nie potrafię na nie wpaść.

Będę wdzięczny za pomoc.

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

masz rację, wchodzisz już w takie rejony SQL-a, że trzeba nieco pokombinować. Tym bardziej że w praktyce możesz jeszcze zmienić schemat bazy danych na wygodniejszy dla wybranych zapytań.

Na początek polecam indeks na tags.name, bez tego już pierwszy twój przykład musi robić full-scan na którejś z tabel.

konto usunięte

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

Ja bym się na twoim miejscu zastanowił w jakiej kolejności łączysz te tabele, bo wg mnie w pesymistycznym przypadku, gdy baza będzie nie za duża wykonasz tysiące razy więcej złączeń. ;)

konto usunięte

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

Udało mi się napisać dość uniwersalną wersję zapytania:

SELECT n. *
FROM notes n
WHERE n.id
IN (

SELECT x.noteId
FROM tags t, note_tag_xref x
WHERE t.name
IN (
'aaa', 'bbb'
)
AND x.tagId = t.id
GROUP BY x.noteId
HAVING count( * ) =2
)
OR n.id
IN (

SELECT x.noteId
FROM tags t, note_tag_xref x
WHERE t.name = 'ccc'
AND x.tagId = t.id
)


Wyszkuje notatki wg wzorca: (aaa i bbb) lub ccc
Łatwo mogę ją rozbudowywać o kolejne zależności.
Teraz pozostaje mi kwestia poprawy wydajności.
Marcin P.

Marcin P. Software Engineer /
PL/SQL Developer

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

Może coś takiego zadziała:

SELECT n . *
FROM tags t
JOIN note_tag_xref x ON x.tagId = t.id
JOIN notes n ON x.noteId = n.id
WHERE t.name = 'aaa'
INTERSECT
SELECT n . *
FROM tags t
JOIN note_tag_xref x ON x.tagId = t.id
JOIN notes n ON x.noteId = n.id
WHERE t.name = 'bbb'
Grzegorz G.

Grzegorz G. ASE / Systems
Architect, Syniverse

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

SELECT
MAX(CASE WHEN tag = 'aaa' THEN 1 ELSE 0 END) aaa,
MAX(CASE WHEN tag = 'bbb' THEN 1 ELSE 0 END) bbb,
MAX(CASE WHEN tag = 'ccc' THEN 1 ELSE 0 END) ccc
FROM tags
WHERE tag IN ('aaa', 'bbb', 'ccc')


A potem doklejasz co chcesz,
WHERE aaa = 1
WHERE (aaa = 1 AND bbb = 1) OR ccc = 1
WHERE (aaa = 1 AND bbb = 1) OR (ccc = 1 AND aaa = 0)

Czyli suma sumarum

SELECT n.* 
FROM notes n
WHERE n.id IN (
SELECT nId
FROM (
SELECT x.noteId nId,
MAX(CASE WHEN tag = 'aaa' THEN 1 ELSE 0 END) aaa,
MAX(CASE WHEN tag = 'bbb' THEN 1 ELSE 0 END) bbb,
MAX(CASE WHEN tag = 'ccc' THEN 1 ELSE 0 END) ccc
FROM tags t, note_tag_xref x
WHERE t.name IN ('aaa', 'bbb', 'ccc')
AND x.tagId = t.id
GROUP BY x.noteId
)
WHERE (aaa = 1 AND bbb = 1) OR ccc = 1
)


Słowem komentarza: założenie jest, że tagi użyte przez użytkownika są bardzo selektywne dlatego spokojnie można wybrać wszystkie wiersze zaliczające się do nich. Rozwiązanie pozwala na dużą dowolność względem warunków przy równej asymptotycznej złożoności. Można za darmo dołożyć wagę warunków i takie tam. A dodatkowo przy "rozsądnych" (selektywnych) przypadkach powinno sie szybko liczyć...Grzegorz G. edytował(a) ten post dnia 06.03.09 o godzinie 00:19

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

Rafał Wardas:
Ja bym się na twoim miejscu zastanowił w jakiej kolejności łączysz te tabele, bo wg mnie w pesymistycznym przypadku, gdy baza będzie nie za duża wykonasz tysiące razy więcej złączeń. ;)

AFAIR MySQL sam decyduje o kolejności wykonywania złączeń, i trzeba coś dodać do zapytania żeby samemu tym sterować. Czy coś się zmieniło?
Michał Jarosz

Michał Jarosz Frontend Developer &
Team Leader

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

Raczej nic. Zresztą EXPLAIN wyświetli kolejność łączenia więc można sprawdzić.
Żeby wymusić kolejność używa się STRAIGHT_JOIN

konto usunięte

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

No Twój pomysł, Grzegorzu, bardzo mi się podoba. Chyba jest najbardziej przejrzysty, no i wydaje mi się, że wydajniejszy od moich przykładów. Wielkie dzięki.
teraz spróbuje to zaimplementować i zobacze jak to sprawdzi się "w praniu".

konto usunięte

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

Marcin Stefaniak:
Rafał Wardas:
Ja bym się na twoim miejscu zastanowił w jakiej kolejności łączysz te tabele, bo wg mnie w pesymistycznym przypadku, gdy baza będzie nie za duża wykonasz tysiące razy więcej złączeń. ;)

AFAIR MySQL sam decyduje o kolejności wykonywania złączeń, i trzeba coś dodać do zapytania żeby samemu tym sterować. Czy coś się zmieniło?
Wiesz ;) każda szanująca się baza ma coś takiego.. podobnie jak hint'y wychodzą z użytku w Ora, bo mało kto jest lepszy od statystyki. Może ty jesteś zwolennikiem podejścia "Dobrze, że nie rozumiesz RDBMS, samo cię podetrze" a ja jednak nie. Takie zabawki to pewnie nie wcześniej niż w 5.1+ a jak nasz petent działa na 4+ to bieda ;) Nawet w 0ra trzeba tuning samemu włączyć... tak więc nie popieram mimo wszystko.

A wracając do pomysłu Grzegorza: Kiedyś miałem coś podobnego do wymyślenia i nie wiele się różni ;)Rafał Wardas edytował(a) ten post dnia 06.03.09 o godzinie 23:33

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

Rafale, może ty jesteś zwolennikiem dyskutowania o podcieraniu, a ja jednak nie.
Przemysław Krygier

Przemysław Krygier Specjalista Systemów
Informatycznych

Temat: [Mysql] Wyszukiwanie po tagach (relacja wiele do wielu)

Witam

Przetłumaczyłem sobie to na SQL serwer (jest mi wygodniej)

Najpierw struktura



CREATE TABLE [dbo].[wiadomosci](
[id_wiadomosci] [bigint] NOT NULL,
[tytul] [varchar](128) COLLATE Polish_CI_AS NULL,
CONSTRAINT [PK_wiadomosci] PRIMARY KEY CLUSTERED
(
[id_wiadomosci] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[tagi](
[id_tagi] [bigint] NOT NULL,
[tekst] [varchar](32) COLLATE Polish_CI_AS NOT NULL,
CONSTRAINT [PK_tagi] PRIMARY KEY CLUSTERED
(
[id_tagi] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[wiadomosci__tagi](
[id_wiadomosci] [bigint] NOT NULL,
[id_tagi] [bigint] NOT NULL,
CONSTRAINT [PK_wiadomosci__tagi_1] PRIMARY KEY CLUSTERED
(
[id_wiadomosci] ASC,
[id_tagi] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[wiadomosci__tagi] WITH CHECK ADD CONSTRAINT [FK_wiadomosci__tagi_tagi] FOREIGN KEY([id_tagi])
REFERENCES [dbo].[tagi] ([id_tagi])
GO
ALTER TABLE [dbo].[wiadomosci__tagi] CHECK CONSTRAINT [FK_wiadomosci__tagi_tagi]
GO
ALTER TABLE [dbo].[wiadomosci__tagi] WITH CHECK ADD CONSTRAINT [FK_wiadomosci__tagi_wiadomosci] FOREIGN KEY([id_wiadomosci])
REFERENCES [dbo].[wiadomosci] ([id_wiadomosci])
GO
ALTER TABLE [dbo].[wiadomosci__tagi] CHECK CONSTRAINT [FK_wiadomosci__tagi_wiadomosci]


a samo zapytanie:


declare @tag1 as varchar(32)
declare @tag2 as varchar(32)

set @tag1 = 'monitory'
set @tag2 = 'sprzet_komputerowy'

-- wybranie identyfikatorow wiadomosci posiadajacych zadane tagi

SELECT t.id_wiadomosci
FROM
(
SELECT id_wiadomosci, count(id_tagi) as liczba_tagow FROM wiadomosci__tagi wt
WHERE wt.id_tagi IN (select id_tagi from tagi where tekst in ( @tag1, @tag2) )
GROUP BY id_wiadomosci
) as t
WHERE t.liczba_tagow = 2


Pozdrawiam



Wyślij zaproszenie do