Gerelateerde berichten sorteren op meeste overeenkomende tags

[Heedless]

Ik heb drie tables in mijn database:

posts
- id
- titel

tags
- id
- name

tag2post
- id
- tag_id
- post_id

Hiermee koppel ik tags aan posts.

Ik gebruik al een tijd mysql voor mijn databases, maar meestal zijn het simpele queries. Wat ik nu wil doen, daar kom ik niet uit.

Ik heb een id van een post, bijv 10. Ik kan dan tags zoeken die daar aan gekoppeld zijn. Vervolgens kan ik ook weer posts vinden die aan die tags gekoppeld zijn en dus gerelateerd zijn aan de eerste post. Maar deze resultaten wil ik sorteren op overeenkomst: als een post 6 tags overeen heeft met de eerste post, dan wil ik die als eerste resultaat. Een post met 3 post die overeenkomen op nummer twee, en met 1 post op nummer drie, etc.

Ik kan dit wel doen met een hoop omwegen: zoek alle tags bij gegeven post_id. Zoek daarna per tag naar post die bij de tags horen en tel hoe vaak je posts tegenkomen. Sorteer die op voorkomendheid en filter duplicaten eruit. Maar dat is nogal onhandig.
Kan iemand mij helpen bij het formuleren van een query die zoiets kan doen hiervoor? Welke commando's heb ik bijvoorbeeld nodig?

[WernerL]

Ik denk dat je dit het makkelijkst met een subquery op kunt lossen. Dus met 1 query tel je het aantal tags dat overeenkomt, en in je 'hoofdquery' sorteer je op de resultaten van deze subquery.

[renegeuze]

Ik hoop dat mijn moeite niet voor niets was en ik het goed heb begrepen(don't worry, heel veel werk was het nou ook weer niet)


$tag = "aap";
$sql = "
SELECT posttable.id, tagtable.tag, posttable.post, COUNT(tagtable.tag) AS occurrences
FROM tagtable
JOIN posttable ON posttable.id = tagtable.postid
WHERE tagtable.tag = '{$tag}'
GROUP BY posttable.post
ORDER BY occurrences
DESC LIMIT 10
";
$query = mysql_query($sql);
$arr = "";
while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
echo "-{$row['post']}- with {$row['occurrences']} occurrences on {$tag}.<br/>";
}


Ik gebruik hierbij maar 2 tabellen in mijn database.
rows:
postdata.id
postdata.post
tagdata.id(eigenlijk ongebruikt)
tagdata.tag
tagdata.postid(die dus linkt naar postdata.id)

Voorbeeld op: http://geuze.name/flashfocus/heedless/ (waar voor de rest niet veel te zien is)

[jaspermuts]

@renegeuze: je hebt nu alleen maar hoe vaak een post een specifieke tag heeft (waarschijnlijk maximaal 1 x). Dat is handig als je wil opzoeken welke posts een specifieke tag heeft, maar niet om te kijken welke andere post(s) de meeste tags overeen heeft met een geselecteerde post.

Ik heb dit ook eens moeten maken, maar het opnieuw maken lukte ff niet.
Als ik mijn manier terug kan vinden zal ik het hier posten. [:)]

[renegeuze]

ahja, ik lees het nu pas goed.
Ach, misschien kan mijn query nog deels gebruikt worden ;)

[Heedless]

@Renegeuze
Het is inderdaad niet wat ik zoek, maar zal je query wel even doorspitten om te kijken hoe hij werkt, aangezien ik nog niet zo veel weet van meer complexe mysql queries ;)

[Heedless]

Mijn oplossing: ik weet zeker dat het niet de meest optimale manier is, maar het werk :)

Mijn posts hebben een titel (title) en een titel die geschikt is voor de url (title_safe). Als ik het id ($_id) opgeef van een post wil ik de title en title_safe van de 5 posts die de meeste tags gemeen hebben met de opgegeven post.


//maak een lege array voor de resultaten
$related_posts = array();

