Подсчитывайте сообщения по категориям и по количеству просмотров в WordPress

Я работаю над Wordpress и пытаюсь выполнить запрос, который вернет следующую таблицу: 

+------------+------------+------------+
|    name    |   posts    |   views    |
|------------|------------|------------|
| politics   |         10 |        311 |
|------------|------------|------------|
| economy    |         20 |        764 |
|------------|------------|------------|
| gossip     |         15 |        551 |
|------------|------------|------------|
| entertain  |          2 |        117 |
|------------|------------|------------|
| fun        |          7 |        249 |
+------------+------------+------------+

Поле просмотров - это количество просмотров сообщений внутри каждой категории, поле сообщений - это количество сообщений внутри каждой категории.

Таблица views имеет следующую структуру: 

view_id (INT)
view_date (DATETIME)
view_post_id (INT)

Я использую эту структуру, так как мне нужно знать дату каждого посещения. Когда сообщение просматривается, оно регистрируется в этой таблице.

В настоящее время, Я могу получить количество сообщений в категории со следующим запросом:

SELECT wp_terms.name,
count(wp_term_relationships.object_id) AS num_posts
FROM wp_term_taxonomy
JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
JOIN wp_posts ON (wp_term_relationships.object_id = wp_posts.id AND wp_term_taxonomy.taxonomy='category' AND post_type='post' AND post_status='publish')
GROUP BY wp_terms.term_id
ORDER BY name ASC

И я могу получить количество просмотров для каждого поста с помощью следующего запроса.

SELECT view_post_id, COUNT(view_id) as total_views, ID, post_title FROM wp_views JOIN wp_posts ON (view_post_id = ID AND post_status = 'publish') GROUP BY view_post_id

Но я не могу понять, как их объединить. Я даже не знаю, будет ли это хорошей идеей, так как запрос может быть очень тяжелым.

Вы рекомендуете использовать два запроса? Если да, то как я могу присоединиться ко второму, так как это в первую очередь мой проблема.

Спасибо!

ОБНОВЛЕНИЕ

Вот дамп SQL.

CREATE TABLE IF NOT EXISTS `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext NOT NULL,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`post_status` varchar(20) NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) NOT NULL DEFAULT 'open',
`ping_status` varchar(20) NOT NULL DEFAULT 'open',
`post_password` varchar(20) NOT NULL DEFAULT '',
`post_name` varchar(200) NOT NULL DEFAULT '',
`to_ping` text NOT NULL,
`pinged` text NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` text NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`guid` varchar(255) NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT '0',
`post_type` varchar(20) NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES
(5, 1, '2010-11-26 16:23:54', '2010-11-26 16:23:54', '', 'fun post #1', '', 'publish', 'open', 'open', '', 'fun-post-1', '', '', '2010-11-26 16:23:54', '2010-11-26 16:23:54', '', 0, 'http://devcake/?p=5', 0, 'post', '', 0),
(7, 1, '2010-11-26 16:24:09', '2010-11-26 16:24:09', '', 'entertainment & fun post', '', 'publish', 'open', 'open', '', 'entertainment-fun-post', '', '', '2010-11-26 16:24:09', '2010-11-26 16:24:09', '', 0, 'http://devcake/?p=7', 0, 'post', '', 0),
(9, 1, '2010-11-26 16:24:24', '2010-11-26 16:24:24', '', 'entertainment & politics', '', 'publish', 'open', 'open', '', 'entertainment-politics', '', '', '2010-11-26 16:24:24', '2010-11-26 16:24:24', '', 0, 'http://devcake/?p=9', 0, 'post', '', 0),
(11, 1, '2010-11-26 16:24:34', '2010-11-26 16:24:34', '', 'gossip', '', 'publish', 'open', 'open', '', 'gossip', '', '', '2010-11-26 16:24:35', '2010-11-26 16:24:35', '', 0, 'http://devcake/?p=11', 0, 'post', '', 0);


