JOIN - 选择不同于COUNT的条件的列/详细信息

Question

I have a chat system. There are 3 tables:

data_chats - holds the IDs of the chats themselves. This is where you mark a chat as deleted.

data_chat_parties - holds the member or team ID that is included in chat along with chat permissions, who they were invited by, etc

data_chat_messages - holds the actual messages of chats

With my query, I am trying to fetch the info from data_chat_parties related to the party requesting this information (ie currently logged in user), but also get the total number of chat parties in the chat.

  SELECT
    data_chats.id AS chat,
    data_chats_parties.*,
    COUNT(data_chats_parties.id) AS total_parties,
    data_chats_messages.created AS last_message_created,
    data_chats_messages.author AS last_message_author,
    data_chats_messages.author_type AS last_message_author_type,
    data_chats_messages.message AS last_message
  FROM data_chats
   LEFT JOIN data_chats_parties ON data_chats_parties.chat=data_chats.id
   LEFT JOIN data_chats_messages ON data_chats_messages.chat=data_chats.id AND data_chats_messages.active=1
  WHERE
    data_chats.active=1 AND
    data_chats_parties.member=1 AND
    data_chats_parties.status >= 1
  GROUP BY data_chats_parties.chat
  ORDER BY last_message_created DESC

This all works fine, except that total_chat_parties always returns 1, presumably because it's only matching the record of data_chats_parties.member=1. How would I fetch the party record specific to this user but at the same time, fetch the total number of parties for this chat?

Answer 1

You should use a correlated query :

  SELECT data_chats.id AS chat,
         (SELECT COUNT(data_chats_parties.id) FROM data_chats_parties
          WHERE data_chats_parties.chat = data_chats.id) AS total_parties,
         data_chats_messages.created AS last_message_created,
         data_chats_messages.author AS last_message_author,
         data_chats_messages.author_type AS last_message_author_type,
         data_chats_messages.message AS last_message
    FROM data_chats
    LEFT JOIN data_chats_messages
      ON data_chats_messages.chat = data_chats.id
     AND data_chats_messages.active = 1
     AND data_chats_parties.member = 1
     AND data_chats_parties.status >= 1
   WHERE data_chats.active = 1
   ORDER BY last_message_created DESC

Another thing is the conditions on the WHERE clause, you can filter the RIGHT table of a LEFT JOIN in the WHERE clause, those condition should only be specified in the ON clause.

You also group by a column from the RIGHT table - this is not suggested at all! Either use an inner join, or group by another field.