[MySQL] dubbi sul "group by" e simili

[heracleum]

Ogni tanto fatemi chiedere qualche aiutino anche a me :grin:
Soprattutto riguardo MySQL, visto che su SQL ho sempre cercato quel che mi serviva senza mai studiare per bene a discapito delle ottimizzazioni...
Dunque la questione è questa:

facciamo un semplice esempio con una "message board", per capirci, con due tabelle:

"message"
con i campi:
| id | testo | timestamp | boardid |

e
"board"
con i campi:
| id | titolo | descriz |

Il rapporto tra le due tabelle penso sia semplice da capire: la gente scrive messaggi nelle differenti board, ogni messaggio ha un suo orario (unix_timestamp) e ciscun messaggio appartiene alle varie board in relazione al boardid, ovvio.

Ora quel che vorrei è riuscire ad ottenere la lista di "board" commentate più di recente in una sola query;
dunque
nei risultati ogni riga (o record) dovrebbe contenere i dati sia della "board" sia del "message" TUTTI coerenti con il valore più recente del timestamp (unix_timestamp dunque un semplice intero, int(10) ) del messaggio. Visto che è un int il fatto che sia più recente è ovviamente il valore intero maggiore.
Visto che devo pescare solo una board con il messaggio più recente dovrò sicuramente adottare un sistema di raggruppamento "group by" o simili.

Finora il massimo che sono riuscito ad ottenere nei vari tentativi è una lista di risultati dove ho -sì- il valore maggiore di timestamp - max(message.timestamp) - PERO' il resto dei dati che mi ritrovo non è allineato con il messaggio più recente.. noto che mi ritrovo il testo del message in base all' id.. forse..
(porca trota, mi fa impazzire sta cosa, datemi una mano.. HELP).

[Ospite]

se ho capito bene vuoi praticamente la lista delle board in ordine di ultimo messaggio inserito più recentemente...

prova con

SELECT MAX(timestamp) AS max,id FROM messages GROUP BY id ORDER BY max DESC

se invece devi selezionare anche il titolo e la descrizione con la query prova con

SELECT MAX(messages.timestamp) AS max, board.titolo, board.descriz FROM messages,board GROUP BY messages.id ORDER BY max DESC

ma non sono sicuro della seconda... :d

[heracleum]

*SIGH* esattamente come ho fatto in uno degli svariati tentativi...
Mi ferisco alla seconda query (perché mi servono i dati di entrambe le tabelle).

Niente da fare, i risultati degli altri dati non hanno alcun legame con il MAX(messages.timestamp) che è l'unico dato corretto, gli altri vanno per conto loro.

Suppongo dunque che ci sta sfuggendo qualcosa nella clausola WHERE, cioè penso proprio che ci dovrebbe essere e che dovremmo stabilire questa coerenza (mancante per ora) tra il max e i dati relativi a quella data.
Pensavo dunque di aggiungere un ..WHERE message.timestamp = max ...
ma gli alias non me li fa usare nella where (non capisco il motivo ma mi fido).

Mi sono avventurato anche in JOIN varie (con pessimi risultati)...

[binarysun]

SELECT * FROM messages GROUP BY boardid ORDER BY timestamp DESC LIMIT 0,10;

Così funziona?

[heracleum]

Binarysun:
sì ma in quel modo, visto che si lavora solo sulla tabella dei messaggi, ottengo -sì- gli ultimi messaggi di ciascuna board, però non ho alcun dato delle board se non l'ID di riferimento.
Dunque facendo così dovrei fare un'ulteriore query per ottenere gli altri dati utili delle board, indicando nella clausola WHERE un triste elencone di tutti gli ID risultanti dalla query precedente (WHERE id = 3 AND id = 5 AND id = 1 AND ... )
E' senza dubbio un modo per risolvere e andare avanti, senza complicarsi la vita troppo con query complesse, ma risolvere tutto i un'unica query era la mia domanda di fondo nel post e sicuramente è il metodo più ottimizzato (e penso proprio ci sia un modo! a saperlo).

Dai dai, smanettoni di mysql, please! trovate una soluzione :grin:

[binarysun]

Be allora prova
SELECT * FROM messages,board WHERE boardid=board.id GROUP BY boardid ORDER BY timestamp DESC LIMIT 0,10;

[heracleum]

