Comme mentionné dans les autres réponses, il y a probablement plusieurs-à-plusieurs relations entre les utilisateurs et les balises, représenté comme un tableau. J'ai fait un SQL démo d'un cas simplifié. L' InterestingTags tableau est le tableau de la connexion de l'utilisateur est intéressé à ce que les balises.
/* 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;
Mise à jour: Ajout de php démo.
N'oubliez pas de changer votre mysql constantes avant de lancer la démo
Ce que cela ne fait exécuter deux requêtes à la base de données:
- Une demande pour toutes les questions et leurs tags
- Une demande pour quelles balises intéressent l'utilisateur.
Pour "marquer" une question avec ses balises, il ajoute une class pour chaque balise, il appartient à -- par exemple, une question avec tagged jQuery (où jQuery a l'ID 1) et php (avec l'ID 2) ont l'classes tagged-1 et tagged-2.
Maintenant la combinant avec une autre requête, de l'extraction de l'intéressant tags, vous avez juste à sélectionner les questions ayant des classes correspondant à l'intérêt des balises et le style. Par exemple, si vous êtes intéressé dans les balises avec l'ID 1 et 3, il serait le suivant 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
}
?>
