Geek asks and answers

How to get a list with a ratio of 1: N

I have a table of articles and I have a table of tags. There is an N: N relationship between them, so I also have a table called articles_tags_relations. I would like to get a list of articles with tags, however, in addition to the tag name, I also need its identifier. Thus, the returned data should be something like (of course, not in JSON format):

[[1,"FirstArticle",{"1":"FirstTag","2":"SecondTag"}],[2,"SecondArticle",{"3":"ThirdTag"}]]

So far, I have something like:

SELECT article.*, GROUP_CONCAT(tag.name SEPARATOR ', ') AS tags FROM articles AS article LEFT JOIN articles_tags_relations AS relation ON article.id = relation.article_id LEFT JOIN tags AS tag ON relation.tag_id = tag.id LIMIT 0,10;

But it has only tags without identifiers. There will be many read requests on the table, so performance is important. I'm not sure that using GROUP_CONCAT is the way to go, I am open to any ideas. Thank!

+3
source share
4 answers

. CONCAT, , GROUP_CONCAT .

SELECT article.*, 
  GROUP_CONCAT(CONCAT(tag.id,':',tag.name)) AS tags 
FROM articles AS article 
LEFT JOIN articles_tags_relations AS relation ON article.id = relation.article_id 
LEFT JOIN tags AS tag ON relation.tag_id = tag.id
GROUP BY article.id 
LIMIT 0,10

AFAIK GROUP_CONCAT, .

, JSON

SELECT CONCAT('[',GROUP_CONCAT(CONCAT('[',row_data,']')),']') as JSON
FROM
(
  SELECT CONCAT(a.id,',"',a.title,'",{',GROUP_CONCAT(CONCAT("'",tag.id,'":"',tag.name,'"')),'}') as row_data
  FROM articles AS a 
  LEFT JOIN articles_tags_relations AS relation ON a.id = relation.article_id 
  LEFT JOIN tags AS tag ON relation.tag_id = tag.id
  GROUP BY a.id 
  LIMIT 0,10 
) t1
0

? , , , . , . (, , .)

, sql- , , , .

+1

As far as I know, it GROUP_CONCAT()works with the proposal GROUB BY. However, here are two alternatives for getting tag.id with your data:

SELECT article.*, tag.id AS tags_id, GROUP_CONCAT(tag.name SEPARATOR ', ') AS tags FROM articles AS article LEFT JOIN articles_tags_relations AS relation ON article.id = relation.article_id LEFT JOIN tags AS tag ON relation.tag_id = tag.id LIMIT 0,10;

AND 2:

SELECT article.*, GROUP_CONCAT(tag.name SEPARATOR ', ') AS tags, GROUP_CONCAT(tag.id SEPARATOR ', ') AS tag_ids FROM articles AS article LEFT JOIN articles_tags_relations AS relation ON article.id = relation.article_id LEFT JOIN tags AS tag ON relation.tag_id = tag.id LIMIT 0,10;
+1
source

Sorry, I don't seem to understand your problem. For me, the solutions are easy:select a.id, a.name, t.id, t.name from articles a, tags t, articles_tags_relations atr where a.id = atr.article_id and t.id = atr.tag_id

Of course, this does not format the result the way you want, but formatting is not what SQL is for. First you extract the result (see above), then you use the programming language of your choice to format the result set.

0
source

More articles:


All Articles