Have Geek Brain

JOIN request edited so that field having unique values

I have a JOIN request that fetches the last 6 "topic" entries (posts with the highest topics that have topic_status 0, that is, not spam):

SELECT topic_title, topic_slug, meta_value, topic_poster
FROM `folio_topics` as Topics
INNER JOIN `folio_topicmeta` as Topicmeta
ON Topics.topic_id = Topicmeta.topic_id
WHERE `topic_status` = 0
AND Topicmeta.meta_key = 'bb_attach_thumb'
ORDER BY Topics.`topic_id` DESC
LIMIT 6

I would like to edit this query so that the folio_topics.topic_poster field is unique i.e. so I don't get 6 posts with the same topic_poster, but 6 posts in total, all of which have a unique topic_poster value.

Any help would be appreciated! :-)

Rejecting Vincent's answer, I came up with a query that correctly pulls the highest topic_id for each topic_poster, but it takes 8.7 seconds, which is extremely slow compared to most queries (which take less than 0.1 seconds). Is there a way to optimize this query?

SELECT topic_title, topic_slug, meta_value, topic_poster
FROM `folio_topics` AS Topics
INNER JOIN `folio_topicmeta` AS Topicmeta
ON Topics.topic_id = Topicmeta.topic_id
WHERE Topics.topic_id IN (
    SELECT MAX(`topic_id`)
    FROM `folio_topics`
    WHERE `topic_status` = 0
    GROUP BY `topic_poster`)
AND Topicmeta.meta_key = 'bb_attach_thumb'
ORDER BY Topics.`topic_id` DESC
LIMIT 6
+1


a source to share


2 answers


In case anyone comes across this, I couldn't find a way to quickly run this huge request, so I split it into two requests in code. Here's the PHP code:

$folio_posters = $wpdb->get_results("SELECT MAX( `topic_id` ) as topiclist FROM `folio_topics` WHERE `topic_status` =0 GROUP BY `topic_poster` ORDER BY topiclist DESC LIMIT 6");

foreach ($folio_posters as $folio_poster) {
    $folio_poster_list[] = $folio_poster->topiclist;
}

$foliopics = $wpdb->get_results("SELECT topic_title, topic_slug, meta_value, topic_poster FROM `folio_topics` AS Topics INNER JOIN `folio_topicmeta` AS Topicmeta ON Topics.topic_id = Topicmeta.topic_id WHERE Topics.topic_id IN (" .  implode ($folio_poster_list, ',') . ") AND Topicmeta.meta_key = 'bb_attach_thumb' ORDER BY Topics.`topic_id` DESC");


If anyone figures out how to do this with a single request, I will agree with this version. :-)

0


a source


According to this article, you can use Group By to accomplish what you are trying. try it



SELECT topic_title, topic_slug, meta_value, topic_poster
FROM `folio_topics` as Topics
INNER JOIN `folio_topicmeta` as Topicmeta
ON Topics.topic_id = Topicmeta.topic_id
WHERE `topic_status` = 0
AND Topicmeta.meta_key = 'bb_attach_thumb'
Group By topic_poster
ORDER BY Topics.`topic_id` DESC
LIMIT 6
+1


a source






More articles:


All Articles