CODEWITHSUNDEEP
phpmysql
Freddy
Freddy

Reputation: 867

Checking multiple rows in table and counting them as 1 if rows are matching

I have a table called private_messages with the following structure:

id     message_from     message_to

I am trying to determine the number of active chat rooms, but the table stores all messages sent and received, therefore there may be many rows where Alice is in message_from and in message_to.

Assume my private_messages table has 4 rows:

id   message_from    message_to
1    Alice           conor
2    Alice           conor
3    connor          Alice
4    Anderson        conor

The conversation between Alice and conor should be considered as 1. Therefore when I echo, let's say $active_conversations, the number 2 should be printed.

I am assuming this involved an array? i.e. $conversations = [];. But I do not know or understand what to do beyond that.

Upvotes: 0

Views: 31

Answers (3)

spencer7593
spencer7593

Reputation: 108450

One of the issues is that the tuples ('connor','Alice') and ('Alice','connor') in `private_messages` represent the same conversation/room whatever it is you are counting.

One approach would be get a total count of the distinct values, and divide by two, but that doesn't really seem like the right way to do it. (We can setup some edge cases where the result we get back isn't an integer, or is less than the actual number of active rooms.... e.g

id   message_from    message_to
--   ------------    ----------
1    Alice           conor
2    Alice           conor
3    conor           Alice
4    Anderson        conor
5    Cooper          Alice
6    Dalton          Allman

Another approach would be to reorder the values in the tuples (swapping message_from and message_to) on some rows that they match message_from and message_two on the other side of the conversation. For example, we could compare message_from and message_to, and swap them if message_to is "lower" than message_from.

As an illustration, using an expression to return values as displayed in the m_one and m_two columns.

id   message_from    message_to      m_one       t_two
--   ------------    ----------      -----       -----
1    Alice           conor           Alice       conor
2    Alice           conor           Alice       conor
3    conor           Alice           Alice       conor
4    Anderson        conor           Anderson    conor
5    Cooper          Alice           Alice       Cooper
6    Allman          Dalton          Allman      Dalton

And then we could count the distinct values of the (m_one,m_two) tuples.

A third approach would be to just discard any tuples where the message_from value is higher than the message_to, get rid of one side of the conversation/room. And then get a count of the distinct tuples that remain. But again, this would potentially exclude some conversations/rooms that had messages in only one direction.

id   message_from    message_to     m_one       t_two
--   ------------    ----------     -----       -----
1    Alice           conor          Alice       conor
2    Alice           conor          Alice       conor
3    conor           Alice          Alice       conor
4    Anderson        conor          Anderson    conor
5    Cooper          Alice          (NULL)      (NULL)
6    Allman          Dalton         Allman      Dalton

(We'll set aside a discussion of the the corner case of a conversation/room where message_from is equal to message_to.)

The SQL we write really depends on what algorithm we want to implement.

We could use a GROUP BY to collapse "matching" rows, and get a count. Or we could use a COUNT(DISTINCT foo) to get a count of distinct values.

If we choose an algorithm that needs to "swap" values, we could use expressions that make use of the LEAST and GREATEST functions. Or MySQL IF() function, or CASE expressions.

If we want to exclude rows from being counted, we can include a predicate in the WHERE clause.

All of those are possible, and there are multiple ways to achieve the specified result.

Upvotes: 0

Tin Tran
Tin Tran

Reputation: 6202

If you just want to get a count of conversations, you can use something like this

SELECT COUNT(DISTINCT
             CONCAT(
                    LEAST(message_from,message_to),
                    '->',
                    GREATEST(message_from,message_to)
                    )
             ) AS active_conversations
FROM private_messages;

sqlfiddle

output:

active_conversations
2

If you want to get details of the conversations you can use this 

SELECT DISTINCT
       LEAST(message_from,message_to) as FirstPerson,
       GREATEST(message_from,message_to) as SecondPerson
FROM private_messages;

sqlfiddle

output:

FirstPerson     SecondPerson
Alice           conor
Anderson        conor

Upvotes: 0

tapos ghosh
tapos ghosh

Reputation: 2202

select count(id) from private_messages group by message_from,message_to

then you get appropriate result

Upvotes: 0

Related Questions