How can I implement an "interesting tags" feature like that on Stack Overflow?

Answer 1

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).

Answer 2

There is a row in mysql for every member, let's call it "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.

Answer 3

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

• query for the users interesting tags
• make results available to client-side JS
• JS iterates over each question and changes an attribute based on a match (includes removing the post if it matches an 'ignored' tag.

Answer 4

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.

/* 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

What this does is running two queries to the 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.

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');

<?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"&gt;&lt;/script&gt;

<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
    }
?>

Answer 5

@new question: http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html contains an (old but useful) comparison of different kinds of taggning. Don't forget to read the comments, they're very useful.

Answer 6

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