如何在Stack Overflow上实现“有趣的标签”特性?

Check my other question with bounty: Finding similar number patterns in table

用bounty来检查我的另一个问题:在表格中找到类似的数字模式

I'm trying to implement an Interesting Tags feature. For reference, this is how it works on SO:

我正在尝试实现一个有趣的标记特性。作为参考，它是这样工作的:

1. I add into the "interesting" list my interested tags (like php, mysql, jquery and so on).
2. 我将感兴趣的标签(如php、mysql、jquery等)添加到“有趣”列表中。
3. Then, if any of the displayed questions has some of the tags in my list, it makes the background orange.
4. 然后，如果显示的任何问题中有一些标签在我的列表中，它使背景变成橙色。

I understand how to use jQuery to do that (there are related questions on that), but can't figure out how to implement the back-end portion using MySQL!

我知道如何使用jQuery来实现这一点(这里有相关的问题)，但是我不知道如何使用MySQL实现后端部分!

So here's my question: How is it done? I imagine it working like this:

我的问题是:这是怎么做到的?我想象它是这样工作的:

• There is a row in mysql for every member, let's call it "interested_tags".
• mysql中对于每个成员都有一行，我们称之为“interested_tags”。
• After I write and submit my tag through input, it is being written in a row "interested_tags".
• 在我输入并提交我的标签之后，它被写在一行“interested_tags”中。

• Then, the main page has a query which shows all answers and it always checks question's tags with mine tags using strpos like this:

  然后，主页上有一个显示所有答案的查询，它总是使用这样的strpos来检查问题的标签:

  if(strpos($question_tags, $my_tags) === true) {
     //and here will be made background orange
  }
  

Am I thinking right or is there any way to do it?

我的想法是对的还是有办法去做?

EDIT: So, can you show me an example or give me some tips how to implement this with many-to-many relationships? Thanks.

编辑:那么，你能给我一个例子或者给我一些如何实现多对多关系的技巧吗?谢谢。

6 个解决方案

#1

As mentioned in the other answers, there's most likely a many-to-many relationship with between users and tags, represented as an own table. I made a SQL demo of a simplified case. The InterestingTags table is the table connecting what user is interested in what tags.

正如在其他答案中提到的，用户和标记之间很可能存在多对多关系，这些关系表示为一个自己的表。我做了一个简化案例的SQL演示。有趣标签表是连接用户感兴趣的标签的表。

/* Create tables */
CREATE TABLE User (id INT NOT NULL AUTO_INCREMENT, name varchar(50), PRIMARY KEY(id));
CREATE TABLE Tag (id INT NOT NULL AUTO_INCREMENT, name varchar(50), PRIMARY KEY(id));
CREATE TABLE InterestingTags (user_id INT NOT NULL REFERENCES User(id), tag_id INT NOT NULL REFERENCES Tag(id), PRIMARY KEY(user_id,tag_id));

/* Insert some data */
/* 3 users, 5 tags and some connections between users and tags */
INSERT INTO User (name) VALUES ('jQueryFreak'), ('noFavoriteMan'), ('generalist'); 
INSERT INTO Tag (name) VALUES ('jQuery'), ('php'), ('asp.net'), ('c#'), ('ruby');
INSERT INTO InterestingTags (user_id, tag_id) VALUES (1,1), (3,1), (3,2), (3,3), (3,4);

/* Select all the users and what tags they are interested in */
SELECT u.name, t.name FROM User u 
LEFT JOIN InterestingTags it ON it.user_id = u.id 
LEFT JOIN Tag t ON t.id = it.tag_id;

/* Select all tag ids that are interesting to user 3 ("generalist") */
SELECT tag_id FROM InterestingTags WHERE user_id = 3;

/* 
    Now let's introduce a questions table.
    For simplicity, let's say a question can only have one tag. 
    There's really a many-to-many relationship here, too, as with user and tag
*/
CREATE TABLE Question (id INT NOT NULL AUTO_INCREMENT, title VARCHAR(50) NOT NULL, tag_id INT NOT NULL REFERENCES Tag(id), PRIMARY KEY(id));

/* Insert some questions */
INSERT INTO Question (title, tag_id) VALUES 
    ('generating random numbers in php', 2),     /*php question*/
    ('hiding divs in jQuery', 1),                /*jQuery question*/
    ('how do i add numbers with jQuery', 1),     /*jQuery question 2*/
    ('asp.net help', 3),                         /*asp.net question */
    ('c# question', 4),                          /*c# question */
    ('ruby question', 5);                        /*ruby question */

/* select all questions and what users are interested in them */
SELECT q.title, u.name FROM Question q
LEFT JOIN InterestingTags it ON it.tag_id = q.tag_id 
LEFT JOIN User u ON u.id = it.user_id;


