I’m building a directory of internet marketing products at the moment called imrepo.com. It’s a project for me to learn more complex Laravel stuff and toy around with ranking algorithms, though I think there’s actually some serious money to be made if the idea is executed well.
I had a slight dilemma in that I wasn’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 post date and number of net upvotes. I’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).
I basically have 3 tables for this:
Before sorting by the algorithm, the query must:
- Sum votes for each post and join to posts on post_id
- Join the posts and users tables with the above
The complexity of this meant that I was unable to use Laravel’s fluent or eloquent to run the query, and therefore had to turn to running a raw SQL query. If anyone thinks this can be run with a normal fluent query then I’d love to hear about it!
This post is more to illustrate how you’d run a complex query in Laravel, and also how to use a Reddit-esque ranking algorithm (actually this isn’t even the algorithm that Reddit use, but it’s the closest and most popular website that I can liken it to).
Here’s how to run it within a Laravel app:
$posts['products'] = DB::select(DB::raw(" SELECT posts.id, slug, product_price, post_title, posts.created_at, product_link, affiliate_link, username, (SELECT SUM(vote) FROM posts_votes WHERE posts.id = posts_votes.post_id ) as VotesCount FROM posts JOIN user ON posts.post_author=user.id ORDER by ((VotesCount - 1) / (TIMESTAMPDIFF(HOUR, posts.created_at, NOW()) + 2)^1.5) DESC "));
(apologies for the poor formatting above, WordPress is automatically formatting it. I’ll fix soon)
Since the query returns an object, we can turn it into an array like this:
$posts['products'] = json_decode(json_encode($posts['products']), true);
I’m aware that this method will become pretty slow when more posts and votes are added to the site, but I’m very conscious of optimising too soon. At some point I’ll look into implementing caching and perhaps a cron that auto runs the above every X minutes or something like that.
Here’s the resulting sort of posts:
Do you know of a way I can make this better or more efficient? Let me know in the comments 🙂