doubledee — 2013-12-28T19:56:54-05:00 — #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,
GROUP_CONCAT(m_to.username ORDER BY m_to.username SEPARATOR ', '),
'...') 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
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?
r937 — 2013-12-28T21:51:25-05:00 — #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)
doubledee — 2013-12-28T22:13:07-05:00 — #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,
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? :-/