/* select all questions a user will be interested in. Here the user is jQueryFreak with id = 1 */
SELECT q.id, q.title FROM Question q
LEFT JOIN InterestingTags it ON it.tag_id = q.tag_id
LEFT JOIN User u ON u.id = it.user_id
WHERE u.id = 1;


/* Select all questions and indicate whether or not jQueryFreak (with id = 1) is interested in each one */
/* TODO: make SO question about how to do this as efficient as possible :) */
SELECT q.id, q.title,
    (SELECT COUNT(*) FROM InterestingTags it 
    WHERE it.tag_id = q.tag_id AND it.user_id = 1)
    AS is_interested 
FROM Question q;


/* Let's add a many-to-many relationship between questions and tags. 
   Questions can now have many tags 
*/
ALTER TABLE Question DROP COLUMN tag_id;

CREATE TABLE Question_Tag ( 
    question_id INT NOT NULL REFERENCES Question (id),
    tag_id      INT NOT NULL REFERENCES Tag (id),
    PRIMARY KEY (question_id, tag_id)
);

/* Insert relationships between questions and tags */
INSERT INTO Question_Tag VALUES
    /* First the tags as in the above examples */
    (1,2), (2,1), (3,1),(4,3),(5,4),(6,5),
    /* And some more. ASP.NET question is also tagged C#
    and php question is tagged jQuery */
    (1,1), (4,4);


/* select all questions and what users are interested in them
(Some combinations will show up multiple times. This duplication is removed in the 
two following queries but I didn't find a solution for it here)*/
SELECT q.title, u.name FROM Question q
LEFT JOIN Question_Tag qt ON qt.question_id = q.id /* <-- new join */
LEFT JOIN InterestingTags it ON it.tag_id = qt.tag_id 
LEFT JOIN User u ON u.id = it.user_id;


/* select all questions a user will be interested in. Here the user is jQueryFreak with id = 1 */
SELECT q.id, q.title FROM Question q
LEFT JOIN Question_Tag qt ON qt.question_id = q.id /* <-- new join */
LEFT JOIN InterestingTags it ON it.tag_id = qt.tag_id
LEFT JOIN User u ON u.id = it.user_id
WHERE u.id = 1
GROUP BY q.id; /* prevent duplication of a question in the result list */


/* Select all questions and indicate whether or not jQueryFreak (with id = 1) is interested in each one */
/* STILL TODO: make SO question about how to do this as efficient as possible :) */
SELECT q.id, q.title,
    (SELECT COUNT(*) FROM InterestingTags it
     WHERE it.tag_id = qt.tag_id AND it.user_id = 1)
    AS is_interested 
FROM Question q
LEFT JOIN Question_Tag qt ON qt.question_id = q.id /* <-- new join */
GROUP BY q.id;

Update: Added php demo.
Remember to change your mysql constants before running the demo

更新:添加php演示。在运行演示之前，请记住更改mysql常量

What this does is running two queries to the DB:

它的作用是对DB运行两个查询:

• One asking for all questions and their tags
• 一个人问所有的问题和他们的标签
• One asking for what tags the user is interested in.
• 一个询问用户感兴趣的标签。

To "mark" a question with its tags, it adds a class for each tag it belongs to -- e.g. a question tagged with jQuery (where jQuery has the ID 1) and php (with ID 2) will have the classes tagged-1 and tagged-2.

为了用标签“标记”一个问题，它为它所属的每个标签添加一个类——例如，一个用jQuery标记的问题(jQuery有ID 1)和php (ID 2)将有类tagged-1和tagged-2。

Now combining this with the other query, fetching the interesting tags, you just have to select the questions having classes corresponding to the interesting tags and style them. For example, if you're interested in the tags with ID 1 and 3, it would be the following jQuery code $('.tagged-1, .tagged-3').addClass('interesting-tag');