No niente.
Comunque anche provando le query più semplici (come la prima proposta da Bynarysun) il problema me lo ritrovo sempre sul GROUP BY:
raggruppando, mi ritorna comunque i primi messaggi (forse con message.id inferiore??? ma che c'entra?) che hanno per primi un nuovo boardid.
E per assurdo mi invalida completamente l'ORDER BY se ne frega altamente??!?!?!?
Dunque senza il MAX( ) è inutile.

a questo punto anch'io dico la mia :grin:

non vorrei che l'incasinamento sul GROUP fosse dovuto all'utilizzo di *, quindi propongo una selezione specifica di campi. Poi ho messo anche un alias "max" per l'ordinamento, ma non so se sia del tutto necessario:

SELECT board.titolo, MAX(message.timestamp) AS max FROM message,board WHERE message.boardid=board.id GROUP BY board.titolo ORDER BY max DESC LIMIT 0,10;

[heracleum]

No no, ho capito l'inghippo,
o meglio mi sono fatto un'idea abbastanza precisa (poi se qualcuno ha la chiave meglio ancora),
facciamo tutti lo stesso errore, o meglio l'"ingenuità" di pensare che, se nella SELECT oltre al MAX() aggiungo * o i campi che mi interessano, il record che risulta avrà tutti i campi direttamente associati al campo (o espressione) su cui ho fatto MAX(). Ebbene no.

Dunque la soluzione (o meglio il "rimedio") l'ho trovata qui, manuale ufficiale:
http://dev.mysql.com/doc/mysql/en/ex...group-row.html

ancor più utili i commenti degli utenti in basso, che addirittura correggono qualche erroruzzo.

In breve, per chi non sguazza nell'inglese (o per i più pigri :grin:):
fondamentalmente è uno degli esempi sulle query più comuni ma sulle quali si può avere difficoltà (e infatti!) e pone un'esigenza:
"Per ciascun articolo trovare il o i venditore col prezzo più elevato"
Si basa su una tabella sola comunque chiarisce ugualmente sulle possibilità:
1) Il metodo più ottimizzato è usare una SUB-SELECT, una select dentro la select. Peccato che si può fare solo dalla versione MySQL 4.1 in poi, mentre qui su AV abbiamo la 3.23 o qualcosa di simile, comunque non se po' fa!
2) Come metodo alternativo propone uno scriptone sql allucinogeno, di almeno una decina di righe con creazione di tabella temporanea... oOOOOH? ma scherziamo?

3) Come metodo "di scorta", per chi deve usare una query sola come noi, c'è una "pecionata terrificante", anche loro si vergognano di proporla... ma per assurdo è l'unica mia salvezza, l'ho adottata e funziona! (e te credo)

Una porcata assurda, che ha fa rabbrividire quasi tutti quelli che commentano (proponendo altre soluzioni che non mi aiutano perché usano una SELF-JOIN, la join sulla tabella stessa).. però funziona una favola.
Dunque viene notoriamente definito "MAX-CONCAT TRICK" (=trucchetto del max-concat) insomma la classica pezza. Consiste nel crearsi (col concat) un unico stringone tutto dentro al MAX(), con tutti i campi che ci interessano, sistemati e formattati ovviamente con riempimento di spazi (LPAD) ciascuno occupando un tot di caratteri. Capito come? guardate bene la query della terza soluzione, altrimenti non si capisce nulla a parole...

Nel nostro esempio invece sarà più o meno così (concatenando simbolicamente solo un altro paio di campi altrimenti è un marasma):

SELECT count(ms.pid) as nmessaggi,
max( concat( concat(ms.time, lpad(ms.testo,100,' ')), lpad(ms.bid,10,' ')) ) as ccdata
FROM  board bd, bl_msg ms WHERE bd.id = ms.bid
GROUP BY bd.id ORDER BY ms.time DESC LIMIT 10

Dunque ho ridotto i nomi da timestamp a time, da boardid a bid, in più visto che ci sono mi faccio ritornare il numero di messaggi totale (count()) per ogni board.
Come vedrete siamo eventualmente costretti a "troncare" dei campi con lunghezza maggiore (per es. prendo solo i primi 100 caratteri del messaggio, o se si vuole si può anche esagerare).

Diciamo che la "pezza" tutto sommato non mi viene affatto scomoda, però è una vergogna ricorrere a questi mezzucci ancora nella versione 3, e che ca...
Poi se AV migra alla 4.x meglio ancora!! :grin:

[makpaolo]

heracleum ottima spiegazione come sempre, piccolo appunto .. su AV c'è il PHP ver 4.3.4
( o almeno su server 6 )

[heracleum]

Ah sì??? ma dove l'hai vista?
io ho:
1) controllato su phpinfo() e alla voce mysql l'unica versione che vedo è:
Client API version 3.23.49
ma ora che mi ci fai pensare suppongo sia la versione delle mysql_api di PHP ..oops..
Dove si controlla la versione di mysql?

2) il problema è che prima di arrendermi ho provato di persona la prima possibilità citata in quell'esempio, ovvero la sub-select e mi da un bieco errore. Oltretutto, nei vari tentativi disperati, avevo anche provato un attibuto strano (GROUP BY ... "WITH ROLLUP") che figurava se non ricordo male nella versione 4.x e mi phpmyadmin mi informava che nella mia versione non era disponibile...

any idea? :eyes:

[heracleum]

Ho controllato la versione tramite:
echo mysql_get_server_info();

e mi ritorna:
4.0.20-standard-log

Dunque è ovvio che non mi funziona la sub-select visto che richiede la 4.1.x
...
sto su server 5.

eh non vale! :grin: ecco!
come mai voi avete la 4.3.4?
Scandalo! Protestooo

(e fu così che si incammino' verso il tristo viale che conduce al secondo ban :grin: )

[binarysun]

A questo punto, se sei disperato, usa le query nidificate!

[makpaolo]

sorry heracleum .. ho letto frasche per fiaschi ....

uhm..... io avrei fatto in questo modo:


"message" con i campi: | id | testo | timestamp | boardid |

"board" con i campi: | id | titolo | descriz |

allora partendo dal linguaggio parlato, direi che bisogna:
selezionare i record di message con timestamp maggiore per ogni argomento; in sql
quindi:

SELECT id,testo,max(timestamp),boardid FROM message GROUP BY boardid

a questo punto, devo per ogni id selezionato, andarmi a prendere i rispettivi record
della tabella board, allora modifico la SELECT in questo modo:

SELECT a.id,a.testo,a.max(timestamp),a.boardid, b.*
FROM message as a, board as b
WHERE a.boardid=b.id
GROUP BY a.boardid

penso che così dovrebbe andare......

[heracleum]

mak:
ah vabbè ok... meglio così eheh

binary:
per "sub-select" (o sub-query) intendevo proprio le query nidificate :grin:
sempre se non intendi invece farsi a mano, nel codice php, più query separate...

Non so se hai letto il mio post sopra, quello bello lungo:
ho comunque utilizzato una tecnica (la classica pezza) consigliata dal manuale ufficiale per chi non può usare le sub-select e almeno ho risolto in una query sola, certo non è il massimo dell'eleganza, però funziona...
Dunque almeno una soluzione c'è.

Jole:
No è lo stesso identico errore che abbiamo fatto tutti nelle query-tentativo che puoi leggere sopra:
ovvero i campi indicati nella SELECT non sono allineati con ciò che ritorna il MAX().

Se vuoi fai una prova, nel record vedi il valore di max corretto ma gli altri dati non c'entrano una mazza con la row della data maggiore, vengono ordinati per creazione mentre la data con MAX è ordinata come previsto.

Giusto, hai ragione, non ci avevo pensato.....

[binarysun]

Con query nidificate io intendo le query nelle query, quelle dove c'è la condizione
SELECT nomi FROM tabella WHERE id IN (altra query)

[heracleum]

Jole:
infatti è più che naturale fare così come hai pensato tu (come tutti noi), perché più che altro sarebbe molto utile.. e invece no

binary:
sì esatto, proprio le sub-query o sub-select che intendevo io.
Sarebbe mooolto comodo usarle...
e invece non si possono fare perché su AV abbiamo una versione di poco inferiore alla 4.1.x che appunto ne permette l'uso (come scritto nei post sopra).

Molto istruttiva sta discussione :D
Grazie x lo spunto Heracleum :P

Bye.

[heracleum]

Ah sì, come spiegavo nel primo post (dove chiedevo aiuto) di solito questioni di questo genere cercavo di aggirarle con più query "sparse"... almeno stavolta ho voluto approfondire un po' di più, si perde del tempo ma di solito è conveniente
Non puoi capire, spulciando in giro, quanto ho aggiunto rispetto alla mia scarsa conoscenza SQL.

Comunque la situazione della message board era solo un semplice esempio, in realtà sto sviluppando qualcosa di ben più complesso *aria misteriosa* :grin: