WordPress.org

Ready to get started?Download WordPress

Forums

Question for Advanced Developers (11 posts)

  1. lee_k
    Member
    Posted 7 years ago #

    Here is an advanced challenge that I am struggling with.

    I am developing a plugin which, as part of the functionality, saves user_ids to the wp-postmeta table with post_id. For each post there may be multiple user ids, or none.

    Anyway, what I am trying to achieve is the option of ordering posts by a count of the user_ids in the wp_postmeta table (in descending order).

    The route I have been taking is to use the 'add_filter' hook with 'posts_where', 'posts_join', and 'posts_orderby'. These only kick in if the variable '&show=count' is added the url to index.php.

    However I can't seem to get the query to order by the count of the user_ids.

    If someone out there can point me in the right direction I will be very grateful.

    Cheers

    Lee

  2. mickemus
    Member
    Posted 7 years ago #

    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.

  3. lee_k
    Member
    Posted 7 years ago #

    Thanks for the reply.

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

    Cheers
    Lee

  4. Samuel Wood (Otto)
    Tech Ninja
    Posted 7 years ago #

    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');

  5. davidchait
    Member
    Posted 7 years ago #

    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.

  6. lee_k
    Member
    Posted 7 years ago #

    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

  7. Samuel Wood (Otto)
    Tech Ninja
    Posted 7 years ago #

    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. :)

  8. lee_k
    Member
    Posted 7 years ago #

    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

  9. Samuel Wood (Otto)
    Tech Ninja
    Posted 7 years ago #

    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.

  10. Marcomail
    Member
    Posted 7 years ago #

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

  11. lee_k
    Member
    Posted 7 years ago #

    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

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.