mySQL >> Adding HREF links to a Distinct GROUP_CONCAT

Question

I have the following query:

SELECT *, RES.res_id 'ID', RES.RES_Title 'Title', 
GROUP_CONCAT(DISTINCT T.T_Name separator ', ') 'Topics', 
GROUP_CONCAT(DISTINCT CH.CH_Code separator ', ') 'Chapters' 
FROM Resources RES 
LEFT JOIN topic_to_resource RT ON RT.RT_ResourceID = RES.RES_ID 
LEFT JOIN topics T on T.T_ID = RT.RT_TopicID 
LEFT JOIN topic_to_chapter TCH on TCH.TCH_TopicID = T.T_ID 
LEFT JOIN chapters CH ON CH.CH_ID = TCH.TCH_FrameworkID 
WHERE RES_Status = 'Active' 
GROUP BY RES.RES_ID 
ORDER BY RES_Title ASC, RES_Source DESC LIMIT 0, 10

For each of the GROUP_CONCATs (Topics and Chapters), I need to convert the list into links while keeping the Distinct.

For example, instead of the Topics output of the query above: Sports, Current Events, Politics, Climate, etc.

I need to generate:

<a href="page.asp?topic=Sports" title="Sports">Sports</a>, 
<a href="page.asp?topic=Current%20Events" title="Current Events">Current Events</a>, 
<a href="page.asp?topic=Politics" title="Politics">Politics</a>, 
<a href="page.asp?topic=Climate" title="Climate">Climate</a>

I can achieve the links by nesting a CONCAT within the GROUP_CONCAT, but then I lose the Distinct. Also, I need the query to stay grouped by the Resource ID (RES.RES_ID).

Any ideas on how to accomplish a linked list of distinct topics and chapters?

Many thanks in advance!

Hi. Again, it is extremely important that you read and act on [mcve]. That includes DDL and example input data & desired output. Also, we don't know what "list into links while keeping the Distinct" means--it's just a bunch of words that are vaguely sloppily descriptive if one already knows what you want, but doesn't communicate it. Show us your "already". What's the code have to do with anything? I am just repeating my comment from your last question. Please act on it. Yes, it's hard to be clear & precise. So work on it. (I already told you to finish certain sentences. Do that.) — philipxy, Oct 08 '17 at 01:45
Possible duplicate of [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/q/45250646/3404097). Read the definition of left join. It returns what inner join does plus unmatched left rows extended by nulls. You want an inner join of left joins. — philipxy, Oct 08 '17 at 01:57
@philipxy, thanks again. I just added an example of what is currently generated and what I need to generate. I will also prepare and share a SQL Fiddle. — nbardach, Oct 08 '17 at 01:58
@philipxy, I beg to differ that this is a duplicate of the post you cited. My question here is about how to maintain "Distinct" within a CONCAT nested in a GROUP_CONCAT. — nbardach, Oct 08 '17 at 02:01
Please post a link to http://sqlfiddle.com/ showing an example table. — J_H, Oct 08 '17 at 02:14
Re the duplicate: You don't appear to have read the question & my answer, which I strongly suggest you do, until you understand the problem & various solutions, because as the 2nd table in its question shows, it is about *unwanted duplicates in multiple group_concats*. Only ever use group_concat as final output formatting. Map to links before you group_concat. Your starting pre-mapping-to-links query can be writtten many ways, and the ways that group_concat last are what you need. First get correct queries each with 1 group_concat. Then combine via 1 of the 4 ways in my answer. — philipxy, Oct 08 '17 at 02:29

score 0 · Accepted Answer · answered Oct 08 '17 at 11:53

0

Following on from this question, and using that query as the basis for the following example, you can do this with the following:

SELECT 
CONCAT(res_id,': ',res_name) 'Resources', 
GROUP_CONCAT(distinct t_name order by t_id separator ',') 'Topics', 
GROUP_CONCAT(distinct ch_name order by ch_id separator ',') 'Chapters'
FROM (SELECT res_id,
      res_name,
      t_id,
      t_name,
      ch_id, 
      CONCAT("<a href=\"page.asp?topic=",ch_name,"\" title=\"",ch_name,"\">",ch_name,"</a>") as ch_name 
             FROM resources r
             JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
             JOIN topics t on t.t_id = ttr.tr_tid
             JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
             JOIN chapters ch ON ch.ch_id = tch_chid) links
      GROUP BY res_id
      ORDER BY res_id, t_id, ch_id;

Basically I have wrapped the source data in a separate (sub) query, building the links, then performing the GROUP_CONCATs outside of this.

This produces:

<a href="page.asp?topic=CHAPTER #1" title="CHAPTER #1">CHAPTER #1</a>,
<a href="page.asp?topic=CHAPTER #2" title="CHAPTER #2">CHAPTER #2</a>,
<a href="page.asp?topic=CHAPTER #3" title="CHAPTER #3">CHAPTER #3</a>

See this fiddle for more detail

answered Oct 08 '17 at 11:53

Chris J

1,441
9
19

Chris, thanks SO MUCH for this solution. I've implemented it in our system and it's creating the recordset exactly the way we need it. The only problem is that it is SLOW! I recognize that this may be an indexing issue. If you were going to index the tables based on maximizing the speed of this query, what fields would you index? – nbardach Oct 11 '17 at 17:49
Any field which is 'searched' should have an index - this includes the 'joined' fields, too. Are all of your `id` fields indexed? – Chris J Oct 11 '17 at 18:05
Do you mean any field listed in the WHERE and both sides of the ON in the joins? – nbardach Oct 11 '17 at 18:34
Essentially, yes. They should all be separate indexes based on the information available – Chris J Oct 11 '17 at 18:36
Finished indexing... All IDs were already indexed as Primary Keys. I created indexes for everything else that is searched on (ie. in a WHERE) or used for JOINs. Still, performance is very slow. When I ran the query directly through phpMyAdmin, it took 43.37 seconds to return 63 records. Where would be a good place to show you the actual query? – nbardach Oct 12 '17 at 00:32
These are the biggest offenders in the query Profile: Sending data 32.861205 Creating sort index 5.769150 storing result in query cache 5.418440 – nbardach Oct 12 '17 at 16:44
I added a new question to get help on the performance. Would love to hear your thoughts. Many thanks in advance! https://stackoverflow.com/questions/46716622/mysql-very-slow-query-with-concat-group-concat-and-multiple-joins-100-sec-f – nbardach Oct 12 '17 at 18:34

mySQL >> Adding HREF links to a Distinct GROUP_CONCAT

1 Answers1

Linked