Mam dwie tabele articles i tags:



Muszę przeliczyć ilość wszystkich tagów w tabeli tags. To nie powinno być trudne ponieważ korzystam z wyszukiwania pełnotekstowego w tabeli articles do wyszukiwania artykułów pod względem tagów. Tak więc możliwe jest coś w stylu:

[SQL] pobierz, plaintext 
 MATCH(articles.tags) AGAINST ('+tag' IN BOOLEAN MODE)
[SQL] pobierz, plaintext 

Próbowałem czegoś takiego, ale oczywiście bez efektu:

[SQL] pobierz, plaintext 
 
UPDATE tags 
SET tags.amount=COUNT(SELECT * FROM articles 
                          WHERE MATCH(articles.tags) AGAINST (CONCAT('+',tags.tag) IN BOOLEAN MODE)
[SQL] pobierz, plaintext 

Przeszukiwanie pełnotekstowe raczej nie nadaje się do tego. Jeżeli wystąpienia jakiegoś słowa przekraczają 50% ilości wszystkich słów jest ono ignorowane. Więcej info:

http://dev.mysql.com/doc/refman/5.0/en/ful...l-language.html



Dodatkowo tagi muszą być oddzielone specjalnymi znakami, jak spacja, przecinek, kropka.

No i podstawowa sprawa, że baza danych jest źle zaprojektowana. Jest niezgodna z 1NF skoro musisz w ten sposób ją przeszukiwać.

http://www.pui.ch/phred/archives/2005/05/t...l-fulltext.html
Przeczytałem ten artykuł, rozważyłem plusy i minusy, i stwierdziłem, że FULLTEXT to jednak najlepszy pomysł. Strona ma opierać się na tagach, ma być możliwość wyszukiwania wg jednego tagu, kilku itd. Drugi pomysł na jaki wpadłem to taki:
-tabela artykuły
-tabela tagi
-tabela łącząca artykuły z tagami (np. 5 tagów to 5 wpisów w tej tabeli)
Ale odrzuciłem ten pomysł ze względu na bardziej skomplikowane zapytania ( w mysql nie jestem ekspertem). W razie czego proszę o jakiś artykuł na ten temat albo opis całości, jestem otwarty na wszelkie zmiany.

Co do źle "zaprojektowanej bazy skoro musisz ja w ten sposób przeszukiwać". Mowa o FULLTEXT czy moim pytaniu? Jeśli o pytaniu, to podliczenie ma być awaryjne np. w razie usunięcia wszystkich starszych artykułów, wtedy ma być możliwość ponownego przeliczania ilości artykułów dla każdego z tagu. Natomiast to prawda, FULLTEXT ma wady w tym wypadku.

PS
FULLTEXT działa oczywiście na zasadzie boolean mode, więc nie ma problemu z limitem 50%.

Właśnie ze względu na łatwe wyszukiwanie przez boolean mode wybrałem fulltext.

Właśnie powinieneś mieć trzecią tabelę łączącą artykuły i tagi. Wtedy miałbyś relację wiele-do-wielu w najbardziej klasycznej, podstawowej formie do których silniki relacyjnych baz danych są najlepiej przystosowane. A zapytania byłby znacznie prostsze bo opierałby się na poprawnej strukturze danych.

Taką strukturę tabel przyjąłem korzystając z prezentacji
Practical Full Text Search in MySQL (od 14 slajdu FULLTEXT, od 36 proponowany przez was inverted index - przedstawiony również jako dobra jak nie lepsza metoda)

Ale nie wiem czy zmienić sposób wyszukiwania bo jedynym problemem jest właśnie to... 'awaryjne' podliczenie tagów, a na to jest zapewne jakieś niezbyt skomplikowane zapytanie.

Powinieneś mieć wspomnianą wcześniej strukturę jak podstawę. Możesz co najwyżej dodać kolumnę tagi, która będzie robiła za kopię na potrzeby wyszukiwania. Wtedy też dodajesz do tabeli z tagami kilka triggerów, które zajmować się będą aktualizacją tej kolumny w przypadku usunięcia tagu czy zmiany jego nazwy.

Całkowicie mnie to dobiło, stwierdzam że rozwiązanie wiele-do wielu jest jednak dość skomplikowane. W dodatku nie widzę żadnego naprawdę dobrego tutoriala dotyczącego późniejszych zapytań, a jak patrzę na 2 czy 3 joiny to się trochę gubię. Jeśli ktoś zna jakiś dobry artykuł na ten temat to poproszę .

Nie za bardzo wiem co tutaj trzeba by tłumaczyć, ale http://www.google.pl/search?aq=f&sourc...ny+relationship

OK, przemyślałem i rozumiem, że przy takiej strukturze jak powyżej wybieralibyśmy np. takim zapytaniem, tak?

[SQL] pobierz, plaintext 
SELECT A.*
FROM tagstoarticles M, articles A, tags T
WHERE M.tagid = T.id
AND (T.tag IN ('tag', 'tag2', 'tag3'))
AND A.id = M.articleid
GROUP BY A.id
HAVING COUNT( A.id )=3
[SQL] pobierz, plaintext 

Ale jak równocześnie pobrać wszystkie tagi danego artykułu żeby je wyświetlić? I jak to zrobić kiedy nie wyszukujemy po tagach tylko zwyczajnie wyświetlamy artykuły np wg daty?

[SQL] pobierz, plaintext 
SELECT a.id, a.content, t.tag FROM articles a INNER JOIN tagstoarticles tta ON a.id = tta.article_id INNER JOIN tags t ON t.id = tta.tag_id;
[SQL] pobierz, plaintext 

Oto co wywalczyłem po długim chodzeniu po domu, mówieniu do monitora i wielu dziwnych poświęconych mi spojrzeniach:

Zwykłe wybranie artykułów:

[SQL] pobierz, plaintext 
SELECT a.id, a.content, GROUP_CONCAT(t.tag) AS tags
FROM articles a
LEFT JOIN tagstoarticles tta ON tta.articleid = a.id
LEFT JOIN tags t ON t.id = tta.tagid
GROUP BY a.id
[SQL] pobierz, plaintext 

Wynik:


Wyszukiwanie po tagach (koniunkcja):

[SQL] pobierz, plaintext 
 SELECT a.id, a.content, GROUP_CONCAT(t.tag) AS tags
    FROM articles a
    LEFT JOIN tagstoarticles tta ON tta.articleid = a.id
    LEFT JOIN tags t ON t.id = tta.tagid
    WHERE a.id IN (SELECT A.id FROM tagstoarticles M, articles A, tags T
                   WHERE M.tagid = T.id
                   AND (T.tag IN ('tag1'))
                   AND A.id = M.articleid
                   GROUP BY A.id
                   HAVING COUNT( A.id )=1
    )
    GROUP BY a.id
[SQL] pobierz, plaintext 

Wynik:

Ale niestety drugie zapytanie to straszny bałagan, pewnie można prościej .