[SQL] - [MySQL] Gestione torneo pallavolo - problema classifiche

[malocchio]

In breve, al mio paese hanno organizzato un torneo estivo di pallavolo femminile e io mi sono occupato di fare qualcosa online, per cui ho installato wordpress e alcune pagine php scritte da me per la visualizzazione dei risultati e delle classifiche + front-end di inserimento risultati per me.
Ci sono due categorie di età che sono però gestite assieme, sulle stesse tabelle.

Il sistema usa la libreria mysqli per interfacciare php con MySQL.

Funziona così:

Ho essenzialmente 3 tabelle:
- Categorie. è molto semplice associa un nome completo di categoria ("Giovani", "Ragazze") al suo codice breve, un carattere ('g', 'r'). Così diventa più veloce per il sistema assegnare delle squadre a delle categorie.
CODICE SQL

- Squadre. In pratica assegna un codice (numero intero univoco) ad ogni squadra, il nome completo e il codice di categoria. Gli id vengono attualmente usati nella tabella delle partite.
CODICE SQL

- Partite. Contiene l'elenco degli incontri giocati oppure ancora da giocare. Sono definiti gli id delle 2 squadre, data e ora (tramite un timestamp Unix) e, dopo aver registrato i risultati, i parziali di ogni set (parz_{0-3}{A|B}).
Nonostante nella definizione SQL contenga anche il numero dei set e l'id del vincitore, queste non vengono utilizzate perché sono calcolate all'occorrenza dal sistema, come scritto nelle note del link al codice.
CODICE SQL

Per visualizzare la classifica utilizzo delle view.

- La prima, calcola i risultati (ovvero set vinti, set persi, id vincitore, totale punti fatti, totale punti subiti) per ogni partita, leggendo i parziali dei set.
CODICE SQL

- La seconda, indispensabile per calcolare la classifica, prende tutte le partite (più risultati) e le inserisce 2 volte, scambiando le 2 squadre.
Così posso avere tutte le partite "viste" da tutte le squadre. Così, per ogni squadra, posso vedere tutti i risultati conseguiti.
Spero di essere stato chiaro, mi ci è voluto un po' per elaborare il sistema.
CODICE SQL

- La terza è la classifica vera e propria. Per ogni squadra (quindi un GROUP BY) somma i set vinti, set persi, partite vinte, parite perse, punti fatti, punti subiti, ecc.
CODICE SQL

Ma questa view non ordina i record per cui è praticamente "inutile".
Quindi mi sono messo d'impegno e ho aggiunto la clause ORDER BY.

Priorità dei fattori per l'ordinamento (indicazioni di mio papà):
- Set vinti (decrescente)
- Partite vinte (decrescente)
- Quoziente set (il rapporto set vinti/set persi)
- Quoziente punti (il rapporto punti fatti/punti subiti)
- Alfabetico; se tutti gli altri parametri sono uguali (caso estremo) allora si va di ordine alfabetico sul nome della squadra (quindi ascendente).

Ecco il nuovo codice SQL:
CODICE SQL che definisce l'ordinamente come l'ho spiegato io prima. Per cui ora è il momento di eseguire le query. Ma qui sorge un problema.
phpMyAdmin riporta:

 Messaggio di MySQL: Documentazione
#1247 - Reference 'SetVinti' not supported (reference to group function)

Sulla documentazione ufficiale di MySQL non ho trovato niente riguardo a ORDER BY o GROUP BY che mi faccia capire dove sbaglio. Oppure che non si possa proprio fare concettualmente (è questo il mio dubbio).

A dire la verità ho risolto il problema spostando la clause ORDER BY nella query di SELECT che esegui mysqli per leggere la view. Ma volevo che fosse la view a occuparsi dell'ordinamento.

GRAZIE IN ANTICIPO E FATE TUTTE LE DOMANDE CHE VOLETE

