A little boost in retreiving tops

[allenskd]

Well, here is the problem I'm currently having, I want to grab the top authors from custom fields in wordpress, I just used GROUP BY m.meta_value and and I got results (after using COUNT(m.meta_value) as topauthors), then I wanted to add another meta, that is the author's website. And well, group by works, yes, but there is a problem, the IDs won't match because its grouped and neither the ids of the websites. So when I do the loop, there is no ID match because of the differences

 

QUERY to get the tops

SELECT p.ID, p.post_type, p.post_status, m.meta_key, m.meta_value, m.post_id, COUNT(m.meta_value) as topauthors
FROM mywp_posts AS p
INNER JOIN mywp_postmeta AS m ON m.post_id = p.ID
WHERE p.post_type = "post"
AND p.post_status = "publish"
AND m.meta_key = "author"
GROUP BY m.meta_value
ORDER BY topauthors DESC
LIMIT 10

 

Then the websites

SELECT p.ID, p.post_type, p.post_status, m.meta_key, m.meta_value, m.post_id
FROM mywp_posts AS p
INNER JOIN mywp_postmeta AS m ON m.post_id = p.ID
WHERE p.post_type = "post"
AND p.post_status = "publish"
AND m.meta_key = "author_website"

LIMIT 10

 

How can I approach this matter?

[allenskd]

A little more information after reading the guidelines

 

CREATE TABLE `mywp_postmeta` (
`meta_id` bigint(20) NOT NULL AUTO_INCREMENT,
`post_id` bigint(20) NOT NULL DEFAULT '0',
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=86 DEFAULT CHARSET=utf8

 

CREATE TABLE `mywp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) 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_category` int(4) NOT NULL DEFAULT '0',
`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) 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',
`language` text NOT NULL,
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`)
) ENGINE=MyISAM AUTO_INCREMENT=41 DEFAULT CHARSET=utf8

 

sorry for double post

[fenway]

I don't understand your question.

[allenskd]

Alright, well I went with another approach of just creating the two tables, but for the sake of future reference (maybe there is still a solution) I will explain...

 

Well, in wordpress you can set up custom values in posts, so the key was "author" and the value "the author's name", these custom values are stored in mywp_postmeta, the goal was to make a top authors box; now the problem is when creating another key called "author_website" and with its custom value.

 

This created the following situation, I was running the following query:

SELECT p.ID, p.post_type, p.post_status, m.meta_key, m.meta_value, m.post_id, COUNT(m.meta_value) AS top_authors 
FROM mywp_posts AS p 
LEFT JOIN mywp_postmeta AS m ON p.ID = m.post_id
WHERE p.post_status = "publish" AND
p.post_type = "post"
AND m.meta_key = "author"
ORDER BY top_authors
GROUP BY m.meta_value 

(P.S the query might look a bit different because i wrote it from me memory, erased the other query in the files)

 

The other query was similar to that one, with the exception that AND m.meta_key = "author" was AND m.meta_key = "author_website" and it didn't had GROUP BY

 

I went and executed that query in the loop, that is when I found that the IDs weren't matching (there are two queries, one for the authors and another for the websites). Due to the grouping, the IDs only showed the latest of the author, so the mismatch happened in the websites query, there were replicas of the same authors url, and their incremented IDs

 

I did several attempts, in adding GROUP BY in the websites query etc etc, but no avail

 

The expected result is :

Top authors

Author (URL)

 

Wrapping it up of what I was trying to do, I'm trying to get the top authors of posts generated via custom values in wordpress, which I failed in my first attempt. As for the question well.. how do I get the top authors with website using those tables?

 

 

[fenway]

Yes, you can't use GROUP BY and retrieve non-aggregate columns.