likes.sql

-- таблица категорий
-- индексное поле только PRIMARY KEY
CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

-- таблица лайков
-- индексное поле PRIMARY KEY
-- user_id тк будет выборка по пользователю
-- news_feed_id тоже буде выборка в запросах
-- is_liked -1: отмена лайка, 1: поставлен лайк
-- в эту таблицу происходит только вставка, update будет замедлять базу
CREATE TABLE IF NOT EXISTS `likes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `news_feed_id` int(11) DEFAULT NULL,
  `is_liked` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `news_feed_id` (`news_feed_id`),
  KEY `is_liked` (`is_liked`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

-- новостная лента
-- индексные поля те по которым будет происходить выборка
-- дату можно делать возможно тоже, не понятна будет ли по ней выборка
-- также можно было бы разбить на партиции по дате для ускорения поиска
CREATE TABLE IF NOT EXISTS `news_feed` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `author` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `content` varchar(243) DEFAULT NULL,
  `date_create` datetime DEFAULT CURRENT_TIMESTAMP,
  `deleted` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  KEY `deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

-- пользователи
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;




-- выборка юзеров проставивших лайки для поста id=1
SELECT u.* FROM users u

JOIN (

	SELECT  
		l.user_id, 			
		SUM(is_liked) sum_liked
	FROM 
		likes l
	WHERE 
		l.news_feed_id = 1	
	GROUP BY 
		l.user_id	
	HAVING 
		sum_liked > 0 
) A

ON A.user_id=u.id
;

-- ------------------------------------------

-- выборка контетна
-- последние 10 записей
SELECT 
	n.*, 
	u_author.username, 
	c.title category_title  
FROM 
	news_feed n

JOIN users u_author
ON u_author.id = n.author

JOIN category c
ON c.id=n.category_id

WHERE 
	n.deleted=0
	
ORDER BY n.id DESC
LIMIT 10;


-- обновление контента с привязкой к автору 
UPDATE news_feed 
SET `content`='some new text' 
WHERE  
	id=2
	AND
	author=1
;

-- поставить лайк
INSERT INTO likes 
(`user_id`, `news_feed_id`, `is_liked`) 
VALUES 
(1, 1, 1);

-- убрать лайк
INSERT INTO likes 
(`user_id`, `news_feed_id`, `is_liked`) 
VALUES 
(1, 1, -1);