[MySQL][PHP] Pobranie nieprzeczytanych wiadomości użytkownika Opcje

[gogomania]

Witam,
próbuję stworzyć zapytanie, które pobierze wiadomości obecnie zalogowanego użytkownika.

Struktura tabel:

[SQL] pobierz, plaintext 
CREATE TABLE `konwersacje` (
  `konwersacja_id` int(8) NOT NULL,
  `konwersacja_temat` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `konwersacje`
  ADD PRIMARY KEY (`konwersacja_id`);
ALTER TABLE `konwersacje`
  MODIFY `konwersacja_id` int(8) NOT NULL AUTO_INCREMENT;
 
CREATE TABLE `konwersacje_czlonkowie` (
  `id` int(11) NOT NULL,
  `konwersacja_id` int(8) NOT NULL,
  `uzytkownik_id` int(11) NOT NULL,
  `konwersacja_ostatnio_ogladana` int(10) NOT NULL,
  `konwersacja_usunieta` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `konwersacje_czlonkowie`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unikalny` (`konwersacja_id`,`uzytkownik_id`),
  ADD KEY `FK_uzytkownicy_konwersacje_czlonkowie1` (`uzytkownik_id`);
ALTER TABLE `konwersacje_czlonkowie`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `konwersacje_czlonkowie`
  ADD CONSTRAINT `FK_uzytkownicy_konwersacje_czlonkowie1` FOREIGN KEY (`uzytkownik_id`) REFERENCES `uzytkownicy` (`uzytkownik_id`) ON UPDATE CASCADE;
 
CREATE TABLE `konwersacje_wiadomosci` (
  `wiadomosc_id` int(10) NOT NULL,
  `konwersacja_id` int(8) NOT NULL,
  `uzytkownik_id` int(11) NOT NULL,
  `data_wyslania` int(10) NOT NULL,
  `wiadomosc_ogladana` int(1) NOT NULL DEFAULT '0',
  `wiadomosc_tresc` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `konwersacje_wiadomosci`
  ADD PRIMARY KEY (`wiadomosc_id`),
  ADD KEY `konwersacja_id` (`konwersacja_id`),
  ADD KEY `uzytkownik_id` (`uzytkownik_id`);
ALTER TABLE `konwersacje_wiadomosci`
  MODIFY `wiadomosc_id` int(10) NOT NULL AUTO_INCREMENT;
ALTER TABLE `konwersacje_wiadomosci`
  ADD CONSTRAINT `FK_uzytkownicy_konwersacje_wiadomosci` FOREIGN KEY (`uzytkownik_id`) REFERENCES `uzytkownicy` (`uzytkownik_id`) ON UPDATE CASCADE;
 
CREATE TABLE `uzytkownicy` (
  `uzytkownik_id` int(11) NOT NULL,
  `login` varchar(32) NOT NULL,
  `haslo` varchar(255) NOT NULL,
  `imie` varchar(55) NOT NULL,
  `nazwisko` varchar(80) NOT NULL,
  `tel_komorkowy` varchar(25) NOT NULL DEFAULT '',
  `tel_stacjonarny` varchar(25) NOT NULL DEFAULT '',
  `email` varchar(200) NOT NULL,
  `firma_id` int(1) NOT NULL DEFAULT '0',
  `pozwol_email` int(1) NOT NULL DEFAULT '0',
  `uprawnienia` int(1) NOT NULL DEFAULT '0',
  `ostatnie_logowanie` datetime DEFAULT NULL,
  `zdjecie` varchar(150) DEFAULT NULL,
  `typ` enum('Normalny','Pracownik','Administrator','Super-Administrator') NOT NULL DEFAULT 'Normalny',
  `aktywny` int(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `uzytkownicy`
  ADD PRIMARY KEY (`uzytkownik_id`),
  ADD UNIQUE KEY `login` (`login`),
  ADD KEY `firma_id` (`firma_id`);
ALTER TABLE `uzytkownicy`
  MODIFY `uzytkownik_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `uzytkownicy`
  ADD CONSTRAINT `FK_firmy_uzytkownicy` FOREIGN KEY (`firma_id`) REFERENCES `firmy` (`firma_id`) ON UPDATE CASCADE;
[SQL] pobierz, plaintext 

I przyznam, że się pogubiłem. Czy ktoś może mi pomóc/nakierować?
funkcja nad którą pracuję:

[PHP] pobierz, plaintext 
function zlicz_wiadomosci_nieprzeczytane($uzytkownik_id) {
	global $connect;
 
	$uzytkownik_id = (int)$uzytkownik_id;
 
	$sql = "SELECT
				COUNT(konwersacje_wiadomosci.wiadomosc_ogladana) AS nieczytana
			FROM konwersacje_wiadomosci, konwersacje_czlonkowie
			WHERE konwersacje_wiadomosci.uzytkownik_id = {$_SESSION['uzytkownik_id']} 
			AND konwersacje_wiadomosci.wiadomosc_ogladana = 0
			AND konwersacje_czlonkowie.konwersacja_ostatnio_ogladana = 0
	";
 
	$wynik = mysqli_query($connect, $sql);
 
	if($wynik === FALSE) { 
		die(mysqli_error($connect));
	} else {
		if($row = mysqli_fetch_row($wynik)) {
			return $row[0];
		}
	}
 
}
[PHP] pobierz, plaintext 

[bostaf]

Hmm w takiej postaci nie ma możliwości ustalenia, kto jaką wiadomość przeczytał. Brakuje jednoznacznej informacji wskazującej, że dany użytkownik przeczytał daną wiadomość.

Masz "konwersacja_ostatnio_ogladana" w tabeli "konwersacje_czlonkowie", ale to tylko wskazuje kiedy dana konwersacja została otwarta w widoku do odczytu.

Masz "wiadomosc_ogladana" w "konwersacje_wiadomosci", ale to też nie wskazuje KTO oglądał daną wiadomość. Tylko tyle, że ktoś oglądał i już nie jest globalnie nieprzeczytana.

Jedyne co można z takimi danymi zrobić, to pokazać pierwszemu zalogowanemu uczestnikowi danej konwersacji, że ma N nieprzeczytanych wiadomości. W momencie jak ten użytkownik otworzy konwersację, to znaczniki się ustawią na 1 i kolejny użytkownik już będzie miał status "wszystko przeczytane".

Musiałbyś dodać jakąś tabelę, która powiąże id_wiadomosci, id_uzytkownika polem "przeczytana" (np.: "wiadomosci_przeczytane_przez"), i dopisywać tam rekordy w momencie otwarcia wiadomości do odczytu. Dodatkowo, autor wiadomości też tam powinien być dopisany w momencie wysyłania wiadomości.
Wtedy określenie ilości nieprzeczytanych wiadomości sprowadzałoby się do obliczenia różnicy a - b, gdzie:
a: ilość wszystkich wiadomości we wszystkich konwersacjach dla danego użytkownika
b: ilość wszystkich rekordów w tabeli "wiadomosci_przeczytane_przez" policzona dla id_wiadomosci z puli liczonej w "a"

To zadziała pod warunkiem, że rozumiesz że:

Np. 'admin' wysyła wiadomość do 'test_1' i 'test_2' [...] a w tabeli konwersacje_czlonkowie dodane zostaną 3 nowe wiersze z użytkownikami tej konwersacji oraz id konwersacji.

ta logika MOŻE spowodować pojawienie się duplikatów, i że programistycznie nie dopuszczasz do pojawienia się takich duplikatów.

Tak mi się wydaje.
Ale ogólnie na twoim miejscu mocno przemyślałbym ten model danych. Nie jestem pewien czy "konwersacja" jest tu w ogóle potrzebna, bo konwersację można ustalić dynamicznie na podstawie analizy łańcucha wiadomości:
1. Jedna wiadomość to logicznie już konwersacja. Bez odpowiedzi, ale konwersacja.
2. Dwie i więcej wiadomości to już porządna konwersacja, której początek, ciąg i koniec można ustalić dodając pole "w_odpowiedzi_na" przechowujące id_wiadomosci poprzedniej, ale jakiejkolwiek innej....

[bostaf]

W takim razie zacytuję sedno tego, co napisałem w poprzednim poście:

Hmm w takiej postaci nie ma możliwości ustalenia, kto jaką wiadomość przeczytał. Brakuje jednoznacznej informacji wskazującej, że dany użytkownik przeczytał daną wiadomość.

Miałem tu na myśli, że w bazie danych nie masz w tej chwili informacji o tym kto jaką wiadomość przeczytał lub nie. Czyli nie skonstruujesz zapytania SQL które odpowie na Twoje pytanie.
Reszta tamtego posta to szybka analiza tego co masz dostępne i propozycja (jedna z kilku opcji) w jaki sposób taką funkcjonalność wdrożyć. Liczba nieprzeczytanych to przykład; taką samą techniką można te wiadomości pobrać.

[gogomania]

W takim razie zacytuję sedno tego, co napisałem w poprzednim poście:

Miałem tu na myśli, że w bazie danych nie masz w tej chwili informacji o tym kto jaką wiadomość przeczytał lub nie. Czyli nie skonstruujesz zapytania SQL które odpowie na Twoje pytanie.
Reszta tamtego posta to szybka analiza tego co masz dostępne i propozycja (jedna z kilku opcji) w jaki sposób taką funkcjonalność wdrożyć. Liczba nieprzeczytanych to przykład; taką samą techniką można te wiadomości pobrać.

Przepraszam, może to nie niemożliwe, ale mam tą funkcjonalność tj. mam tą informację która wiadomość została przeczytana:

[PHP] pobierz, plaintext 
function pobierz_sume_konwersacji() {
	global $connect;
	$sql = "SELECT
				  k.konwersacja_id
				, k.konwersacja_temat
				, MAX(kw.data_wyslania) AS konwersacja_ostatnia_odpowiedz
				, MAX(kw.data_wyslania) > (kc.konwersacja_ostatnio_ogladana) AS konwersacja_nieprzeczytana
			FROM konwersacje AS k
			LEFT JOIN konwersacje_wiadomosci AS kw ON k.konwersacja_id = kw.konwersacja_id
			INNER JOIN konwersacje_czlonkowie AS kc ON k.konwersacja_id = kc.konwersacja_id
			WHERE kc.uzytkownik_id = {$_SESSION['uzytkownik_id']} 
			AND kc.konwersacja_usunieta = 0 
			GROUP BY k.konwersacja_id 
			ORDER BY konwersacja_ostatnia_odpowiedz DESC
			";
	$wynik = mysqli_query($connect, $sql);
	$konwersacje = array();
	while ($wiersz = $wynik->fetch_assoc()) {
		$konwersacje[] = array(
			'id' 						=> $wiersz['konwersacja_id'],
			'temat' 					=> $wiersz['konwersacja_temat'],
			'ostatnia_odpowiedz' 		=> $wiersz['konwersacja_ostatnia_odpowiedz'],
			'wiadomosci_nieprzeczytane' => ($wiersz['konwersacja_nieprzeczytana'] == 1)
		);
	}
	return $konwersacje;
}
[PHP] pobierz, plaintext 

W tej funkcji pobieram 'konwersacja_nieprzeczytana' i wyświetlam na liście konwersacji informację np. "Nowe" co jest jednoznaczne z tym, że są nowe wiadomości w konwersacji.

[PHP] pobierz, plaintext 
function pobierz_wiadomosci_konwersacji($konwersacja_id) {
	global $connect;
	$konwersacja_id = (int)$konwersacja_id;
	$sql = "SELECT
				  kw.data_wyslania
				, kw.data_wyslania > kc.konwersacja_ostatnio_ogladana AS konwersacja_nieprzeczytana
				, kw.wiadomosc_tresc
				, CONCAT(u1.imie,' ',u1.nazwisko) AS nadawca
				, u1.uzytkownik_id AS nadawca_id
				, u1.zdjecie
			FROM konwersacje_wiadomosci AS kw
			INNER JOIN konwersacje_czlonkowie AS kc ON kw.konwersacja_id = kc.konwersacja_id
			INNER JOIN uzytkownicy AS u1 ON u1.uzytkownik_id = kw.uzytkownik_id
			WHERE kw.konwersacja_id = {$konwersacja_id}
			AND kc.uzytkownik_id = {$_SESSION['uzytkownik_id']}
			ORDER BY kw.data_wyslania DESC";
	$wynik = mysqli_query($connect, $sql);
	$wiadomosci = array();
	while ($wiersz = $wynik->fetch_assoc()) {
		$wiadomosci[] = array(
			'data' 				=> $wiersz['data_wyslania'],
			'nadawca' 			=> $wiersz['nadawca'],
			'nadawca_id' 		=> $wiersz['nadawca_id'],
			'zdjecie'			=> $wiersz['zdjecie'],
			'nieprzeczytana' 	=> $wiersz['konwersacja_nieprzeczytana'],
			'tresc' 			=> $wiersz['wiadomosc_tresc'],
		);
	}
	return $wiadomosci;
}
[PHP] pobierz, plaintext 

Tutaj między innymi pobieram ile jest wiadomosci nieprzeczytanych dla danej konwersacji...

Ale brakuje mi jak wspomniałem funckji która wyświetli mi wszystkie nieprzeczytane wiadomości we wszystkich konwersacjach użytkownika. Tzn. zapytania SQL. Jakieś pomysły jak powinno składać wyglądać te zapytanie?