Ich weiß nicht, ob’s jemand schon bemerkt hat^^, aber seit letzter Woche findet sich in meiner Sidebar ein Abschnitt mit den beliebtesten Beiträgen, und wie das bei mir so üblich ist, bemühe ich da nicht groß ein Plugin, sondern hab das selber gebastelt. Und da es vielleicht den einen oder anderen auch interessieren könnte, gibt’s diesen Beitrag… Verbesserungsvorschläge sind natürlich willkommen.
Die Frage war also: Welche Kriterien sollen für die Beliebtheit gelten, und wie krieg ich das mit einer Datenbankabfrage hin? Die Kriterien, an die ich gedacht habe, sind:
- Aufrufzahlen der Beiträge, gezählt mit WP-PostViews;
- Anzahl der Kommentare;
- Länge der Kommentare – kurze à la „toller Beitrag“ weniger stark gewichten;
- Alter der Beiträge – neue Beiträge mit vielen Aufrufen oder Kommentaren sollen bevorzugt werden – bzw. Aufrufe pro Tag.
Wer ein Plugin zur Beitragsbewertung einsetzt, mag dessen Werte natürlich auch noch mit einschließen; entsprechendes gilt für ein anderes Statistik-Plugin als WP-PostViews. Und natürlich gibt’s auch etliche „Popular Posts“-Plugins, doch die wenigsten davon berücksichtigen mehr als die Kommentaranzahl – und eine maßgeschneiderte Lösung ist mir eben auch lieber…
Generell stellt sich noch die Frage nach der Gewichtung – wenn bei euch fast jeder Besucher kommentiert, müsst ihr die Kommentaranzahl nicht deutlich stärker als die Aufrufe gewichten, und man kann natürlich eigene Schwerpunkte setzen. Und man muss die einzelnen Kriterien auch nicht einfach mit einem Faktor addieren, sondern kann etwa alle anderen durch das Alter teilen u.v.a.m. Meine Gewichtung seht ihr unten im/nach dem Code.
Nun könnte man dazu mehrere Datenbankabfragen machen, die alle der Post-ID, also der eindeutigen Nummer eines Beitrags, ihren jeweiligen Wert zuordnen, und diese Arrays dann mittels PHP verrechnen und gewichten – ich hab’s hingegen in eine einzige MySQL-Abfrage gequetscht (war auch ’ne kleine Übung in Sachen MySQL… und ist sicher nicht perfekt…); mag sein, dass einzelne Abfragen schneller wären, aber nach etwas Optimierung hatte ich meine Query so weit, dass sie nicht viel länger dauert als das „Einsammeln“ der Kommentarlängen (die bei >10000 Kommentaren/Trackbacks schon ein bisschen dauert), und vermutlich geht’s nicht viel schneller – konkret: von ca. 1½ auf 0,3 Sekunden.
(Aus der Zeit vor der Optimierung stammt auch die kleine Cache-Funktionalität, die ihr gleich im Code seht; bei Verwendung eines Datenbank-Caches oder Verlassen auf den Query Cache der Datenbank selbst wäre die auch weniger wichtig.)
Hier also der Code:
function ag_popular_posts($num=10) {
global $wpdb;
$lastupdate = (int) get_option("ag_popular_lastupdate",0);
$output = '';
if (time()-$lastupdate > 1800) {
$exclude = "
AND ID NOT IN (78,70)
AND ID NOT IN (
SELECT object_id FROM $wpdb->term_relationships
WHERE $wpdb->term_relationships.term_taxonomy_id IN (92,384)
)";
$posts = $wpdb->get_results("
SELECT ID,post_date,post_title,post_name,comment_count,
$wpdb->postmeta.meta_value AS views,
TO_DAYS(NOW())-TO_DAYS(post_date) AS age,
comment_length,
($wpdb->postmeta.meta_value/2
+comment_count*50
+comment_length/comment_count*5
+(TO_DAYS(post_date)-TO_DAYS('2006-09-06'))*5) AS popularity
FROM $wpdb->posts,$wpdb->postmeta,(
SELECT comment_post_id, SUM(LENGTH(comment_content)) AS comment_length
FROM $wpdb->comments
WHERE comment_approved='1'
GROUP BY comment_post_id) AS thecomm
WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
AND $wpdb->posts.ID = thecomm.comment_post_id
AND $wpdb->postmeta.meta_key = 'views'
AND comment_count > 0
AND post_status = 'publish'
AND post_type = 'post'
AND post_password = ''
$exclude
ORDER BY popularity DESC, post_date
LIMIT $num");
if ($posts) {
$output="<ul>";
$pop0=0;
foreach ($posts as $p) {
if ($pop0==0) $pop0=$p->popularity;
$output.='<li><a href="'.get_permalink($p->ID).'" title="'.
$p->comment_count.' Kommentare mit durchschnittlich '.
($p->comment_count==0?'0':number_format($p->comment_length/$p->comment_count,1)).' Zeichen, '.
number_format($p->views).'x aufgerufen, '.
number_format($p->age).' Tage alt'.
'">'.
$p->post_title."</a> ".
"<small>(".number_format($p->popularity/$pop0*100,0).'%'.")</small>".
"</li>\n";
}
$output.="</ul>\n";
}
update_option("ag_popular_lastupdate",time());
update_option("ag_popular_cached",$output);
} else {
$output = get_option("ag_popular_cached",'');
}
if ($output!='') {
echo "<li id=\"side-popular\">\n".
"<h2>Beliebteste Beiträge</h2>\n".
$output.
"</li>\n";
}
}
Also gehen wir den Code mal durch:
Wir beginnen mit der Abfrage, ob die Liste aktualisiert werden soll – das Intervall liegt hier bei 1800 Sekunden, also einer halben Stunde.
Um bestimmte Beiträge auszuschließen, kann man ihre ID wie in der ersten Zeile des ausgelagerten $exclude-Statements angeben – bei mir ist es 78 = die Liste der Musik-Zitate, die aufgrund ihrer hohen Abrufzahlen unangefochten an Platz 1 wäre, aber nicht so interessant ist, dass ich sie hier in der Beliebtheitsliste hervorheben möchte, und die 70 = die damalige Technorati-Ketten-Aktion, die nur bei manchen Gewichtungsversuchen in der Top 10 wäre –, oder alternativ z.B. auch post_name.
Eine ganze Kategorie oder ein ganzes Tag kann man mit dem zweiten AND ID NOT IN-Statement ausschließen. Dazu muss man die term_taxonomy_id der/des fraglichen Kategorie/Tag herausfinden – indem man in der Tabelle wp_terms anhand des Namens die term_id findet und mit dieser in wp_term_taxonomy nachschlägt (oder irgendwelche WP-Funktionen verwendet, mit get_term_by() könnt’s vielleicht gehen) – und am Ende einsetzen; bei mir will ich alle Quiz-Beiträge (92) und nur englische (384) ausschließen (bzw. in englischer Ansicht alle deutschen, da steht dann 385).
Weiter geht’s mit der eigentlichen SELECT-Abfrage. In deren ersten vier Zeilen stehen ein paar für die Ausgabe benötigte Angaben, danach in Klammern (...) AS popularity der eigentliche Beliebtheitswert.
Wie ihr (vielleicht) seht, gewichte ich die Seitenaufrufe mit 0,5, die Kommentaranzahl mit 50, die durchschnittliche Kommentarlänge ebenso wie die Neuheit des Beitrags (Tage seit Blog-Start, was ihr für euch natürlich ändern müsst, siehe gelb hinterlegtes Datum – wenn ihr stattdessen das Alter, also die Tage zwischen Beitrag und heute, verwenden wollt, nehmt TO_DAYS(NOW())-TO_DAYS(post_date)) mit 5, was sich nach etwas Herumspielen als recht passend für mein Blog gezeigt hat.
(Die kurz darüber deklarierten Felder (AS views, AS age) kann man hier übrigens nicht mit den neuen Namen nehmen, da meckert MySQL – also eben nochmal komplett einsetzen.)
Die FROM-Zeile enthält neben der wp_posts-Tabelle noch wp_postmeta, weil dort die Aufrufzahlen von WP-PostViews liegen – braucht ihr nicht, wenn ihr diese Werte nicht verwendet – und das untergeordnete SELECT für die Kommentarlänge, genauer: die Summe der Länge aller Kommentare mit derselben post_id (inkl. Trackbacks; diese könnte man mit AND comment_type='' vor GROUP ausschließen).
Die WHERE– und die erste AND-Zeile verknüpfen die beteiligten Tabellen über die Post-ID, die nächste wählt die WP-PostViews-Werte aus (die eben „views“ heißen) und der Rest beschränkt das ganze auf geeignete Beiträge.
Danach wird die Ausgabe zusammengebastelt, insb. in der foreach-Schleife, die über die Beiträge läuft, wo der Link samt Tooltip (title) mit statistischen Infos und einem Prozentwert der Beliebtheit mit 100% für den 1. Platz zusammengeklebt wird.
Wenn diese Ausgabe zusammengeklöppelt ist, wird sie noch schnell in die Datenbank für unseren kleinen Cache geschrieben, von wo aus sie (im else-Zweig) innerhalb der nächsten halben Stunde gelesen wird. Und zu guter Letzt muss das ganze natürlich noch (mit Überschrift) ausgegeben werden.
So, das war jetzt jede Menge Code. Wer Verständnis- oder sonstige Fragen, Anmerkungen, Stellungnahmen, Lob, Kritik, Verbesserungsvorschläge, Beleidigungen, Ideen oder Anregungen hat, nur raus damit – Moment, auf eine dieser Arten von Äußerungen solltet ihr besser verzichten.^^
Wie dem auch sei, gegen 21:55 kamen Axel und Kollegen ironischerweise mit „Too Late“ auf die Bühne, einem von – wenn ich mich nicht täusche – drei Songs des neuen Albums. Mag etwas wenig erscheinen, aber auch bei anderen Bands, die eine ordentliche Auswahl an alten Hits haben, keine Seltenheit und absolut kein Beinbruch.
Mal wieder ein geiles Konzert!
Im GWUP-Blog findet sich aktuell eine Beitragsserie