SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,528
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Adding Ellipsis to GROUP_CONCAT

    I have a "pm_recipient" table which contains a separate record for each Recipient of a Message.
    Code:
    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...

    Code MySQL:
    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...
    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,528
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    CASE WHEN LENGTH(foo) > 40 THEN CONCAT(LEFT(foo,40),'...') ELSE foo END AS recipients
    This is what I came up on my own...

    Code MySQL:
    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...
    Code:
    GROUP_CONCAT(m_to.username ORDER BY r.blind_copy ASC, m_to.username SEPARATOR ', ')


    Quote Originally Posted by r937 View Post
    you realize that this will more than likely cut a name off in the middle, right?
    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!!


    Quote Originally Posted by r937 View Post
    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)
    Why do you say that?

    And even if that is the case, is it a "mortal sin" in a case like this?

    Sincerely,


    Debbie


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •