[PHP][MSSQL] Kilka kategorii jednego artykułu Opcje

[Kshyhoo]

Witam. Pytanie czysto teoretyczne: jak najlepiej trzymać kategorie artykułu przyjmując, że będzie go można przydzielić do kilku kategorii?
Naczytałem się trochę w Google, ale mam mątwik w głowie - wszystkie znalezione przeze mnie sposoby mają tylko wady a żaden ni jednej zalety (tak twierdzą postujący).

[mortus]

W osobnej tabeli, przy czym mogłaby ona posiadać dodatkową kolumnę mówiącą o priorytecie kategorii.
CATEGORIES: { id, name }
ARTICLES: { id, title, content }
ARTICLES_CATEGORIES: { article_id, category_id, category_priority }

EDIT: Jeśli nie ma jakiejś kategorii głównej dla danego artykułu, to ta trzecia kolumna jest zbędna.

Ten post edytował mortus 1.01.2012, 12:32:10

[Kshyhoo]

To tej pory miałem tak dla kategorii:

[SQL] pobierz, plaintext 
kat_id kat_kategoria
[SQL] pobierz, plaintext 

i dla artykułów (gdzie trzymałem ID kategorii:

[SQL] pobierz, plaintext 
tut_id tut_kat_id)
[SQL] pobierz, plaintext 

Ten post edytował Kshyhoo 1.01.2012, 12:41:31

[mortus]

Domyślam się. Musisz to zmienić, jeśli jeden artykuł może posiadać więcej niż jedną kategorię, to potrzebujesz dodatkowej tabeli realizującej relację wiele do wielu. W tej chwili masz relację jeden do wielu - jedna kategoria może być przypisana wielu artykułom.

[Kshyhoo]

Czyli trzymanie w tut_kat_id ID wielu kategorii, nie jest dobrym (optymalnym) rozwiązaniem?

[mortus]

Czyli trzymanie w tut_kat_id ID wielu kategorii, nie jest dobrym (optymalnym) rozwiązaniem?

Jakimś rozwiązaniem jest, ale na pewno nieoptymalnym.

EDIT: Gwoli uzupełnienia. Jeśli każdy z artykułów może mieć - dajmy na to - trzy do pięciu kategorii, to i takie rozwiązanie przejdzie. Ale jeśli liczba artykułów i kategorii z biegiem czasu znacznie wzrośnie, podobnie jak i liczba użytkowników, to np. przeszukiwanie po kategorii będzie prawdziwą katorgą dla serwera baz danych.
Według mnie jedynym słusznym rozwiązaniem jest stworzenie dodatkowej tabeli, bowiem przy takiej strukturze bazy danych zbudowanie zapytania będzie dużo łatwiejsze, a samo manipulowanie danymi będzie bardziej wydajne.

Ten post edytował mortus 1.01.2012, 13:28:05

[Kshyhoo]

Czy tak wystarczy, czy mam jakieś indeksy zakładać?

[SQL] pobierz, plaintext 
CREATE TABLE `tut_kat` (
`tut_id` INT NOT NULL ,
`kat_id` INT NOT NULL ,
`kat_poziom` INT NOT NULL
) ENGINE = MYISAM ;
[SQL] pobierz, plaintext 

Jak mam rozwiązać zapytania? Do tej pory miałem tak (kategorie):

[SQL] pobierz, plaintext 
SELECT kat_kategoria, kat_ile, kat_id  
	FROM kategorie
	LEFT JOIN (SELECT tut_kat_id, 
	COUNT(*) AS kat_ile 
	FROM tutoriale 
	GROUP BY tut_kat_id) AS tutoriale
	ON kat_id = tut_kat_id
	ORDER BY kat_kategoria ASC
[SQL] pobierz, plaintext 

Ten post edytował Kshyhoo 1.01.2012, 13:46:46

[mortus]

Zdaje się, że operacje na tabeli zdecydowanie przyspieszy PRIMARY KEY na dwie kolumny jednocześnie, czyli:

[SQL] pobierz, plaintext 
CREATE TABLE `tut_kat` (
 `tut_id` INT NOT NULL ,
 `kat_id` INT NOT NULL ,
 `kat_poziom` INT NOT NULL,
 PRIMARY KEY `tut_kat_pk` (`tut_id`, `kat_id`)
) ENGINE = MYISAM ;
[SQL] pobierz, plaintext 

Co do zapytania, to jakoś tak:

[SQL] pobierz, plaintext 
SELECT kat_kategoria, kat_ile, kat_id
FROM kategorie
LEFT JOIN (
  SELECT kat_id, COUNT(*) AS kat_ile
  FROM tutoriale
  GROUP BY kat_id) AS tutoriale
USING (`kat_id`)
ORDER BY kat_kategoria ASC
[SQL] pobierz, plaintext 

Choć to tak na szybko wymyślone i dopiero później mogę ewentualnie poprawić.

Ten post edytował mortus 1.01.2012, 14:02:48

[Kshyhoo]

Przeglądając to, coś spłodziłem i nawet nieźle wygląda:

