Table structure for conversation system

[asantos]

Hi,
i'm implementing a message system (for an intranet) and i would like to use a conversation structure (such as gmail's). That means that if i get 4 new messages, but 3 of them come from the same user, i should only have 2 notifications on my inbox.

This is current table structure:

`id_msg` int(11) NOT NULL auto_increment,
`id_user_from` int(11) NOT NULL,
`id_user_to` int(11) NOT NULL,
`date` int(14) NOT NULL,
`msg` mediumtext NOT NULL,

Lets suppose that i get 1 message from user BBB and 3 messages from user CCC. I should only have 2 records in my inbox:
1) BBB (1)
2) CCC (3)

This is my sql sentence to generate that data:

SELECT * FROM msg WHERE id_user_to=1 GROUP BY id_user_from

Any ideas on how to improve this implementation? Thanks!

[phranque]

SELECT DISTINCT(id_user_from), COUNT(id_user_from) FROM msg WHERE id_user_to=1 GROUP BY id_user_from

[asantos]

isnt DISTINCT(id_user_from) and GROUP BY id_user_from practically the same?

[ZydoSEO]

Yes. In this case DISTINCT(id_user_from) is redundant since the GROUP BY id_user_from causes the result set to contain one row per distinct id_user_from value.

But most DBs don't allow what you have in the original select:

>>>SELECT * FROM msg WHERE id_user_to=1 GROUP BY id_user_from

Typically everything in the select list has to be 1) also in the GROUP BY or 2) be some type of agregate function - COUNT(), SUM(), AVG(), etc. So to reduce the previous poster's solution slightly:

SELECT id_user_from, COUNT(*)
FROM msg
WHERE id_user_to=1
GROUP BY id_user_from

[asantos]

Hi, i aplied ZydoSEO's solution yesterday, and a few minutes ago i found out a problem.

The sql query should return me the LAST record of each group of messages from the same user. So i did this:

SELECT * FROM msg WHERE id_user_to=1 GROUP BY id_user_from ORDER BY date DESC

This are my current records:

1. Jane (id2) -> Andres (id1) / Nov 15
2. Mary (id3) -> Andres (id1) / Nov 16
3. Jane (id2) -> Andres (id1) / Nov 17

What Andres (id1) should have in his inbox are records 3 and 2, but instead he is getting 2 and 1.

How can i show the LAST record from GROUP BY values?

[phranque]

i don't precisely understand your problem description but you could probably use MAX [dev.mysql.com] to solve it.

[asantos]

phranque,

this is my table structure and current data:

CREATE TABLE `msg` (
`id_msg` int(11) NOT NULL auto_increment,
`id_user_from` int(11) default NULL,
`id_user_to` int(11) default NULL,
`date` int(14) NOT NULL,
`msg` mediumtext NOT NULL,
`delete_sender` tinyint(4) default '0',
`delete_receiver` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id_msg`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

INSERT INTO `msg` VALUES (1, 1, 2, 1191396067, 'Hi Joe, this is my first message', 0, 0);
INSERT INTO `msg` VALUES (2, 2, 1, 1201396967, 'Hi Andres, nice to hear from you!', 0, 0);
INSERT INTO `msg` VALUES (3, 1, 2, 1211399967, 'Really?', 0, 0);
INSERT INTO `msg` VALUES (4, 3, 1, 1221399967, 'Im a new user! Hi Andres!', 0, 0);
INSERT INTO `msg` VALUES (5, 2, 1, 1231399967, 'Yes, ill call you this weekend!', 0, 0);

This is the SQL query:
SELECT id_msg,id_user_from,date,msg FROM msg WHERE (id_user_to=1 AND delete_receiver=0) GROUP BY id_user_from ORDER BY date DESC LIMIT 0,10

I am id_user 1 and I should get the last messages from all users that have written to me. Since 2 users have written me in the past, i should get only 2 rows.

These are the messages i should get:
5. Yes, ill call you this weekend
4. Im a new user! Hi Andres!

...but instead, im receiving these:
2. Hi Andres, nice to hear from you!
4. Im a new user! Hi Andres!

It seems that any ORDER BY or MAX() approach, only applies to the results AFTER they have been GROUPED BY some field.

What could i be doing wrong?

ps: delete_sender/receiver field is just a flag for the users to hide unwanted messages.

[asantos]

Solved by a friend in the office:

select msg from (SELECT id_msg,id_user_from,date,msg FROM msg WHERE (id_user_to=1 AND delete_receiver=0) ORDER BY date DESC LIMIT 0,10) as R1 group by R1.id_user_from