现在将这个与另一个查询结合起来，获取有趣的标记，您只需要选择与有趣的标签对应的类，并对它们进行样式化。例如，如果您对ID为1和3的标记感兴趣，可以使用以下jQuery代码$(')。tagged-1 .tagged-3”).addClass(“interesting-tag”);

<?php
const mysql_host = "localhost";
const mysql_username = "";
const mysql_password = "";
const mysql_database = "INTERESTINGTEST";

const user_id = 1; //what user is viewing the page?

class Question {
    public $id;
    public $title;
    public $tags;

    function __construct($id,$title) {
        $this->id = $id;
        $this->title = $title;
        $this->tags = array();
    }
}

class Tag {
    public $id;
    public $name;

    function __construct($id,$name) {
        $this->id = $id;
        $this->name = $name;
    }
}

/**************************
Getting info from database
****************************/
mysql_connect(mysql_host,mysql_username,mysql_password);
mysql_select_db(mysql_database);


//Fetch interesting tags
$result = mysql_query("SELECT tag_id FROM InterestingTags WHERE user_id = " . user_id);
$interesting_tags = array();
while($row = mysql_fetch_array($result))
{
    $interesting_tags[] = $row['tag_id'];
}


//Fetch all questions and their tags
$query_select_questions =
'SELECT q.id AS q_id, q.title AS q_title, t.id AS t_id, t.name AS t_name FROM Question q
LEFT JOIN Question_Tag qt ON qt.question_id = q.id
LEFT JOIN Tag t ON t.id = qt.tag_id';

$result = mysql_query($query_select_questions);
$questions = array();

while($row = mysql_fetch_array($result))
{
    $q_id =    $row['q_id'];
    $q_title = $row['q_title'];
    $t_id =    $row['t_id'];
    $t_name =  $row['t_name'];

    if (!array_key_exists($q_id, $questions))
        $questions[$q_id] = new Question($q_id, $q_title);

    $questions[$q_id]->tags[] = new Tag($t_id, $t_name);
}

mysql_close();


/**************************
Write document
****************************/
?>

<style>
    .question { padding:0px 5px 5px 5px; border:1px solid gray; margin-bottom: 10px; width:400px }
    .interesting-tag { background-color: #FFEFC6 }
</style>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.js"></script>

<script>
    var interesting_tags = [ <?php echo implode($interesting_tags,',') ?> ];
    var tagclass_prefix = ".tagged-";
    var tags_selector = tagclass_prefix + interesting_tags.join(", " + tagclass_prefix);

    $(function() {
        $(tags_selector).addClass("interesting-tag");
    });
</script>


<?php
    foreach ($questions as $q) {
        $tagsIDs = array();
        $tagNames = array();
        foreach ($q->tags as $tag) {
            $tagsIDs[] = $tag->id;
            $tagNames[] = $tag->name;
        }
        $classValue = "tagged-" . implode($tagsIDs," tagged-");
        $tagNames = implode($tagNames, ", ");
?>

<div id="question-<?php echo $q->id ?>" class="question <?php echo $classValue ?>">
    <h3><?php echo $q->title ?></h3>
    Tagged with <strong><?php echo $tagNames ?></strong>
</div>

<?php
    }
?>

#2

There is a row in mysql for every member, let's call it "interested_tags".

mysql中对于每个成员都有一行，我们称之为“interested_tags”。

More likely, there is an additional table that represents a many-to-many relationship between users and tags. With another table that associates tags with questions.

更有可能的是，还有一个表表示用户和标记之间的多对多关系。与另一个表关联标签与问题。

Then you'd just need a query (or more likely a stored procedure) that compares a user's tags to a question's tags and returns a boolean true or false.

然后，您只需要一个查询(更可能是一个存储过程)，该查询将用户的标记与问题的标记进行比较，并返回一个布尔值true或false。

#3

Stack Overflow tags work with at least * in the tag, so store your tags in an array and iterate through them, using pattern matching (doesn't matter whether you use glob, SQL, or regex, as long as the user knows which will be used).

堆栈溢出标记至少可以在标记中使用*，所以将您的标记存储在一个数组中并使用模式匹配进行迭代(使用glob、SQL或regex都没有关系，只要用户知道将使用哪个)。

#4

This might enlighten you. As Kelly said, it's done in Javascript, after the page is loaded. As far as I can tell, they load all the tags for all the questions and the ones that have the same tags as on the right side, they get highlighted. See

这可能会启发你。正如Kelly所说，它是在加载页面之后用Javascript完成的。据我所知，他们装载了所有问题的所有标签，以及右边有相同标签的标签，它们会被高亮显示。看到

#5

@new question: http://skmzq.qiniucdn.com/data/20060423142114/index.html contains an (old but useful) comparison of different kinds of taggning. Don't forget to read the comments, they're very useful.

@new question: http://skmzq.qiniucdn.com/data/20060423142114/index.html包含不同类型标签的(旧的但有用的)比较。不要忘记阅读评论，它们非常有用。

#6

From the way the pages are rendering, I have guessed that the tag comparison is being done in JavaScript. So the steps would be:

从页面呈现的方式来看，我猜测标记比较是在JavaScript中进行的。所以步骤是:

• query for the users interesting tags
• 查询用户感兴趣的标签
• make results available to client-side JS
• 使结果对客户端JS可用
• JS iterates over each question and changes an attribute based on a match (includes removing the post if it matches an 'ignored' tag.
• JS对每个问题进行迭代，并基于匹配修改属性(包括如果匹配“忽略”标记，则删除post。