//zoek alle tags bij een gegeven ID van een post ($_id)
$sql_1 = mysql_query("SELECT t2p.tag_id, t.name FROM tag2post t2p, tags t WHERE t2p.post_id='$_id' AND t2p.tag_id = t.id");
while($row_1 = mysql_fetch_assoc($sql_1)){

//ga alle gevonden tags langs en zoek posts die daar ook mee getagt zijn
$sql_2 = mysql_query("SELECT p.* FROM posts p, tag2post t2p WHERE t2p.tag_id='{$row_1['tag_id']}' AND t2p.post_id=p.id");
while($row_2 = mysql_fetch_assoc($sql_2)){
//maak losse var voor de leesbaarheid
$title_safe = $row_2['title_safe'];
//bestaat er geen variabele met de naam 'post_'+de waarde van title_safe,
//dan is dit de eerste keer dat de post een tag gemeen heeft.
//Maak die var aan en geef hem de waarde van title zodat je die later
//terug kan vinden. Gebruik title_safe als key in de array related_post
//om bij te houden hoeveel tags de post gemeen heeft
if(!isset(${"post_".$title_safe})){
${"post_".$title_safe} = $row_2['title'];
$related_posts[$title_safe] = 1;
}else{
$related_posts[$title_safe]++;
}
}
}
//sorteer de array met hoeveelheden overeenkomsten met behoud van de keys
arsort($related_posts);
$keys = array_keys($related_posts);
$return = "";
//geef de 5 hoogst genoteerde posts
for($i=0;$i<min(5, count($related_posts));$i++){
// $keys[$i] is de title_safe
// ${"post_".$keys[$i]} is de title
$return .='<li><a href="/artikel/'.$keys[$i].'" target="_self" class="item_tag">'.${"post_".$keys[$i]}.'</a></li>';
}

[jaspermuts]

ik heb het gevonden waar ik het over had. Onderstaande is een bewerkte versie van wat er in mn txt-bestandje stond, dus niet alles is diréct toepasbaar :)
Scroll naar onder voor 'de' query.

tag2post:

+-----+------+-----+
| ID |postId|tagId|
+-----+------+-----+
| 1 | 100 | 1 |
+-----+------+-----+
| 2 | 100 | 2 |
+-----+------+-----+
| 3 | 100 | 2 |
+-----+------+-----+
| 4 | 105 | 1 |
+-----+------+-----+
| 5 | 105 | 2 |
+-----+------+-----+


PSEUDO CODE voor wanneer je een post met tags met id 1 en id 2 hebt geselecteerd

select distinct postId from tag2post
order by count ( tagId in (1, 2) ) as relevance DESC


GEWENSTE OUTPUT:

+------+-------------+
|postId| relevance |
+------+-------------+
| 100 | 3 | (regel met postId = 100 heeft 3 matches (1 x 1 + 2 x 2) )
+------+-------------+
| 105 | 2 | (regel met postId = 105 heeft 2 matches (1 x 1 + 1 x 2) )
+------+-------------+


Werkende code om een lijst van relevantie te krijgen voor een post met de tags 1 en 2

SELECT DISTINCT
postId,
(
SELECT COUNT(tag2post_sub.tagId)
FROM tag2post AS tag2post_sub
WHERE tagId IN (1, 2)
AND tag2post_sub.postId = tag2post.postId
GROUP BY postId
) AS relevance
FROM tag2post


@Heedless: volgens mij kun je zonder problemen (heb geen zin om een db op te zetten om dit te gaan tesen) een subquery in je IN gebruiken, voor bij post met Id = 6 9dus overal waar 6 gebruikt wordt slaat dat daarop)

SELECT DISTINCT
postId,
(
SELECT COUNT(sub.tagId)
FROM tag2post AS sub
WHERE tagId IN
(
SELECT DISTINCT tagId from tag2post AS sub_sub
WHERE sub_sub.postId = 6
)
AND sub.postId = tag2post.postId
AND sub.postId != 6
GROUP BY postId
) AS relevance
FROM tag2post
# order by moet met dezelfde subquery, weet niet waarom maar 'relevance' aanspreken kon iig niet
# maar ik had de subquery opgeslagen in een php-var
# je kunt natuurlijk ook hem alleen bijorder-by gebruiken, als veld heb je m niet echt nodig
ORDER BY
(
SELECT COUNT(sub.tagId)
FROM tag2post AS sub
WHERE tagId IN
(
SELECT DISTINCT tagId from tag2post AS sub_sub
WHERE sub_sub.postId = 6
)
AND sub.postId = tag2post.postId
AND sub.postId = tag2post.postId != 6
GROUP BY postId
) DESC