[SQL] pobierz, plaintext 
CREATE TABLE `tut_kat` (
  `kat_id_1` int(11) NOT NULL,
  `kat_id_2` int(11) NOT NULL,
  `kat_poziom` int(11) NOT NULL,
  PRIMARY KEY  (`kat_id_1`,`kat_id_2`),
  KEY `kat_id_2` (`kat_id_2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
[SQL] pobierz, plaintext 

gdzie dwie pierwsze kolumny to poziomy kategorii a trzecia to zagłębienie.
Mam teraz jedynie problem, ze zliczeniem artykułów w poszczególnych kategoriach. Przedtem działałem tak:

[SQL] pobierz, plaintext 
SELECT kat_kategoria, kat_ile, kat_id  
FROM kategorie
LEFT JOIN (SELECT tut_kat_id, 
COUNT(*) AS kat_ile 
FROM tutoriale 
GROUP BY tut_kat_id) AS tutoriale
ON kat_id = tut_kat_id
ORDER BY kat_kategoria ASC
[SQL] pobierz, plaintext 

[mortus]

A to zapytanie, które wyżej napisałem daje jakieś wyniki? Pisałem "na szybko", bo musiałem wyjść i nie wiem, czy działa i czy działa prawidłowo.

[Kshyhoo]

Zwraca błąd Unknown column 'kat_id' in 'field list' .

W sumie, ja nadal nie wiem, jak to trzymać w bazie - jak artykuł ma mieć wiele kategorii.

[mortus]

To może po polsku (kolumny przykładowe).
KATEGORIE: {
id_kategorii (klucz główny)
nazwa
}
ARTYKUŁY: {
id_artykułu (klucz główny)
tytuł,
data_utworzenia
}
KATEGORIE_ARTYKUŁÓW: {
id_kategorii (klucz obcy połączony z tabelą KATEGORIE kolumną id_kategorii)
id_artykułu (klucz obcy połączony z tabelą ARTYKUŁY kolumną id_artykułu)
ważność (kolumna dodatkowa określająca, która z kategorii jest ważniejsza - kolumnę tę można pominąć)
}

Nie do końca wiem, co ma robić to zapytanie. Ma liczyć liczbę artykułów/tutoriali w każdej z kategorii? Czy może ma liczyć do ilu kategorii należy dany artykuł/tutorial?

Z tymi kategoriami jest dokładnie tak samo, jak z tagami (słowami kluczowymi) opisującymi dany artykuł.

[Kshyhoo]

Czyli, jak dobrze rozumiem, KATEGORIE_ARTYKUŁÓW ma wiązać całość:

Kod

id_artykułu | id_kategorii
     1      |      1
     1      |      4
     1      |      5
     2      |      1
     2      |      2
     2      |      4
     2      |      6

Czyli, że artykuł ID=1 należy do kategorii ID=1, 4, 5 a artykuł ID=2, do kategorii ID=1, 2, 4, 6...

Ten post edytował Kshyhoo 1.01.2012, 20:11:25

[mortus]

Dokładnie, wtedy zapytaniem:

[SQL] pobierz, plaintext 
SELECT `K`.`id_kategorii`, `K`.`nazwa`, COUNT(*) AS `liczba_artykułów` FROM `KATEGORIE` `K` LEFT JOIN `KATEGORIE_ARTYKUŁÓW` `K_A` USING (`id_kategorii`) GROUP BY `K`.`id_kategorii`
[SQL] pobierz, plaintext 

wyciągniesz identyfikatory, nazwy oraz liczbę artykułów dla każdej z kategorii.

[Kshyhoo]

Mam problem jedynie z kluczami obcymi, bo baza w MyISAM.
No i muszę znaleźć sposób, na wybór i zapis (również edycję) wpisów w bazie poprzez PHP.

No i pobierałem artykuł tak:

[PHP] pobierz, plaintext 
$zapytanie1 = "SELECT * FROM tutoriale LEFT JOIN kategorie ON (kat_id = tut_kat_id) WHERE tut_id='$tut_id'";
[PHP] pobierz, plaintext 

Teraz trzeba będzie to zmienić.

Ten post edytował Kshyhoo 1.01.2012, 20:53:55

[mortus]

Nie ma znaczenia, czy to MyISAM, czy InnoDB. Najwyżej nie założysz kluczy obcych, a poza tym i tak wszystko w PHP musisz zrealizować. Wybór to proste potrójne złączenie, a zapis to po prostu dwa zapytania w tym MULTIINSERT.

[Kshyhoo]

Jak mniemam, moje zapytanie o pojedynczy rekord powinno wyglądać tak:

[PHP] pobierz, plaintext 
$zapytanie1 = "SELECT * FROM tut_kat, tutoriale, kategorie
WHERE tut_kat.id_tut=tutoriale.tut_id AND tut_kat.id_kat=kategorie.kat_id
AND tut_kat.id_tut='$tut_id'";
[PHP] pobierz, plaintext 

Zmieniłem nieco moje zapytanie i wygląda ono obecnie tak:

[PHP] pobierz, plaintext 
$zapytanie1 = "SELECT tut_id, tut_nr, tut_obraz, tut_tytul, tut_autor, kat_kategoria, tut_data, tut_zrodlo, tut_notatki, tut_moderator, tut_czytano, tut_stan, tut_psd, tut_tresc
	FROM tut_kat, tutoriale, kategorie
	WHERE tut_kat.id_tut=tutoriale.tut_id 
	AND tut_kat.id_kat=kategorie.kat_id
	AND tut_kat.id_tut='$tut_id'";
[PHP] pobierz, plaintext 

Jednak, gdy artykuł ma kilka kategorii, w phpMyAdmin mam kilka takich samych wyników, różniących się jedynie ID kategorii. Jaki mam zastosować warunek, żeby tego uniknąć?

[mortus]

Ta nadmiarowość jest jak najbardziej zrozumiała (i nawet do wybaczenia), ale można się jej pozbyć łącząc wszystkie kategorie za pomocą GROUP_CONCAT:

[SQL] pobierz, plaintext 
SELECT tut_id, tut_nr, tut_obraz, tut_tytul, tut_autor, GROUP_CONCAT(kat_kategoria) kategorie, tut_data, tut_zrodlo, tut_notatki, tut_moderator, tut_czytano, tut_stan, tut_psd, tut_tresc
FROM tut_kat, tutoriale, kategorie
WHERE tut_kat.id_tut=tutoriale.tut_id
AND tut_kat.id_kat=kategorie.kat_id
AND tut_kat.id_tut='{$tut_id}'
GROUP BY tut_id
[SQL] pobierz, plaintext 

[thek]

Ja bym się już zastanawiał nad silnikiem. Liczba artykułów z czasem rośnie. Modyfikacje tabeli złączeniowej będą może nie tak częste, ale w MyISAM głupi update czy insert robią lock na całą tabelę :/ Lepiej przejść na InnoDB w przypadku złączeniówki i mieć tylko lock na konkretne wiersze. Ogólnie mortus dobrze radzi i sam identycznie postępuję. Co do edycji lub dodania to masz zapewne MySQL, więc nie widze problemu:
1) Dodajesz nowy artykuł: INSERT zwróci Ci id nowo dodanego. Tylko go podstaw do złączeniówki (IMG:style_emoticons/default/smile.gif)
2) Uaktualniasz artykuł: Najprościej DELETE istniejących aktualnie i insert wybranych przez usera. W wersji inteligentniejszej porównujesz stare i nowe. Usuwasz to, które są w starej wersji i dodajesz te, których w starej nie ma. Plus jest taki, że może Ci się zredukować całość do tylko usuwania albo tylko dodania. Minus? Zapewne usunięcie i dodanie według pierwszej metody będą szybsze niż po stronie php z kombinowaniem co ma być dodane, a co usunięte (IMG:style_emoticons/default/smile.gif)

[Kshyhoo]

Ufff, udało mi się wymodzić takiego potwora:

[PHP] pobierz, plaintext 
$kat_list = ""; $sel = "";
$result = dbquery("SELECT kat_id, kat_kategoria FROM kategorie WHERE kat_cat='0' ORDER BY kat_order");
if (dbrows($result) != 0) {
	while ($data = dbarray($result)) {
		$result2 = dbquery("SELECT kat_id, kat_kategoria FROM kategorie WHERE kat_cat='".$data['kat_id']."' ORDER BY kat_order");
		if (dbrows($result2) != 0) {
			$kat_list .= "<optgroup label='".$data['kat_kategoria']."'>\n";
			while ($data2 = dbarray($result2)) {
				if ($_GET['kat_id'] == $data2['kat_id']) { $sel = " selected"; } else { $sel = ""; }
				$kat_list .= "<option value='".$data2['kat_id']."'$sel>".$data2['kat_kategoria']."</option>\n";
			}
			$kat_list .= "</optgroup>\n";
		}
	}
}
echo '<select name="kat_id[]" style="width:150px;" size="5" multiple>\n'.$kat_list.'</select>
[PHP] pobierz, plaintext 

Odebrać mógłbym tak:

[PHP] pobierz, plaintext 
if (isset($_POST['kat_id'])) {
 
	if (count($_POST['kat_id'])==0) echo "Musisz wybrać!";
	else echo "Wybrane kategorie: ";
 
		for ($i=0;$i<count($_POST['kat_id']);$i++) echo $_POST['kat_id'][$i]."; ";
 
}
[PHP] pobierz, plaintext 

I działa. Nie wiem teraz tylko, jak mam do zapisać do bazy, gdzie mam takie pola

[SQL] pobierz, plaintext 
id_tut 	id_kat
[SQL] pobierz, plaintext 

czyli muszę zapisać, kilka id_kat z jednym id_tut

Podpowie mi ktoś? Da się to zrobić na raz, czy pętlą?

PS. Oczywiście, brak filtrowania (nie bijcie za mocno)...

EDIT. Coś Geshi się buntuje, całe szczęście miałem kopię (IMG:style_emoticons/default/wink.gif)

Ten post edytował Kshyhoo 3.01.2012, 23:27:49

Powód edycji: [Spawnm]: hmm coś się zepsuło :P