Allora, ho provato ma mi da dei risultati farlocchi.
Mi spiego: se applico la query base (quella suddetta), sostituendo al parametro PDO :user_id un valore qualsiasi (2, in questo caso), i risultati vengono esposti in modo corretto. Per dettagliare:
SELECT
chat2.chatId,
chat2.chatDate,
chat2.chatText,
(cm.chat_read) AS chatRead,
chat2.parentChat_id AS chatRif,
cm.sender,
cm.chat_id as chatId_cm,
users.userName,
users.ranking,
(SELECT GROUP_CONCAT(users.userName)
FROM chat_members
INNER JOIN users ON chat_members.user_Id = users.userId
WHERE chat_members.chat_id = chat2.chatId
AND sender = 0) AS addressess
FROM chat2
INNER JOIN users ON chat2.user_Id = users.userId
INNER JOIN chat_members AS cm ON chat2.chatId = cm.chat_id
WHERE cm.user_Id = 2
AND cm.chat_deleted = 0
AND cm.chat_id NOT IN (
SELECT chatId FROM chat_archive
WHERE userId = 2
)
ORDER BY chatRif DESC, chat2.chatDate DESC
mi restituisce:
ma il mio tentativo con una derived table, questa:
SELECT chatId, COUNT(chatRead) AS totale
FROM
(SELECT
chat2.chatId,
chat2.chatDate,
chat2.chatText,
(cm.chat_read) AS chatRead,
chat2.parentChat_id AS chatRif,
cm.sender,
cm.chat_id as chatId_cm,
users.userName,
users.ranking,
(SELECT GROUP_CONCAT(users.userName)
FROM chat_members
INNER JOIN users ON chat_members.user_Id = users.userId
WHERE chat_members.chat_id = chat2.chatId
AND sender = 0) AS addressess
FROM chat2
INNER JOIN users ON chat2.user_Id = users.userId
INNER JOIN chat_members AS cm ON chat2.chatId = cm.chat_id
WHERE cm.user_Id = 2
AND cm.chat_deleted = 0
AND cm.chat_id NOT IN (
SELECT chatId FROM chat_archive
WHERE userId = 2
)
ORDER BY chatRif DESC, chat2.chatDate DESC) AS Q1
Restituisce:
E il risultato è 2 volte sbagliato, perché non esegue come dovrebbe l'iterazione tra le righe di Q1, e somma 5 (che è il numero delle righe di Q1).
Iniziare la query con il riferimento a cm (che è contenuto in Q1), in questo modo:
SELECT chatId, COUNT(cm.chat_read) AS totale
genera l'errore “unknown column 'cm.chat_read' in 'field list'”.
Certamente sbaglio a impostare le istruzioni. Portate pazienza…