Vitaphoto: Облака тегов — различия между версиями

Материал из YourcmcWiki
Перейти к: навигация, поиск
Строка 32: Строка 32:
  
 
Самое интересное, что если таблицы <tt>tag_pair</tt> и <tt>tag_pair_image</tt> материализовать и поддерживать в актуальном состоянии, такое решение выполняется за вполне приемлемое время.
 
Самое интересное, что если таблицы <tt>tag_pair</tt> и <tt>tag_pair_image</tt> материализовать и поддерживать в актуальном состоянии, такое решение выполняется за вполне приемлемое время.
 +
 +
== 3D-облака тегов ==
 +
 +
Для [http://www.wordpress.org/ WordPress] существует плагин, отображающий в виде Flash-вставки трёхмерное вращающееся облако тегов.
 +
 +
Пример:
 +
 +
<html><object type="application/x-shockwave-flash" data="</html>{{localimage:tagcloud.swf}}<html>" width="640" height="480">
 +
<param name="movie" value="</html>{{localimage:tagcloud.swf}}<html>" />
 +
<param name="bgcolor" value="#ffffff" />
 +
<param name="AllowScriptAccess" value="always" />
 +
<param name="wmode" value="transparent" />
 +
<param name="flashvars" value="tcolor=0x111111&amp;tcolor2=0x336699&amp;hicolor=0x&amp;tspeed=100&amp;distr=true&amp;mode=both&amp;tagcloud=%3Ctags%3E%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fapple%2F%27+class%3D%27tag-link-79%27+title%3D%279+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.5135135135135pt%3B%27%3EApple%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fasus%2F%27+class%3D%27tag-link-192%27+title%3D%279+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.5135135135135pt%3B%27%3EAsus%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fblogging%2F%27+class%3D%27tag-link-222%27+title%3D%276+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.3783783783784pt%3B%27%3Eblogging%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fbrowser%2F%27+class%3D%27tag-link-178%27+title%3D%278+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.1351351351351pt%3B%27%3Ebrowser%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fbug%2F%27+class%3D%27tag-link-104%27+title%3D%275+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8pt%3B%27%3Ebug%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fchrome%2F%27+class%3D%27tag-link-229%27+title%3D%277+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.7567567567568pt%3B%27%3EChrome%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fdesign%2F%27+class%3D%27tag-link-24%27+title%3D%2714+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+11.405405405405pt%3B%27%3Edesign%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Feee-pc%2F%27+class%3D%27tag-link-193%27+title%3D%2713+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+11.027027027027pt%3B%27%3Eeee-pc%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fembed%2F%27+class%3D%27tag-link-91%27+title%3D%276+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.3783783783784pt%3B%27%3Eembed%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fexperiences%2F%27+class%3D%27tag-link-72%27+title%3D%279+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.5135135135135pt%3B%27%3Eexperiences%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fexperiment%2F%27+class%3D%27tag-link-115%27+title%3D%278+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.1351351351351pt%3B%27%3Eexperiment%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Ffirefox%2F%27+class%3D%27tag-link-37%27+title%3D%279+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.5135135135135pt%3B%27%3EFirefox%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Ffix%2F%27+class%3D%27tag-link-121%27+title%3D%277+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.7567567567568pt%3B%27%3Efix%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fflash%2F%27+class%3D%27tag-link-18%27+title%3D%2732+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+18.216216216216pt%3B%27%3EFlash%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fgadgets%2F%27+class%3D%27tag-link-4%27+title%3D%2713+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+11.027027027027pt%3B%27%3EGadgets%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fgoogle%2F%27+class%3D%27tag-link-28%27+title%3D%2712+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+10.648648648649pt%3B%27%3EGoogle%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fidea%2F%27+class%3D%27tag-link-57%27+title%3D%275+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8pt%3B%27%3Eidea%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fintel%2F%27+class%3D%27tag-link-256%27+title%3D%279+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.5135135135135pt%3B%27%3EIntel%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fiphone%2F%27+class%3D%27tag-link-96%27+title%3D%276+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.3783783783784pt%3B%27%3EiPhone%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Flaptop%2F%27+class%3D%27tag-link-65%27+title%3D%277+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.7567567567568pt%3B%27%3Elaptop%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Flinux%2F%27+class%3D%27tag-link-202%27+title%3D%2716+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+12.162162162162pt%3B%27%3Elinux%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fmicrosoft%2F%27+class%3D%27tag-link-250%27+title%3D%278+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.1351351351351pt%3B%27%3EMicrosoft%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fmobile-phone%2F%27+class%3D%27tag-link-34%27+title%3D%275+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8pt%3B%27%3Emobile+phone%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fnabaztag%2F%27+class%3D%27tag-link-50%27+title%3D%276+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.3783783783784pt%3B%27%3ENabaztag%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fnetbook%2F%27+class%3D%27tag-link-194%27+title%3D%2719+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+13.297297297297pt%3B%27%3Enetbook%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fnokia%2F%27+class%3D%27tag-link-74%27+title%3D%277+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.7567567567568pt%3B%27%3ENokia%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fopen-source%2F%27+class%3D%27tag-link-186%27+title%3D%277+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.7567567567568pt%3B%27%3Eopen+source%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fplugin%2F%27+class%3D%27tag-link-40%27+title%3D%2717+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+12.540540540541pt%3B%27%3Eplugin%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fplugins%2F%27+class%3D%27tag-link-85%27+title%3D%278+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.1351351351351pt%3B%27%3Eplugins%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fport%2F%27+class%3D%27tag-link-221%27+title%3D%276+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.3783783783784pt%3B%27%3Eport%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fsoftware%2F%27+class%3D%27tag-link-5%27+title%3D%277+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.7567567567568pt%3B%27%3ESoftware%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Ftag-cloud%2F%27+class%3D%27tag-link-109%27+title%3D%2710+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.8918918918919pt%3B%27%3Etag+cloud%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fthemes%2F%27+class%3D%27tag-link-22%27+title%3D%275+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8pt%3B%27%3Ethemes%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Ftwitter%2F%27+class%3D%27tag-link-234%27+title%3D%278+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.1351351351351pt%3B%27%3ETwitter%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fubuntu%2F%27+class%3D%27tag-link-203%27+title%3D%2718+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+12.918918918919pt%3B%27%3EUbuntu%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fupdate%2F%27+class%3D%27tag-link-139%27+title%3D%277+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.7567567567568pt%3B%27%3Eupdate%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fusb%2F%27+class%3D%27tag-link-55%27+title%3D%276+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.3783783783784pt%3B%27%3EUSB%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fvideo%2F%27+class%3D%27tag-link-44%27+title%3D%2711+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+10.27027027027pt%3B%27%3Evideo%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fvista%2F%27+class%3D%27tag-link-47%27+title%3D%278+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.1351351351351pt%3B%27%3EVista%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fwifi%2F%27+class%3D%27tag-link-51%27+title%3D%276+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8.3783783783784pt%3B%27%3Ewifi%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fwindows%2F%27+class%3D%27tag-link-46%27+title%3D%279+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.5135135135135pt%3B%27%3EWindows%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fwordpress%2F%27+class%3D%27tag-link-19%27+title%3D%2742+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+22pt%3B%27%3EWordPress%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fwordpress-plugins%2F%27+class%3D%27tag-link-9%27+title%3D%278+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+9.1351351351351pt%3B%27%3EWordPress+plugins%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fwp-cumulus%2F%27+class%3D%27tag-link-108%27+title%3D%2731+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+17.837837837838pt%3B%27%3EWP-Cumulus%3C%2Fa%3E%0A%3Ca+href%3D%27http%3A%2F%2Fwww.roytanck.com%2Ftag%2Fyoutube%2F%27+class%3D%27tag-link-90%27+title%3D%275+topics%27+rel%3D%22tag%22+style%3D%27font-size%3A+8pt%3B%27%3EYouTube%3C%2Fa%3E%3C%2Ftags%3E&amp;categories=%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Finternet%2Fblogging-internet%2F%22+title%3D%22View+all+posts+filed+under+Blogging%22%3EBlogging%3C%2Fa%3E+%287%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Fenglish%2F%22+title%3D%22View+all+posts+filed+under+English%22%3EEnglish%3C%2Fa%3E+%28248%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Fenvironment%2F%22+title%3D%22View+all+posts+filed+under+Environment%22%3EEnvironment%3C%2Fa%3E+%286%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Finternet%2Fflash%2F%22+title%3D%22View+all+posts+filed+under+Flash%22%3EFlash%3C%2Fa%3E+%2824%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Fgadgets%2F%22+title%3D%22View+all+posts+filed+under+Gadgets%22%3EGadgets%3C%2Fa%3E+%2878%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Fideas%2F%22+title%3D%22View+all+posts+filed+under+ideas%22%3Eideas%3C%2Fa%3E+%2810%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Finternet%2F%22+title%3D%22View+all+posts+filed+under+Internet%22%3EInternet%3C%2Fa%3E+%2861%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Fnederlands%2F%22+title%3D%22View+all+posts+filed+under+Nederlands%22%3ENederlands%3C%2Fa%3E+%2812%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Fpersonal%2F%22+title%3D%22View+all+posts+filed+under+Personal%22%3EPersonal%3C%2Fa%3E+%2827%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Fprogramming%2F%22+title%3D%22View+all+posts+filed+under+Programming%22%3EProgramming%3C%2Fa%3E+%284%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Frecommended-reading%2F%22+title%3D%22View+all+posts+filed+under+recommended+reading%22%3Erecommended+reading%3C%2Fa%3E+%283%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Finternet%2Fseo%2F%22+title%3D%22View+all+posts+filed+under+SEO%22%3ESEO%3C%2Fa%3E+%287%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Fsoftware%2F%22+title%3D%22View+all+posts+filed+under+Software%22%3ESoftware%3C%2Fa%3E+%2851%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Finternet%2Fwebdesign%2F%22+title%3D%22View+all+posts+filed+under+Web+design%22%3EWeb+design%3C%2Fa%3E+%2822%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Finternet%2Fweb-hosting-internet%2F%22+title%3D%22View+all+posts+filed+under+Web+hosting%22%3EWeb+hosting%3C%2Fa%3E+%287%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Finternet%2Fwordpress%2F%22+title%3D%22View+all+posts+filed+under+WordPress%22%3EWordPress%3C%2Fa%3E+%289%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Finternet%2Fwordpress-plugins%2F%22+title%3D%22View+all+posts+filed+under+WordPress+plugins%22%3EWordPress+plugins%3C%2Fa%3E+%2845%29%3Cbr+%2F%3E%0A%09%3Ca+href%3D%22http%3A%2F%2Fwww.roytanck.com%2Fcategory%2Finternet%2Fwordpress-themes%2F%22+title%3D%22View+all+posts+filed+under+WordPress+themes%22%3EWordPress+themes%3C%2Fa%3E+%2815%29%3Cbr+%2F%3E%0A" />
 +
</object>
 +
</html>
  
 
[[Категория:Разработка]]
 
[[Категория:Разработка]]

Версия 19:21, 5 ноября 2009

Облака тегов — набор ссылок, расположенных кластерно или хаотически с размером каждой ссылки, зависящим от её «важности». Конкретнее, в случае «тегов» (меток), каждая ссылка ведёт на все элементы, имеющие данный тег, а размер ссылки зависит от количества таких элементов. Таким образом, наиболее популярные метки оказываются крупными, а непопулярные — мелкими.

Однако, классические, «тупые» облака тегов — обычно свалка в духе «фигпоймёшь», что и где, особенно, если тегов много. (в одном моём Vitaphoto их больше 250…) Но сама-то идея — замечательная! И в Vitaphoto хочется заменить облаками тегов навигацию по альбомам. Как это сделать?

Итак, идея: часто бывает, что какие-то теги являются «подтегами» других. Например, все фотографии с людьми помечены тегом «люди», одновременно некоторые из них помечены тегом с именем конкретного человека. Или же: все фотографии с какой-нибудь тусовки-выезда помечены тегом «Тусовка-выезд 2009», при этом внутри неё тоже существовали «подмероприятия», например, «Экскурсия на Фиолетовые Холмы», и тегами с такими названиями помечены соответствующие множества фотографий. Зачем показывать эти «подтеги», к примеру, на главной странице? Не нужно это.

Правда, может быть ситуация, когда два тега А и Б встречаются на фотографиях только вместе (возникает, конечно, вопрос, нафига ж их тогда два, а не один?) — в этом случае, по-видимому, должны включаться оба.

А при переходе «внутрь» какого-то тега — можно сужать множество тегов облака до тегов, встречающихся вместе с ним.

SQL

А теперь вопрос: как это реализовать средствами MySQL? :)

Есть таблица-отношение tag_image с двумя полями «ID тега» (ti_tag) и «ID изображения» (ti_image). Каждая строчка означает, что фотография с соответствующим ID имеет соответствующий тег.

Ответ ужасен:

SELECT DISTINCT tp_tag1
FROM (SELECT DISTINCT t1.ti_tag tp_tag1, t2.ti_tag tp_tag2 FROM tag_image t1 LEFT JOIN tag_image t2 ON t2.ti_image=t1.ti_image AND t2.ti_tag!=t1.ti_tag) tag_pair
JOIN (SELECT t1.ti_tag tpi_tag1, t1.ti_image tpi_image, t2.ti_tag tpi_tag2 FROM tag_image t1 LEFT JOIN tag_image t2 ON t2.ti_image=t1.ti_image AND t2.ti_tag!=t1.ti_tag) tag_pair_image
ON tpi_tag1=tp_tag1 AND (tpi_tag2!=tp_tag2 OR tp_tag2 IS NULL)
 
UNION
 
SELECT DISTINCT ti_tag FROM tag_image WHERE ti_tag NOT IN (
  SELECT tp_tag1 FROM (SELECT DISTINCT t1.ti_tag tp_tag1, t2.ti_tag tp_tag2 FROM tag_image t1 LEFT JOIN tag_image t2 ON t2.ti_image=t1.ti_image AND t2.ti_tag!=t1.ti_tag) tag_pair
  LEFT JOIN tag_image h1 ON h1.ti_tag IN (tp_tag1, tp_tag2)
  LEFT JOIN tag_image n1 ON n1.ti_image=h1.ti_image AND n1.ti_tag != h1.ti_tag AND n1.ti_tag IN (tp_tag1, tp_tag2)
  WHERE n1.ti_tag IS NULL
)

Самое интересное, что если таблицы tag_pair и tag_pair_image материализовать и поддерживать в актуальном состоянии, такое решение выполняется за вполне приемлемое время.

3D-облака тегов

Для WordPress существует плагин, отображающий в виде Flash-вставки трёхмерное вращающееся облако тегов.

Пример: