Ostatni wpis (z grup i subgrup), Ostatni wpis z grupy i subgrupy Opcje

[Mephis]

Witam.

Posiadam coś takiego jak:
- grupy

• id
• nazwa

- debaty (należą do grup)

• id
• id_grupy
• nazwa

- wpisy (należą do debat)

• id
• id_debaty
• treść
• data

Poniżej umieszczam zrzut bazy danych:

[SQL] pobierz, plaintext 
CREATE DATABASE IF NOT EXISTS `test_lastentry` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `test_lastentry`;
 
CREATE TABLE `groups` (
  `group_id` tinyint(1) UNSIGNED NOT NULL,
  `name` varchar(65) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `debates` (
  `debate_id` smallint(2) UNSIGNED NOT NULL,
  `group_fk` tinyint(1) UNSIGNED NOT NULL,
  `name` varchar(65) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `entries` (
  `entry_id` smallint(2) UNSIGNED NOT NULL,
  `debate_fk` smallint(2) UNSIGNED NOT NULL,
  `content` varchar(65) NOT NULL,
  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `groups` (`group_id`, `name`) VALUES
(1, 'group_one'),
(2, 'group_second'),
(3, 'group_third');
 
INSERT INTO `debates` (`debate_id`, `group_fk`, `name`) VALUES
(1, 1, 'debate 1 for group 1'),
(2, 2, 'debate 1 for group 2'),
(3, 2, 'debate 2 for group 2'),
(4, 2, 'debate 3 for group 2'),
(5, 3, 'debate 1 for group 3'),
(6, 3, 'debate 2 for group 3');
 
INSERT INTO `entries` (`entry_id`, `debate_fk`, `content`, `date`) VALUES
(1, 1, 'entry 1 for debate 1', '2017-03-12 03:12:23'),
(2, 1, 'entry 2 for debate 1', '2017-03-13 03:15:18'),
(3, 2, 'entry 1 for debate 2', '2017-03-13 06:40:20'),
(4, 2, 'entry 2 for debate 2', '2017-03-14 09:15:24'),
(5, 3, 'entry 1 for debate 3', '2017-03-12 02:12:05'),
(6, 3, 'entry 2 for debate 3', '2017-03-13 13:47:52'),
(7, 3, 'entry 3 for debate 3', '2017-03-15 14:15:15'),
(8, 4, 'entry 1 for debate 4', '2017-03-15 14:15:15'),
(9, 5, 'entry 1 for debate 5', '2017-03-11 08:11:39'),
(10, 5, 'entry 2 for debate 5', '2017-03-12 16:22:18'),
(11, 6, 'entry 1 for debate 6', '2017-03-14 09:15:24');
 
ALTER TABLE `groups`
  ADD PRIMARY KEY (`group_id`);
ALTER TABLE `debates`
  ADD PRIMARY KEY (`debate_id`);
ALTER TABLE `entries`
  ADD PRIMARY KEY (`entry_id`);
 
ALTER TABLE `groups`
  MODIFY `group_id` tinyint(1) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
ALTER TABLE `debates`
  MODIFY `debate_id` smallint(2) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
ALTER TABLE `entries`
  MODIFY `entry_id` smallint(2) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
[SQL] pobierz, plaintext 

A tutaj zobrazowane dane:
(IMG:https://zapodaj.net/images/d9263e25416b7.png)
GROUP - grupa
D - "debates" - debaty
E - "entries" - wpisy
last (X) - ostatni wpis w debacie (o czasie X)


Najpierw sprawa jest w miarę prosta.
Utworzyłem sobie listę "debat" wraz z danymi dot. ostatniego wpisu i wygląda to następująco:

[SQL] pobierz, plaintext 
SELECT
	`d`.`debate_id`,
	`d`.`name`,
	`last_e`.*
FROM `debates` AS `d`
LEFT JOIN (
	SELECT
		`e`.`debate_fk`,
		`e`.`entry_id`,
		`e`.`content`,
		`e`.`date`
	FROM `entries` AS `e`
	INNER JOIN (
		SELECT
			`debate_fk`,
			MAX(`date`) AS `date`
		FROM `entries`
		GROUP BY `debate_fk`
	) AS `last_e_pd` ON (
		(`last_e_pd`.`debate_fk` = `e`.`debate_fk`)
		AND (`last_e_pd`.`date` = `e`.`date`)
	)
) AS `last_e` ON (`last_e`.`debate_fk` = `d`.`debate_id`)
[SQL] pobierz, plaintext 

Dałoby się jakąś to uprościć? Jakieś uwagi?

Cóż chciałbym osiągnąć? Chciałbym pobrać listę grup (a nie dabat) wraz z danymi dot. ostatniego wpisu (i dot. debaty do której wpis ten należy) w grupie...
Czyli identyfikator i nazwę grupy, identyfikator, treść i datę ostatniego wpisu oraz identyfikator i nazwa debaty.

Przy liście z "debatami" sprawa była prosta o tyle, że pobierałem najwyższą wartość (date) i grupowałem wg. id debaty - tutaj otrzymywałem wymagany wynik.
Jeśli chciałbym do wpisów dołączyć tabelę debat (aby otrzymać z nich fk grupy), to grupując wg fk grupy nie otrzymam pożądanych wyników, ponieważ zgadzać będzie się id grupy, data ostatniego wpisu, ale id debaty zostanie wzięte pierwsze z brzegu (i nie będzie zgodny z oczekiwaniami... eh, gdyby tylko ten "MAX" wybierał cały wiersz a nie tylko komórkę).
Posiadając id grupy i datę ostatniego wpisu, nie mogę do tego dołączyć wpisów (co mógłbym zrobić tylko na podstawie daty), ponieważ jeśli wpisy z różnych debat będą mieć identyczny czas (mało prawdopodobne, ale jednak), to wynik będzie nieprawidłowy.

Czy ktoś borykał się z podobnym problemem? Miałby ktoś jakieś pomysły?

Proszę o pomoc.

Ten post edytował Mephis 18.03.2017, 19:52:50

[javafxdev]

"Chciałbym pobrać listę grup (a nie dabat) wraz z danymi dot. ostatniego wpisu (i dot. debaty do której wpis ten należy) w grupie..."
a potem:
"...ponieważ jeśli wpisy z różnych debat będą mieć identyczny czas (mało prawdopodobne, ale jednak), to wynik będzie nieprawidłowy..."

to co chciałbyś uzyskać jak jednak ten przypadek się zdarzy? listę dot. ostatnich wpisów czy jak niby to by miało działać?

[Mephis]

W tym pierwszym zapytaniu otrzymuję listę debat i połączone z nimi ostatnie wpisy:
(IMG:https://zapodaj.net/images/8bb0a313f613a.png)

Chcę uzyskać listę grup i połączone z nimi ostatnie wpisy (i debaty), czyli te wiersze:

[SQL] pobierz, plaintext 
SELECT
	`g`.*,
	`d`.`debate_id`,
	`d`.`name`,
	`last_e`.`debate_fk`,
	`last_e`.`entry_id`,
	`last_e`.`content`,
	`last_e`.`date`
FROM `groups` AS `g`
LEFT JOIN `debates` AS `d` ON (`d`.`group_fk` = `g`.`group_id`)
LEFT JOIN (
	SELECT
		`e`.`debate_fk`,
		`e`.`entry_id`,
		`e`.`content`,
		`e`.`date`
	FROM `entries` AS `e`
	INNER JOIN (
		SELECT
			`debate_fk`,
			MAX(`date`) AS `date`
		FROM `entries`
		GROUP BY `debate_fk`
	) AS `last_e_pd` ON (
		(`last_e_pd`.`debate_fk` = `e`.`debate_fk`)
		AND (`last_e_pd`.`date` = `e`.`date`)
	)
) AS `last_e` ON (`last_e`.`debate_fk` = `d`.`debate_id`)
[SQL] pobierz, plaintext 

(IMG:https://zapodaj.net/images/b8b009e5b5390.png)

Lecz, w przypadku, kiedy chciałbym pogrupować wg. grupy i pobrać najwyższą wartość z daty (tak, jak robię to w przypadku listy debat):

[SQL] pobierz, plaintext 
SELECT
	`g`.*,
	`d`.`debate_id`,
	`d`.`name`,
	`last_e`.`debate_fk`,
	`last_e`.`entry_id`,
	`last_e`.`content`,
	MAX(`last_e`.`date`) AS `date`
FROM `groups` AS `g`
LEFT JOIN `debates` AS `d` ON (`d`.`group_fk` = `g`.`group_id`)
LEFT JOIN (
	SELECT
		`e`.`debate_fk`,
		`e`.`entry_id`,
		`e`.`content`,
		`e`.`date`
	FROM `entries` AS `e`
	INNER JOIN (
		SELECT
			`debate_fk`,
			MAX(`date`) AS `date`
		FROM `entries`
		GROUP BY `debate_fk`
	) AS `last_e_pd` ON (
		(`last_e_pd`.`debate_fk` = `e`.`debate_fk`)
		AND (`last_e_pd`.`date` = `e`.`date`)
	)
) AS `last_e` ON (`last_e`.`debate_fk` = `d`.`debate_id`)
GROUP BY `g`.`group_id`
[SQL] pobierz, plaintext 

(IMG:https://zapodaj.net/images/7f378f46efbfe.png)
...otrzymuję nieprawidłowe wyniki.