[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....