Question for Advanced Developers

you may have to do your own sql query for this and save it into a parmeter.. then use that parameter and feed it into the hooks. that way you don’t have to bother about the actual url the user is on. I see a lot of plugins going that way to select and order data.

Thanks for the reply.

Can you point any examples of this so I can see how the code looks.

Cheers
Lee

saves user_ids to the wp-postmeta table with post_id. For each post there may be multiple user ids, or none

Is each of these ID’s a separate piece of metadata? That is, does 2 users correspond to two separate “add_meta..” calls?

If so, then you could do a left join of the posts being pulled with the count of the relevant metadata. You add that to the join, then you add something to the order by so as to order the posts correctly.

This is just off the cuff, mind you. Probably won’t work directly, but it might give you some ideas:

function add_my_join($join)
{
$join = $join."LEFT JOIN
(SELECT post_id, count(*) YOUR_COUNT_NAME
FROM $wpdb->postmeta
WHERE meta_key = 'YOUR_META_KEY'
GROUP BY post_id) as YOUR_UNIQUE_IDENTIFER
ON (YOUR_UNIQUE_IDENTIFER.post_id = $wpdb->posts.ID)";
return $join;
}
function add_my_ordering($order)
{
$order = " YOUR_UNIQUE_IDENTIFER.YOUR_COUNT_NAME, ".$order;
return $order;
}
add_filter('posts_join','add_my_join');
add_filter('posts_orderby','add_my_ordering');


by count of user-IDs per post (i.e., post 17 has three different user-IDs…), or by count of posts for each user-ID (i.e., userID 5 has post 17, 25, and 31)?

Otto, reminder that older MySQL didn’t do subqueries (certainly 3.x didn’t), and I don’t know how/if it optimizes the subquery (I guess same as a base query..).

Otherwise, would agree that looks approximately what he’s described.

Otto
Your solution mostly worked. It shows the posts ordered by the count of user_ids in the wp-postmeta table, which is what I was after. However, strangely it also displays an SQL error at the end for the part –
“(SELECT post_id, count(*) YOUR_COUNT_NAME FROM $wpdb->postmeta”

David
I want the count of user-ID’s per post. I know basic MySQL and not really sure what subqueries are. But I am thinking it is related to the part of the query that is getting the error message above.

Thanks for your help guys

However, strangely it also displays an SQL error at the end for the part –
“(SELECT post_id, count(*) YOUR_COUNT_NAME FROM $wpdb->postmeta”

a) You should change all those YOUR* bits I put in there to something unique to your plugin.
b) Telling me it displays an error isn’t particularly helpful unless you also say what the error is. ??

Otto

The YOUR* bits have been changed and the error doesn’t give much information. Here is it below.

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4]
SELECT COUNT(DISTINCT ID) FROM wp_posts LEFT JOIN (SELECT post_id, count(*) user_count FROM wp_postmeta WHERE meta_key = 'votes'

Like I said, the query does work as the posts are displayed in the order I was after. This error message appears after the posts details.

Initially I was thinking that the ‘trouble’ was the subquery but I am running MySQL 4.1.11 (I understand that subqueries feature from 4.1 onwards).

Cheers
Lee

Ahh. That’s when it’s trying to get the next posts link. Okay, clearly there’s a problem there, somehow you need to workout how to add your bits selectively instead. Not sure how to do that, exactly.

otto42 could you develop a custom plugin for me ? I think is simple

Otto

I found this which solves the problem for me. But it doesn’t help for producing a plugin that can be used by the community.

Guess I’ll have to keep on looking.

Lee