Wyszukiwanie pokrewnych rekordów, i wyświetlanie ich według skali pokrewieństwa Opcje

[krzmig]

Witam,
chciał bym na stronie wyświetlać pokrewne działy według ilości wystąpień tych samych słów kluczowych. Szukałem na forum ale niestety nic nie znalazłem :/

Przypuśćmy że mamy tabelę: Nazwa
A w niej:

Kod

+----+----------+
| id | keywords |
+----+----------+
|  1 | a,b      |
|  2 | a,c      |
|  3 | b        |
|  4 | b,c      |
|  5 | a,b,c    |
|  6 | c,a,b    |
|  7 | a        |
|  8 | b,a      |
|  9 | c        |
+----+----------+

I teraz chciał bym wybrać rekordy względem podobieństwa do rekordu z id 1. Rekord ten pobieram już wcześniej i mam jego wartość w zmiennej php.
Jako wynik chciał bym otrzymać coś w stylu:

Kod

5,6,8,2,3,4,7

5,6,8 - z największą zgodnością (2 słowa się powtarzają)
2,3,4,7 - z mniejszą zgodnością (1 słowo się powtarza)
9 - bez zgodności (0 słów się powtarza)

A w przypadku wybieranie względem rekordu z id=5:

Kod

6,1,2,4,8,3,7,9

6 - z największą zgodnością (3 słowa się powtarzają)
1,2,3,8 - z mniejszą zgodnością (2 słowa się powtarzają)
379 - z najmniejszą zgodnością (1 słowo się powtarza)

Zapytanie mogę tworzyć dynamicznie poprzez php więc nie ma potrzeby używania nigdzie poza wykreślaniem go z wyników, rekordu dla którego szukam podobnych.
Oczywiście można dla każdej wartości wykonać osobne zapytanie i w php policzyć ilość wystąpień ale zależało by mi na czymś bardziej optymalnym, tabela na której będą wykonywane operacje nie jest wcale mała więc zależy mi na ograniczeniu operacji na niej do minimum.

[SQL] pobierz, plaintext 
SELECT * FROM Nazwa WHERE id != 1 AND ( keywords LIKE '%a%' OR keywords LIKE '%b%' );
[SQL] pobierz, plaintext 

Pobiera dobre rekordy ale w złej kolejności.

Mam nadzieję że ktokolwiek zrozumiał moje wypociny i ma jakiś pomysł ;]
Pozdrawiam.

[Kicok]

Proponuję zrobić z tej tabeli trzy:

tabela ( wszystkie dotychczasowe dane z twojej tabeli oprócz keywords )

Kod

+------+------------+
|  ID  |   nazwa    |  (...)
+------+------------+
|   1  |   jeden    |
|   2  |    dwa     |
|   3  |    trzy    |
|   4  |   cztery   |
|   5  |    pięć    |
|   6  |   sześć    |
|   7  |   siedem   |
|   8  |   osiem    |
|   9  |  dziewięć  |

keywords ( wszystkie możliwe słowa kluczowe )

Kod

+------+-----------+
|  ID  |  keyword  |
+------+-----------+
|   1  |     a     |
|   2  |     b     |
|   3  |     c     |

tabela_keywords ( przypisanie słów kluczowych do elementów tabeli tabela )

Kod

+-------------+---------------+
|  ID_tabela  |  ID_keywords  |
+-------------+---------------+
|      1      |       1       |
|      1      |       2       |
|      2      |       1       |
|      2      |       3       |
|      3      |       2       |
|      4      |       2       |
|      4      |       3       |
|      5      |       1       |
|      5      |       2       |
|      5      |       3       |
|      6      |       3       |
|      6      |       1       |
|      6      |       2       |
|      7      |       1       |
|      8      |       2       |
|      8      |       1       |
|      9      |       3       |

I przykładowe zapytanie pobierające rekordy podobne do ID = 5:

[SQL] pobierz, plaintext 
SELECT t.*
FROM tabela t
JOIN tabela_keywords tk ON ( t.id = tk.id_tabela )
JOIN keywords k ON ( tk.id_keywords = k.id )
WHERE t.id != 5 AND k.keyword IN ( 'a', 'b', 'c' )
GROUP BY t.id
ORDER BY COUNT( t.id ) DESC, t.id ASC
[SQL] pobierz, plaintext 

Zakładam tutaj, że znasz keywords-y danego elementu. Jeżeli nie, trzeba użyć podzapytania:

[SQL] pobierz, plaintext 
SELECT t.*
FROM tabela t
JOIN tabela_keywords tk ON ( t.id = tk.id_tabela )
JOIN keywords k ON ( tk.id_keywords = k.id )
WHERE t.id != 5 AND k.keyword IN (
  SELECT k2.keyword
  FROM keywords k2
  JOIN tabela_keywords tk2 ON ( k2.id = tk2.id_keywords )
  WHERE tk2.id_tabela = 5
)
GROUP BY t.id
ORDER BY COUNT( t.id ) DESC, t.id ASC
[SQL] pobierz, plaintext