Adding Ellipsis to GROUP_CONCAT

Databases
1

I have a “pm_recipient” table which contains a separate record for each Recipient of a Message.


id	pm_id	recipient_id	created_on
---	------	-------------	-----------
104	12	25		2013-12-28
105	12	39		2013-12-28
106	12	72		2013-12-28
107	12	16		2013-12-28

It is then joined with a “private_msg” and “member” table to yield a recordset used to display “Sent Messages”.

If a Message is sent to 4 Recipients, I need to roll those 4 records up into one record, because only one Message was sent.

I did this by using the following magic in SQL…


SELECT pm.id, pm.sender_flag,
	CONCAT(
		SUBSTRING(
			GROUP_CONCAT(m_to.username ORDER BY m_to.username SEPARATOR ', '),
			 1, 40),
		'...') AS recipients,

The snippet above groups the multiple Recipient records into one record, plus concatenates the Recipients into one field, and truncates anything longer than 40 characters.

So in your “Sent Folder” view, you might see…


To		Subject			Date
---		--------		-----
username1,	Re: Your Voice-mail	2013-12-28 12:25:18
username2,
username3...

On to my question…

After testing my query and code, I noticed that my SQL is just concatenating the Recipients up to 40 characters and then slapping an ellipsis on the end.

This is not exactly what I wanted.

Is there a way - in my SQL - to make it so that IF the concatenated Recipients exceed 40 characters, THEN I add an ellipsis on the end, ELSE I do not??

(An Ellipsis implies continuation. So if there is only one Recipient, then having an Ellipsis on the end is misleading!!)

Can this be done, and if so, how would I modify my SQL above?

Sincerely,

Debbie

2

CASE WHEN LENGTH(foo) > 40 THEN CONCAT(LEFT(foo,40),‘…’) ELSE foo END AS recipients

you realize that this will more than likely cut a name off in the middle, right?

this type of fine-tuning of query results should actually not be done in the sql, but rather, by your application language (php or whatever)

3

This is what I came up on my own…


SELECT pm.id, pm.sender_flag,
	IF(CHAR_LENGTH(GROUP_CONCAT(m_to.username ORDER BY r.blind_copy ASC, m_to.username SEPARATOR ', ')) < '40',
			GROUP_CONCAT(m_to.username ORDER BY r.blind_copy ASC, m_to.username SEPARATOR ', '),
			CONCAT(SUBSTRING(GROUP_CONCAT(m_to.username ORDER BY r.blind_copy ASC, m_to.username SEPARATOR ', ') , 1, 40), '...')) AS recipients,
	pm.subject, pm.created_on
	FROM private_msg_recipient AS r

Is there a way to replace this long monster in the IF statement…


GROUP_CONCAT(m_to.username ORDER BY r.blind_copy ASC, m_to.username SEPARATOR ', ')

I guess so. Never really considered it.

Since this is in the “To:” column in a person’s Sent Folder, I don’t know that it really matters.

My goal is just to show a snippet of the “To:” list.

(If the Sender wants to see all of the details, they can double-click on the Message, and be taken to “View Message” and then they can see the entire distribution list.)

Valid point, though!!

Why do you say that?

And even if that is the case, is it a “mortal sin” in a case like this? :-/

Sincerely,

Debbie