CODEWITHSUNDEEP
mysql
Pr0no
Pr0no

Reputation: 4099

Count number of messages per user

Consider the following tables:

users                messages
-----------------    -----------------------
user_id  messages    msg_id user_id content
-----------------    -----------------------
1        0           1      1       foo
2        0           2      1       bar
3        0           3      1       foobar
                     4      3       baz
                     5      3       bar

I want to count the number of messages per user and insert the outcome into users.messages, like this:

users                
-----------------    
user_id  messages    
-----------------    
1        3           
2        0           
3        2

I could use PHP to perform this operation, pseudo:

foreach ($user_id in users) {
  $count = select count(msg_id) from messages where user_id = $user_id
  update users set messages = $count
}

But this is probably very inefficient as compared to one query executed in MySQL directly:

UPDATE users SET messages = (
  SELECT COUNT(msg_id) FROM messages
)

But I'm sure this is not a proper query. Therefore, any help would be appreciated :-)

Upvotes: 2

Views: 1667

Answers (6)

Zagor23
Zagor23

Reputation: 1963

Here's the answer:

UPDATE `users` AS u 
SET u.messages=(SELECT COUNT(*) FROM messages WHERE user_id=u.user_id)

and the test:

http://sqlfiddle.com/#!2/2768b/1

I just hope these are not huge tables or this could take a while...

UPDATE: Removed unnecessary JOIN from the query Thanks to @Ariel and @Zane Bien for correcting my answer!

Upvotes: 3

Phani Rahul
Phani Rahul

Reputation: 860

This one would surely work. Try this:

UPDATE users u
 SET u.messages =
      (  SELECT COUNT (*)
           FROM messages m
       GROUP BY user_id
         HAVING u.user_id = m.user_id);

Upvotes: 1

Zane Bien
Zane Bien

Reputation: 23125

MySQL permits you to use JOIN operations in UPDATE statement like so:

UPDATE 
    users a
LEFT JOIN
(
    SELECT user_id, COUNT(1) AS messagecount
    FROM messages
    GROUP BY user_id
) b ON a.user_id = b.user_id
SET 
    a.messages = COALESCE(b.messagecount, 0)

What this will do is line up each corresponding user with the count of his/her messages in the messages table, and so updating the messages column becomes a simple matter of referencing the corresponding joined column containing the message count.

If a user does not have any messages in the messages table, it will simply set the column to 0.

You can also be sure that this will execute very quickly as there is no need to use inefficient subqueries(which end up executing on each iteration), and also the fact that the join will be performed over indexes.

Upvotes: 1

LoneWOLFs
LoneWOLFs

Reputation: 2306

Assuming you know how to fire the query i'd say this ...

Create a trigger on insert whenever an message is posted into the messages table and insert the number of rows into the users table. Thus you don't have to fire a query by php each time you insert a new message. Thus your no of messages will be updated automatically whenver you insert a new message and you just have to fire a select query when you want to check the number of messages for a particular user.

Trigger documentation

Upvotes: 1

Ariel
Ariel

Reputation: 26753

UPDATE users JOIN
       (SELECT user_id, COUNT(msg_id) num FROM messages group by user_id) c
       USING (user_id)
SET users.messages = c.num

Note: I didn't test this.

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

UPDATE users u
inner join 
(
  Select user_id, count(1) as number
    from users u inner join messages m on (u.user_id=m.user_id) group by m.user_id 
) as temp_message tm on (u.user_id=tm.user_id)
set u.messages=tm.number

Upvotes: 0

Related Questions