PS. che dite lascio il codice disponibile ai link oppure lo posto con il tag <code> ??? dite come vi sembra più comodo

[malocchio]

Se vi può essere utile ecco i link alle pagine web dove si possono visualizzare i risultati e classifiche:

Elenco partite disputate

Classifiche

[RobyG_08]

puoi postare qui la select che non funziona? ho un problema ad andare sulle pagine che linki da qui..

[malocchio]

Tabella categorie

#http://phpfi.com/330823

DELIMITER $$

DROP TABLE IF EXISTS `cats`$$

CREATE TABLE `cats` (
  `id` CHAR(1)  NOT NULL,
  `cat` VARCHAR(20)  NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE = MYISAM; $$

Tabella squadre

#http://phpfi.com/330821

DELIMITER $$

DROP TABLE IF EXISTS `teams` $$

CREATE TABLE `teams` (
	`id` 			SMALLINT 	UNSIGNED 	NOT NULL 	AUTO_INCREMENT,
	`name` 		VARCHAR(50)  	NOT NULL,
	`cat` 		CHAR(1)		NOT NULL,	
	PRIMARY KEY(`id`)
)
ENGINE = MYISAM $$

Tabella partite

#http://phpfi.com/330824

DELIMITER $$

DROP TABLE IF EXISTS `matches` $$

CREATE TABLE `matches` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `id_A` smallint(5) unsigned NOT NULL,
  `id_B` smallint(5) unsigned NOT NULL,
  `time` int(10) unsigned NOT NULL,
  `sets_A` smallint(5) unsigned default NULL, #disuso
  `sets_B` smallint(5) unsigned default NULL, #disuso
  `parz_1A` smallint(5) unsigned default NULL,
  `parz_1B` smallint(5) unsigned default NULL,
  `parz_2A` smallint(5) unsigned default NULL,
  `parz_2B` smallint(5) unsigned default NULL,
  `parz_3A` smallint(5) unsigned default NULL,
  `parz_3B` smallint(5) unsigned default NULL,
  `winner` smallint(5) unsigned default NULL, #disuso
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM $$

Vista risultati partita

#http://phpfi.com/330826

DELIMITER $$

DROP VIEW IF EXISTS `vw_match_result`$$

CREATE VIEW `vw_match_result`
AS
SELECT
  `id`                  AS `id`,
  (  (`parz_1A`>`parz_1B`)+
     (`parz_2A`>`parz_2B`)+
     (`parz_3A`>`parz_3B`)  )   AS `sets_A`,
  (  (`parz_1A`<`parz_1B`)+
     (`parz_2A`<`parz_2B`)+
     (`parz_3A`<`parz_3B`)  )   AS `sets_B`,
  `parz_1A`+`parz_2A`+`parz_3A` AS `points_A`,
  `parz_1B`+`parz_2B`+`parz_3B` AS `points_B`,
  IF( ((`parz_1A`>`parz_1B`)+
       (`parz_2A`>`parz_2B`)+
       (`parz_3A`>`parz_3B`))
     >
      ((`parz_1A`<`parz_1B`)+
       (`parz_2A`<`parz_2B`)+
       (`parz_3A`<`parz_3B`)),
     `id_A`,
      IF( ((`parz_1A`>`parz_1B`)+
           (`parz_2A`>`parz_2B`)+
           (`parz_3A`>`parz_3B`))
         <
          ((`parz_1A`<`parz_1B`)+
           (`parz_2A`<`parz_2B`)+
           (`parz_3A`<`parz_3B`)),
         `id_B`,
         NULL)
  )                            AS `winner`
FROM
  `matches`$$

Vista partite per ogni squadra

#http://phpfi.com/330827

DELIMITER $$

DROP VIEW IF EXISTS `teams_matches`$$

CREATE VIEW `teams_matches`
AS

SELECT
  `m`.`id_A`                    AS `team_id`,
  `t`.`cat`			AS `cat`,
  `t`.`name`                    AS `Squadra`,
  `m`.`id`                      AS `match_id`,
  `mr`.`sets_A`                 AS `Set vinti`,
  `mr`.`sets_B`                 AS `Set persi`,
  `mr`.`points_A`               AS `Punti fatti`,
  `mr`.`points_B`               AS `Punti subiti`,
  `mr`.`sets_A`>`mr`.`sets_B`   AS `Vinto`,
  `mr`.`sets_A`<`mr`.`sets_B`   AS `Perso`
FROM
  `matches` AS `m` INNER JOIN
    `teams` AS `t` ON `m`.`id_A`=`t`.`id` INNER JOIN
      `vw_match_result` AS `mr` ON `m`.`id`=`mr`.`id`

UNION

SELECT
  `m`.`id_B`                    AS `team_id`,
  `t`.`cat`			AS `cat`,
  `t`.`name`                    AS `Squadra`,
  `m`.`id`                      AS `match_id`,
  `m`.`sets_B`                  AS `Set vinti`,
  `m`.`sets_A`                  AS `Set persi`,
  `mr`.`points_B`               AS `Punti fatti`,
  `mr`.`points_A`               AS `Punti subiti`,
  `m`.`sets_B`>`mr`.`sets_A`    AS `Vinto`,
  `m`.`sets_B`<`mr`.`sets_A`    AS `Perso`
FROM
  `matches` AS `m` INNER JOIN
    `teams` AS `t` ON `m`.`id_B`=`t`.`id` INNER JOIN
      `vw_match_result` AS `mr` ON `m`.`id`=`mr`.`id`
$$

Vista classifica, completa di ordinamento (NON funzionante)

DELIMITER $$

DROP VIEW IF EXISTS `vw_chart_bysets`$$

CREATE VIEW `vw_chart_bysets`
AS

SELECT
  `t`.`id`                                   AS `id`,
  `t`.`cat`                                  AS `cat`,
  `t`.`name`                                 AS `Squadra`,
  SUM(`tm`.`Set vinti`)                      AS `SetVinti`,
  SUM(`tm`.`Set persi`)                      AS `SetPersi`,
  SUM(`tm`.`Set vinti`)+SUM(`tm`.`Set persi`) AS `SetGiocati`,
  SUM(`tm`.`Vinto`)                          AS `PartiteVinte`,
  SUM(`tm`.`Perso`)                          AS `PartitePerse`,
  COUNT(`tm`.`Vinto`)                        AS `PartiteGiocate`,
  SUM(`tm`.`Punti fatti`)                    AS `PuntiFatti`,
  SUM(`tm`.`Punti subiti`)                   AS `PuntiSubiti`,
  SUM(`tm`.`Punti fatti`)+SUM(`tm`.`Punti subiti`) AS `PuntiGiocati`
FROM
  `teams` AS `t` LEFT JOIN
    `teams_matches` AS `tm` ON `t`.`id`=`tm`.`team_id`
GROUP BY
  `id`
ORDER BY
  `SetVinti` DESC,
  `PartiteVinte` DESC,
  `SetVinti`/`SetPersi` DESC,
  `PuntiFatti`/`PuntiSubiti` DESC,
  `Squadra` ASC
$$

Query che eseguo sulla pagina PHP:

SELECT
  *
FROM
  `vw_chart_bysets` #la vista della classifica SENZA ordinamento
WHERE
  `cat`= ? #è uno prepared statement, sostituisco ? con il codice della categoria ('r' o 'g')
ORDER BY
  `SetVinti` DESC,
  `PartiteVinte` DESC,
  `SetVinti`/`SetPersi` DESC, #quoziente set
  `PuntiFatti`/`PuntiSubiti` DESC #quoziente punti
  `Squadra` ASC

[RobyG_08]

ho cercvato un po in giro..pare sia un bug di mysql

dovresti prima lanciare il comando sul db:

set @@sql_mode='ONLY_FULL_GROUP_BY';

poi dovrebbe darti un errore del genere:

non-grouping field 'SetVinti' is used in HAVING clause

e da qui in avanti non ho mica capito come si risolve

http://bugs.mysql.com/bug.php?id=31797

[malocchio]

Originariamente inviato da RobyG_08

ho cercvato un po in giro..pare sia un bug di mysql

dovresti prima lanciare il comando sul db:

set @@sql_mode='ONLY_FULL_GROUP_BY';

poi dovrebbe darti un errore del genere:

non-grouping field 'SetVinti' is used in HAVING clause

e da qui in avanti non ho mica capito come si risolve

http://bugs.mysql.com/bug.php?id=31797

Prima di tutto grazie della disponibilità e di aver fatto la ricerca...

secondo... potresti postare il link anche del primo bug.. così ho le informazioni complete e capire meglio perché dà l'errore...

TNX


edit: ah no scusa avevo capito male il bug è uno solo... adesso guardo se ONLY_FULL_GROUP_BY mi può essere utile...

[ceschi]

Ma nel GROUP BY non mancano cat e name ??

[malocchio]

Originariamente inviato da ceschi

Ma nel GROUP BY non mancano cat e name ??

Se non è attivato ONLY_FULL_GROUP_BY non è necessario elencare tutte le colonne di tipo non group.

Ho appena provato una normale query SELECT.

La cosa strana è che `SetVinti` lo posso mettere in ORDER BY perché se io scrivo una query del genere:

SELECT
  `t`.`id`                                   AS `id`,
  `t`.`cat`                                  AS `cat`,
  `t`.`name`                                 AS `Squadra`,
  SUM(`tm`.`Set vinti`)                      AS `SetVinti`,
  SUM(`tm`.`Set persi`)                      AS `SetPersi`,
  SUM(`tm`.`Set vinti`)+SUM(`tm`.`Set persi`) AS `SetGiocati`,
  SUM(`tm`.`Vinto`)                          AS `PartiteVinte`,
  SUM(`tm`.`Perso`)                          AS `PartitePerse`,
  COUNT(`tm`.`Vinto`)                        AS `PartiteGiocate`,
  SUM(`tm`.`Punti fatti`)                    AS `PuntiFatti`,
  SUM(`tm`.`Punti subiti`)                   AS `PuntiSubiti`,
  SUM(`tm`.`Punti fatti`)+SUM(`tm`.`Punti subiti`) AS `PuntiGiocati`
FROM
  `teams` AS `t` LEFT JOIN
    `teams_matches` AS `tm` ON `t`.`id`=`tm`.`team_id`
GROUP BY
  `id`,
  `name`,
  `cat`
ORDER BY
  `SetVinti`,
  `PartiteVinte`,
  `SetVinti`

Lui non dà nessun errore (notate che setvinti c'è 2 volte nell'order)
Ma se io invece del secondo SetVinti nell'order scrivo `SetVinti`/`SetPersi` allora mi dà errore sul / reference to group function. Cos'è la divisione tra interi non supportata? Spero di non aver beccato un bug perché azzanno quelli di AB sennò

Notate che non c'è ambiguita di nomi colonne perché quelli delle tabelle FROM sono diversi dagli alias che ho assegnato...

[RobyG_08]

Originariamente inviato da malocchio

Prima di tutto grazie della disponibilità e di aver fatto la ricerca...

secondo... potresti postare il link anche del primo bug.. così ho le informazioni complete e capire meglio perché dà l'errore...

TNX


edit: ah no scusa avevo capito male il bug è uno solo... adesso guardo se ONLY_FULL_GROUP_BY mi può essere utile...

In quella pagina dovrebbero essere riportati i riferimenti ad entrambi i bug

[malocchio]

UP..


Perché nell'ORDER mi dà errore quando uso l'operatore "/" ???

[malocchio]

up