CREATE TABLE IF NOT EXISTS `wp_terms` (
`term_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL DEFAULT '',
`slug` varchar(200) NOT NULL DEFAULT '',
`term_group` bigint(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`term_id`),
UNIQUE KEY `slug` (`slug`),
KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;


INSERT INTO `wp_terms` (`term_id`, `name`, `slug`, `term_group`) VALUES
(1, 'Uncategorized', 'uncategorized', 0),
(2, 'Blogroll', 'blogroll', 0),
(3, 'politics', 'politics', 0),
(4, 'economy', 'economy', 0),
(5, 'gossip', 'gossip', 0),
(6, 'entertainment', 'entertainment', 0),
(7, 'fun', 'fun', 0);



CREATE TABLE IF NOT EXISTS `wp_term_relationships` (
`object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`term_order` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES
(1, 2, 0),
(2, 2, 0),
(3, 2, 0),
(4, 2, 0),
(5, 2, 0),
(6, 2, 0),
(7, 2, 0),
(1, 1, 0),
(5, 7, 0),
(7, 7, 0),
(7, 6, 0),
(9, 6, 0),
(9, 3, 0),
(11, 5, 0);



CREATE TABLE IF NOT EXISTS `wp_term_taxonomy` (
`term_taxonomy_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`term_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`taxonomy` varchar(32) NOT NULL DEFAULT '',
`description` longtext NOT NULL,
`parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`count` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`term_taxonomy_id`),
UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
KEY `taxonomy` (`taxonomy`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;


INSERT INTO `wp_term_taxonomy` (`term_taxonomy_id`, `term_id`, `taxonomy`, `description`, `parent`, `count`) VALUES
(1, 1, 'category', '', 0, 0),
(2, 2, 'link_category', '', 0, 7),
(3, 3, 'category', '', 0, 1),
(4, 4, 'category', '', 0, 0),
(5, 5, 'category', '', 0, 1),
(6, 6, 'category', '', 0, 2),
(7, 7, 'category', '', 0, 2);

CREATE TABLE IF NOT EXISTS `wp_views` (
  `view_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `view_post_id` int(10) DEFAULT NULL,
  `view_date` datetime DEFAULT NULL,
  PRIMARY KEY (`view_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;


INSERT INTO `wp_views` (`view_id`, `view_post_id`, `view_date`) VALUES
(1, 11, '2010-11-26 16:28:52'),
(2, 9, '2010-11-26 16:28:56'),
(3, 7, '2010-11-26 16:31:54'),
(4, 7, '2010-11-26 16:31:55'),
(5, 7, '2010-11-26 16:31:56'),
(6, 5, '2010-11-26 16:31:58'),
(7, 5, '2010-11-26 16:31:59'),
(8, 5, '2010-11-26 16:31:59'),
(9, 5, '2010-11-26 16:32:00'),
(10, 5, '2010-11-26 16:32:00');

Подсчет по категориям (первый запрос в вопросе) должен выдавать что-то вроде:

+---------------+-----------+
| name          | num_posts |
|---------------|-----------|
| entertainment |         2 |
|---------------|-----------|
| fun           |         2 |
|---------------|-----------|
| gossip        |         1 |
|---------------|-----------|
| politics      |         1 |
+---------------+-----------+

Количество просмотров должно возвращать что-то вроде

+---------------+-------------+----+--------------------------+
| view_post_id  | total_views | ID | post_title               |
|---------------|-------------|----|--------------------------|
|            5  |           5 |  5 | fun post #1              |
|---------------|-------------|----|--------------------------|
|            7  |           3 |  7 | entertainment & fun post |
|---------------|-------------|----|--------------------------|
|            9  |           1 |  9 | entertainment & politics |
|---------------|-------------|----|--------------------------|
|           11  |           1 | 11 | gossip                   |
+---------------+-------------+----+--------------------------+

И запрос, который я на самом деле ищу, вернет что-то вроде:

+------------+------------+------------+
|    name    |   posts    |   views    |
|------------|------------|------------|
| politics   |          1 |          1 |
|------------|------------|------------|
| gossip     |          1 |          1 |
|------------|------------|------------|
| entertain  |          2 |          4 |
|------------|------------|------------|
| fun        |          2 |          8 |
+------------+------------+------------+

Обратите внимание, что у fun есть два сообщения, одно с 5 просмотрами и одно с 3, и в общей сложности 8 для этой категории.

Спасибо.

Author: skaffman, 2010-11-25
Source

1 answers

Как это работает для вас?

SELECT wp_terms.name,
  COUNT(wp_term_relationships.object_id) AS num_posts,
  SUM(total_views) AS num_views
FROM wp_term_taxonomy
JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
JOIN wp_posts ON (wp_term_relationships.object_id = wp_posts.id AND wp_term_taxonomy.taxonomy='category' AND post_type='post' AND post_status='publish')
JOIN (
       SELECT COUNT(*) as total_views, view_post_id
       FROM wp_views 
       GROUP BY view_post_id
     ) wpv ON wpv.view_post_id=wp_posts.id
GROUP BY wp_terms.term_id
ORDER BY name ASC

Re: Я даже не знаю, будет ли это хорошей идеей, так как запрос может быть очень тяжелым.

Сложность запроса зависит от ваших данных и ваших индексов. Не стесняйтесь обновлять свой пост (или создавать новый) с помощью результирующего EXPLAIN, если выполнение выполняется медленнее, чем допустимо. Во всяком случае, если вы можете кэшировать результаты где-нибудь (таблица отчетов/временная таблица в MySQL, на диске или в memcache) и только периодически вычислять их (и не вычисляйте это каждый раз ), у вас все должно быть в порядке.

 2
Author: Riedsio, 2010-11-25 22:06:16