query mooolto complessa con mysql

[tarini]

salve ragazzi... cerco di spiegarvi il mio problema che vorrei risolvere in un unica query

le tabelle coinvolte sono 4:

tabella USER
id       nome
1        pippo
2        pluto

tabella TOPIC
id       forum_id     titolo
1        1               aiuto in mysql
2        1               le stringhe in php

tabella POST
id       topic_id      data             contenuto
1        1               01-gen-07     bla bla bla
2        1               01-gen-07     bla bla bla
3        2               02-gen-07     dasdasdasd
4        2               03-gen-07     adsdasdasd

tabella TOPIC_READ (rappresenta l'ultima volta che l'utente ha letto il topic)
topic_id      user_id     data
1               1             01-gen-07

io vorrei con una query estrarre oltre alle informazioni relative al topic (titolo) anche la data di ultima lettura (o null se non esiste) e la massima data relativa ai post collegati al topic corrente; ovviamente tutto questo deve essere fatto in relazione all'utente corrente

io sono a questo punto:

$user_id = utente loggato al forum
$forum_id = id del forum


SELECT t.*, MAX(p.data) AS data_ultimo_post, tr.data AS data_ultima_lettura
FROM topic AS t 
LEFT JOIN post AS p ON t.id = p.topic_id
LEFT JOIN topic_read AS tr ON t.id = tr.topic_id
WHERE t.forum_id = $forum_id 
AND tr.id_user = $user_id
GROUP BY t.id 
ORDER BY MAX(p.d) DESC

il problema cosi facendo è che non mi riporta i topic che non sono stati ancora relazionati con l'utente tramite la table topic_read

ecco cosa mi aspetto di ricevere:

$forum_id = 1; $user_id = 1

topic.id    topic.titolo              titolo.id_forum     data_ultimo_post    data_ultima_lettura
1            aiuto in mysql         1                        01-gen-07             01-gen-07
2            le stringhe in php     1                       03-gen-07              null

mentre con:
$forum_id = 1; $user_id = 2

topic.id    topic.titolo              titolo.id_forum     data_ultimo_post    data_ultima_lettura
1            aiuto in mysql         1                        01-gen-07             null
2            le stringhe in php     1                       03-gen-07              null

[bubu77]

prova a sostituire questo
AND tr.id_user = $user_id

con
AND (tr.id_user = $user_id || tr.id_user IS NULL)

[tarini]

ci avevo già pensato, ma cosi facendo:

- per l'utente 1 tutto funziona regolarmente

UTENTE 1

topic.id    topic.titolo              topic.id_forum     data_ultimo_post    data_ultima_lettura
1            aiuto in mysql         1                        01-gen-07             01-gen-07
2            le stringhe in php     1                       03-gen-07              null

- per l'utente 2 viene visualizzato solamente il topic non letto dal primo (in quanto facendo la join, la tabella "completa" contiene come user_id i valori 1 e NULL)

UTENTE 2

topic.id    topic.titolo              topic.id_forum     data_ultimo_post    data_ultima_lettura
2            le stringhe in php     1                       03-gen-07              null

bisognerebbe in qualche modo fare la join mettendo nella colonna user_id null al posto dell'id di un altro utente

grazie cmq per la risposta

[gianf_tarantino]

Il problema è ke nella clausola WHERE tu hai:

WHERE t.forum_id = $forum_id AND tr.id_user = $user_id

quell'AND tr.id_user = $user_id è una condizione che riguarda la tabella che si trova a destra del left join e, qundi, tale condizione fa cadere il left join trasformandolo in un join normale. Infatti se provi a togliere AND tr.id_user funziona e da il risultato che tu vuoi:

topic.id topic.titolo titolo.id_forum data_ultimo_post data_ultima_lettura
1 aiuto in mysql 1 01-gen-07 null
2 le stringhe in php 1 03-gen-07 null

OK. Ora, se provi ad inserire nella select anke l'id user:

SELECT t.*, MAX(p.data) AS data_ultimo_post, tr.id_user, tr.data AS data_ultima_lettura
FROM topic AS t
LEFT JOIN post AS p ON t.id = p.topic_id
LEFT JOIN topic_read AS tr ON t.id = tr.topic_id
WHERE t.forum_id = $forum_id
GROUP BY t.id
ORDER BY MAX(p.d) DESC

ti viene:

topic.id topic.titolo titolo.id_forum data_ultimo_post id_user data_ultima_lettura
1 aiuto in mysql 1 01-gen-07 1 null
2 le stringhe in php 1 03-gen-07 null null


Questo perché nel left join le righe della tabella di destra che non trovano corrispondenza vengono tutte restituite con il valore null, quindi anke id_user, e se tu imponi che id_user=1 allora quella riga non viene considerata: ecco perché cade il left join.

[tarini]

Originariamente inviato da gianf_tarantino
OK. Ora, se provi ad inserire nella select anke l'id user:

.....

Questo perché nel left join le righe della tabella di destra che non trovano corrispondenza vengono tutte restituite con il valore null, quindi anke id_user, e se tu imponi che id_user=1 allora quella riga non viene considerata: ecco perché cade il left join.

Hai perfettamente ragione su tutto, peccato che la mia necessità è quella di avere risultati diversi in base all'utente loggato al forum...

Con il tuo metodo, come faccio a discriminare??
Si comporta infatti come se tutti gli utenti avessero letto allo stesso momento i topic


grazie anche a te per la risposta

[gianf_tarantino]

Hai ragione, ci avevo pensato pure io e penso ke forse così potrebbe andare bene:

SELECT t.*, MAX(p.data) AS data_ultimo_post, tr.id_user, tr.data AS data_ultima_lettura
FROM topic AS t
LEFT JOIN post AS p ON t.id = p.topic_id
LEFT JOIN (SELECT * FROM topic_read WHERE id_user=1) AS tr ON t.id = tr.topic_id
WHERE t.forum_id = 1
GROUP BY t.id
ORDER BY MAX(p.id) DESC

cioé usare una subquery nella FROM in cui non ti prende tutte le righe di topic_read ma solo quelle del tuo utente.

[tarini]

forse questa è la soluzione!!

ma sei sicuro sia supportata senza problemi da mysql??

mica le query annidiate fino non venivano eseguite??

se me lo sono sognato dillo pure :P

[gianf_tarantino]

Io uso MySQL 5.0.27 e ho creato un db di prova con le tue tabelle l'ho provato con i dati che ti ho postato e funziona.
Per questo avevo lasciato WHERE id_user=1 e WHERE t.forum_id = 1

perché la query l'ho copiata da MySQL quando ho visto ke funzionava.

[tarini]

sulla mia macchina funziona tutto perfettamente... grandissimo!

e ho la 5.0.3

[Andrea1979]

Originariamente inviato da tarini
forse questa è la soluzione!!

ma sei sicuro sia supportata senza problemi da mysql??

mica le query annidiate fino non venivano eseguite??

se me lo sono sognato dillo pure :P

Mi pare che dalla 4 (qualcosa) in poi si possano utilizzare query annidate in mysql. Sicuramente sulla 5 ci sono.

Edit:
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html