{"id":444,"date":"2015-02-04T22:58:09","date_gmt":"2015-02-04T22:58:09","guid":{"rendered":"https:\/\/www.joetannorella.com\/?p=444"},"modified":"2015-02-04T23:00:51","modified_gmt":"2015-02-04T23:00:51","slug":"implementing-hottest-algorithm-laravel-like-reddit-hacker-news","status":"publish","type":"post","link":"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/","title":{"rendered":"Implementing a &#8216;hottest&#8217; algorithm in Laravel like that of Reddit \/ Hacker News"},"content":{"rendered":"<p>I&#8217;m building a directory of internet marketing products at the moment called <a href=\"http:\/\/imrepo.com\" target=\"_blank\">imrepo.com<\/a>. It&#8217;s a project for me to learn more complex Laravel stuff and toy around with ranking algorithms, though I think there&#8217;s actually some serious money to be made if the idea is executed well.<\/p>\n<p>I had a slight dilemma in that I wasn&#8217;t sure whether I should show all posts by day, or use an algorithm like Reddit to rank each post according to a combination of\u00a0post date and number of net upvotes. I&#8217;ve decided to go with the latter, pretty much because it sounds cooler and is more of a challenge (like I said, this is a learning curve for me).<\/p>\n<p>I basically have 3 tables for this:<\/p>\n<ul>\n<li>posts<\/li>\n<li>users<\/li>\n<li>votes<\/li>\n<\/ul>\n<p>Before sorting by the algorithm, the query must:<\/p>\n<ul>\n<li>Sum votes for each post and join to posts on post_id<\/li>\n<li>Join the posts and users tables with\u00a0the above<\/li>\n<\/ul>\n<p>The complexity of this meant that I was unable to use Laravel&#8217;s fluent or eloquent to run the query, and therefore had to turn to running a raw SQL query. <strong>If anyone thinks this can be run with a normal fluent query then I&#8217;d love to hear about it!<\/strong><\/p>\n<p>This post is more to illustrate how you&#8217;d run a complex query in Laravel, and also how to use a Reddit-esque ranking algorithm (actually this isn&#8217;t even the algorithm that Reddit use, but it&#8217;s the closest and most popular website that I can liken it to).<\/p>\n<p>Here&#8217;s how to run it within a Laravel app:<\/p>\n<pre class=\"brush: php; title: ; notranslate\" title=\"\">\r\n\r\n$posts['products'] = DB::select(DB::raw(&quot;\r\nSELECT\r\nposts.id,\r\nslug,\r\nproduct_price,\r\npost_title,\r\nposts.created_at,\r\nproduct_link,\r\naffiliate_link,\r\nusername,\r\n(SELECT SUM(vote) FROM posts_votes WHERE posts.id = posts_votes.post_id ) as VotesCount\r\nFROM\r\nposts\r\nJOIN\r\nuser\r\nON\r\nposts.post_author=user.id\r\nORDER by\r\n((VotesCount - 1) \/ (TIMESTAMPDIFF(HOUR, posts.created_at, NOW()) + 2)^1.5) DESC\r\n&quot;));\r\n\r\n<\/pre>\n<p>(apologies for the poor formatting above, WordPress is automatically formatting it. I&#8217;ll fix soon)<\/p>\n<p>Since the query returns an object, we can turn it into an array like this:<\/p>\n<pre class=\"brush: php; title: ; notranslate\" title=\"\">\r\n\r\n$posts['products'] = json_decode(json_encode($posts['products']), true);\r\n\r\n<\/pre>\n<p>I&#8217;m aware that this method will become pretty slow when more posts and votes are added to the site, but I&#8217;m very conscious of optimising too soon. At some point I&#8217;ll look into implementing caching and perhaps a cron that auto runs the above every X minutes or something like that.<\/p>\n<p>Here&#8217;s the resulting sort of posts:<\/p>\n<p><a href=\"https:\/\/www.joetannorella.com\/wp-content\/uploads\/2015\/02\/imrepo-ranking.png\"><img decoding=\"async\" loading=\"lazy\" class=\"aligncenter size-large wp-image-445\" src=\"https:\/\/www.joetannorella.com\/wp-content\/uploads\/2015\/02\/imrepo-ranking-533x1024.png\" alt=\"imrepo-ranking\" width=\"533\" height=\"1024\" srcset=\"https:\/\/www.joetannorella.com\/wp-content\/uploads\/2015\/02\/imrepo-ranking-533x1024.png 533w, https:\/\/www.joetannorella.com\/wp-content\/uploads\/2015\/02\/imrepo-ranking-156x300.png 156w, https:\/\/www.joetannorella.com\/wp-content\/uploads\/2015\/02\/imrepo-ranking.png 1300w\" sizes=\"(max-width: 533px) 100vw, 533px\" \/><\/a><\/p>\n<p><strong>Do you know of a way I can make this better or more efficient? Let me know in the comments \ud83d\ude42<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;m building a directory of internet marketing products at the moment called imrepo.com. It&#8217;s a project for me to learn more complex Laravel stuff and toy around with ranking algorithms, though I think there&#8217;s actually some serious money to be made if the idea is executed well. I had a slight dilemma in that I&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[6],"tags":[27,12],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.13 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Implementing a &#039;hottest&#039; algorithm in Laravel like that of Reddit \/ Hacker News - JoeTannorella.com<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Implementing a &#039;hottest&#039; algorithm in Laravel like that of Reddit \/ Hacker News - JoeTannorella.com\" \/>\n<meta property=\"og:description\" content=\"I&#8217;m building a directory of internet marketing products at the moment called imrepo.com. It&#8217;s a project for me to learn more complex Laravel stuff and toy around with ranking algorithms, though I think there&#8217;s actually some serious money to be made if the idea is executed well. I had a slight dilemma in that I...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/\" \/>\n<meta property=\"og:site_name\" content=\"JoeTannorella.com\" \/>\n<meta property=\"article:published_time\" content=\"2015-02-04T22:58:09+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2015-02-04T23:00:51+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.joetannorella.com\/wp-content\/uploads\/2015\/02\/imrepo-ranking-533x1024.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/\",\"url\":\"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/\",\"name\":\"Implementing a 'hottest' algorithm in Laravel like that of Reddit \/ Hacker News - JoeTannorella.com\",\"isPartOf\":{\"@id\":\"https:\/\/www.joetannorella.com\/#website\"},\"datePublished\":\"2015-02-04T22:58:09+00:00\",\"dateModified\":\"2015-02-04T23:00:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.joetannorella.com\/#\/schema\/person\/84eafed4ad6cd6934b006e371c06c7ae\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/\"]}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.joetannorella.com\/#website\",\"url\":\"https:\/\/www.joetannorella.com\/\",\"name\":\"JoeTannorella.com\",\"description\":\"Web Development | Digital Marketing | Business\/Startups | Analytics\/data\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.joetannorella.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.joetannorella.com\/#\/schema\/person\/84eafed4ad6cd6934b006e371c06c7ae\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.joetannorella.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/257001162d06da1a465f242dfa80dd7b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/257001162d06da1a465f242dfa80dd7b?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"url\":\"https:\/\/www.joetannorella.com\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Implementing a 'hottest' algorithm in Laravel like that of Reddit \/ Hacker News - JoeTannorella.com","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/","og_locale":"en_US","og_type":"article","og_title":"Implementing a 'hottest' algorithm in Laravel like that of Reddit \/ Hacker News - JoeTannorella.com","og_description":"I&#8217;m building a directory of internet marketing products at the moment called imrepo.com. It&#8217;s a project for me to learn more complex Laravel stuff and toy around with ranking algorithms, though I think there&#8217;s actually some serious money to be made if the idea is executed well. I had a slight dilemma in that I...","og_url":"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/","og_site_name":"JoeTannorella.com","article_published_time":"2015-02-04T22:58:09+00:00","article_modified_time":"2015-02-04T23:00:51+00:00","og_image":[{"url":"https:\/\/www.joetannorella.com\/wp-content\/uploads\/2015\/02\/imrepo-ranking-533x1024.png"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/","url":"https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/","name":"Implementing a 'hottest' algorithm in Laravel like that of Reddit \/ Hacker News - JoeTannorella.com","isPartOf":{"@id":"https:\/\/www.joetannorella.com\/#website"},"datePublished":"2015-02-04T22:58:09+00:00","dateModified":"2015-02-04T23:00:51+00:00","author":{"@id":"https:\/\/www.joetannorella.com\/#\/schema\/person\/84eafed4ad6cd6934b006e371c06c7ae"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.joetannorella.com\/implementing-hottest-algorithm-laravel-like-reddit-hacker-news\/"]}]},{"@type":"WebSite","@id":"https:\/\/www.joetannorella.com\/#website","url":"https:\/\/www.joetannorella.com\/","name":"JoeTannorella.com","description":"Web Development | Digital Marketing | Business\/Startups | Analytics\/data","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.joetannorella.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.joetannorella.com\/#\/schema\/person\/84eafed4ad6cd6934b006e371c06c7ae","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.joetannorella.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/257001162d06da1a465f242dfa80dd7b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/257001162d06da1a465f242dfa80dd7b?s=96&d=mm&r=g","caption":"admin"},"url":"https:\/\/www.joetannorella.com\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/posts\/444"}],"collection":[{"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/comments?post=444"}],"version-history":[{"count":4,"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/posts\/444\/revisions"}],"predecessor-version":[{"id":449,"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/posts\/444\/revisions\/449"}],"wp:attachment":[{"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/media?parent=444"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/categories?post=444"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.joetannorella.com\/wp-json\/wp\/v2\/tags?post=444"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}