Created
September 23, 2019 18:26
-
-
Save connecteev/0e0d6751a7056af00ead267189a5864e to your computer and use it in GitHub Desktop.
Schema for News feed - need to order posts based on a composite score
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
I am building a content platform and have a Laravel API for my news feed (infinite-scrolling feed like the Linkedin feed, Facebook feed, etc). I need to order the posts in my news feed based on the algorithm below (each parameter is going to have a weight, and I want to use these to calculate and order the results / Posts by the composite score). | |
Parameters for algorithm: | |
Num Comments | |
Num Likes | |
Date Created (Recency of Post) | |
Date Updated (Recency of Post) | |
Date last comment (Recency of Comments) | |
Date last like / reaction (Recency of Likes / Reactions) | |
Length of Post? | |
Fast Rising: Number of likes / comments in past X <hours / days> | |
The DB structure looks like this: | |
Post hasMany Comments | |
Post HasMany Reactions ('likes') | |
The schema is below. How can you do an OrderBy in Laravel based on | |
1. a composite score or combined metric from multiple columns | |
2. values from related tables (comments, reactions) | |
(like what I am trying to do here)? | |
# Dump of table posts | |
# ------------------------------------------------------------ | |
DROP TABLE IF EXISTS `posts`; | |
CREATE TABLE `posts` ( | |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, | |
`slug` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, | |
`description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, | |
`body` longtext COLLATE utf8mb4_unicode_ci NOT NULL, | |
`featured_image_caption` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, | |
`is_pinned` tinyint(1) DEFAULT '0', | |
`reading_time_minutes` int(11) DEFAULT NULL, | |
`canonical_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, | |
`status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, | |
`is_removed_by_admin` tinyint(1) DEFAULT '0', | |
`removal_reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, | |
`num_comments` int(11) NOT NULL, | |
`num_likes` int(11) NOT NULL, | |
`created_at` timestamp NULL DEFAULT NULL, | |
`updated_at` timestamp NULL DEFAULT NULL, | |
`deleted_at` timestamp NULL DEFAULT NULL, | |
`author_id` int(10) unsigned NOT NULL, | |
PRIMARY KEY (`id`), | |
KEY `author_fk_272473` (`author_id`), | |
CONSTRAINT `author_fk_272473` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; | |
DROP TABLE IF EXISTS `comments`; | |
CREATE TABLE `comments` ( | |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
`comment_on_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, | |
`body` longtext COLLATE utf8mb4_unicode_ci NOT NULL, | |
`is_removed` tinyint(1) DEFAULT '0', | |
`created_at` timestamp NULL DEFAULT NULL, | |
`updated_at` timestamp NULL DEFAULT NULL, | |
`deleted_at` timestamp NULL DEFAULT NULL, | |
`user_id` int(10) unsigned NOT NULL, | |
`comment_on_post_id` int(10) unsigned DEFAULT NULL, | |
`comment_on_comment_id` int(10) unsigned DEFAULT NULL, | |
PRIMARY KEY (`id`), | |
KEY `user_fk_369255` (`user_id`), | |
KEY `comment_on_post_fk_376132` (`comment_on_post_id`), | |
KEY `comment_on_comment_fk_376133` (`comment_on_comment_id`), | |
CONSTRAINT `comment_on_comment_fk_376133` FOREIGN KEY (`comment_on_comment_id`) REFERENCES `comments` (`id`), | |
CONSTRAINT `comment_on_post_fk_376132` FOREIGN KEY (`comment_on_post_id`) REFERENCES `posts` (`id`), | |
CONSTRAINT `user_fk_369255` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; | |
# Dump of table reactions | |
# ------------------------------------------------------------ | |
DROP TABLE IF EXISTS `reactions`; | |
CREATE TABLE `reactions` ( | |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
`reaction_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, | |
`is_removed` tinyint(1) DEFAULT '0', | |
`reaction_to_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, | |
`created_at` timestamp NULL DEFAULT NULL, | |
`updated_at` timestamp NULL DEFAULT NULL, | |
`deleted_at` timestamp NULL DEFAULT NULL, | |
`user_id` int(10) unsigned NOT NULL, | |
`reaction_to_post_id` int(10) unsigned DEFAULT NULL, | |
`reaction_to_comment_id` int(10) unsigned DEFAULT NULL, | |
PRIMARY KEY (`id`), | |
KEY `user_fk_369186` (`user_id`), | |
KEY `reaction_to_post_fk_376403` (`reaction_to_post_id`), | |
KEY `reaction_to_comment_fk_376404` (`reaction_to_comment_id`), | |
CONSTRAINT `reaction_to_comment_fk_376404` FOREIGN KEY (`reaction_to_comment_id`) REFERENCES `comments` (`id`), | |
CONSTRAINT `reaction_to_post_fk_376403` FOREIGN KEY (`reaction_to_post_id`) REFERENCES `posts` (`id`), | |
CONSTRAINT `user_fk_369186` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; | |
DROP TABLE IF EXISTS `users`; | |
CREATE TABLE `users` ( | |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, | |
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, | |
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, | |
`email_verified_at` datetime DEFAULT NULL, | |
`password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, | |
`remember_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, | |
`created_at` timestamp NULL DEFAULT NULL, | |
`updated_at` timestamp NULL DEFAULT NULL, | |
`deleted_at` timestamp NULL DEFAULT NULL, | |
PRIMARY KEY (`id`) | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment