Mysql – get all posts with their associative tags without duplicates

join;many-to-manyMySQL

Two tables. Post and Tag. Many to Many bi-directional relationship.

Post Table              Tag Table             Post_Tag Pivot Table
---------------         ------------          -----------------
id   content            id   name             id_post   id_tag
---------------         ------------          -----------------
 1   hey there           1    php                1        1
                         2    python             1        2

I want to get all posts with their tags.

I've tried this:

SELECT P.title, T.tag
From Post P
JOIN Tag T
ORDER BY P.id DESC

It works, but it gives duplicates:

hey there, php
hey there, python

Is there a way to group tags together in one row here. Thanks

Best Answer

You didn't provide DDL and sample data so we can't test anything. Also, it is not clear what is the join predicate, but I think you are looking for GROUP_CONCAT, something along the lines of (Pseudo code):

SELECT P.Title, GROUP_CONCAT(T.Tag SEPARATOR ',') AS Concatenated_Tags
FROM Post P INNER JOIN Tag T ...
GROUP BY P.Title;

HTH

Related Solutions

Mysql – Need advice for a ‘tag’ table design

I would go for option 1 (two tables).

If you only plan to have 1000 content items (and in 3 years, not now), I don't think you will have many performance problems unless each content item is tagged with a dozen tags where most are unique to that item. And even then I don't think you'll see much of a performance problems. It also simplifies your structure if you have 1 tag table for every content type and then you decide to add/remove/change content types later.

If you're really not sure, do you have the time/resources to build two sample DBs, one based on each structure, populate them with what you think is a realistic load (you can write a script/program to generate & insert test data, right? with "boring" tags and content such as "content_1..content_2000 and tag_1... tag_5000) and actually test which performs better?

Mysql – Index optimization

I think that the best for your tag and posttag tables is:

ok for the tagid as a primary key
add an index on your tag field (this cover your second query)
add two index on posttag table like INDEX(postid,tagid) and INDEX(tagid,postid) (this cover your first and second query)

So:

CREATE TABLE `posts` (
  `postid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `post` varchar(255) NOT NULL,
  PRIMARY KEY (`postid`)
);

CREATE TABLE `posttags` (
  `postid` mediumint(9) NOT NULL,
  `tagid` mediumint(9) NOT NULL,
  PRIMARY KEY (`postid`,`tagid`),
  UNIQUE KEY `tagid_postid_UQ` (`tagid`,`postid`),
  FOREIGN KEY (postid)
    REFERENCES posts (postid),
  FOREIGN KEY (tagid)
    REFERENCES tags (tagid)
);

CREATE TABLE `tags` (
  `tagid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `tag` char(30) NOT NULL,
  PRIMARY KEY (`tagid`),
  UNIQUE KEY `tag_UQ` (`tag`)
);

And for your query:

mysql> explain select p.* from tags as t    inner join posttags as pt on t.tagid = pt.tagid   inner join posts as p on p.postid = pt.postid  where t.tag =  ?;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref    | PRIMARY,tag   | tag     | 32      | const          |    1 | Using where; Using index |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | tagid   | 3       | test.t.tagid   |    1 | Using index              |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 4       | test.pt.postid |    1 |                          |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+


explain select p.* from posts as p    inner join posttags as pt on pt.postid = p.postid   inner join tags as t on t.tagid = pt.tagid  where p.postid = ?;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | p     | const  | PRIMARY       | PRIMARY | 4       | const         |    1 |             |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | postid  | 4       | const         |    2 | Using index |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 3       | test.pt.tagid |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

Best Answer

Related Solutions

Mysql – Need advice for a ‘tag’ table design

Mysql – Index optimization

Related Question