The MU forums have moved to WordPress.org

Sort Recent Posts by post-date (ah_recent_posts_mu) (15 posts)

  1. oregondean
    Member
    Posted 15 years ago #

    Greetings,

    I have been tinkering with and learning a lot about WPMU, plugins, and PHP/SQL.

    I started with ah_recent_posts_mu (Thanks Dan and Andrea!), changed it a bit to show date of post, title of post, author's name and the first 65 characters of the tag-stripped post.

    The challenge is that the entries are sorted by the last modification fo the blog, not the date of the post.

    i do not know enough about the array PHP functions to know where to put the sort code.

    Your help is appreciated.

    The plugin starts by setting up

    $blogs = $wpdb->get_col("SELECT blog_id FROM $wpdb->blogs WHERE ... ORDER BY last_updated DESC");

    Then it gathers the post data through two calls to the database
    foreach ($blogs as $blog) {
    ...
    $wpdb->get_results("SELECT option_value FROM $blogOptionsTable WHERE ... ORDER BY option_name DESC");
    $thispost = $wpdb->get_results("SELECT ID, post_title, post_date, post_content, post_author FROM $blogPostsTable WHERE ... ORDER BY post_date DESC LIMIT 0,1");

    What variable do I resort (and how do I do so) at this point before I start echo'ing out the results?

    I'd be happy to post the entire code if necessary. You can see it in operation (and the date mis-sorting) at http://mldfamily.org

    Thanks,

    Dean

  2. oregondean
    Member
    Posted 15 years ago #

    Original source of the plugin:
    http://wiki.evernex.com/index.php?title=Wordpress_MU_sitewide_recent_posts_plugin

    I added post date, author's name, and content of post.

    On the http://MLDfamily.org site the date displayed is the date of the post, while the actual order is the last modified date for the blog. Hence the mismatch.

    There is a bit more aesthetic formatting to do but the meat is there. Fixing the date sorting, eliminating picture tags (see last post), and then breaking the post at a word boundary instead of counting characters are the three key tasks.

  3. azizpoonawalla
    Member
    Posted 15 years ago #

    instead of ORDER BY last_updated, just use ORDER BY post_date. In my version of the plugin, I actually ORDER BY ID instead. Thats basically a surrogate for the post date isnce usually posts are made in sequential order (th eonly time thats not true is if you schedule a post for the future).

    take a look at my code -

    http://wpmudevorg.wordpress.com/project/AHP-Sitewide-Recent-Posts-for-WPMU

  4. azizpoonawalla
    Member
    Posted 15 years ago #

    oh.. wait, I see where I made a mistake in my comment above. The problem is that thefirst sql query is doing the ordering, not the second. The original plugin iterates over each blog first, and then queries the posts for that blog. So to sort all the posts by post date, you'd need to have a version of the plugin that pulls all the most recent N posts from all blogs, not the most recent post from each of N blogs (the latter being how this plugin is structured). I am actually modifying my version of the same plugin to do the latter as an option, but the SQL query is complicated, more so because I don't want to do a brute force query and eat up a lot of database accesses.

    I dont recall the database structure for wp_blogs off the top of my head but I dont think theres any field for the most recent post date therein. Last Date updated is as close as you can probably get, but check the wordpress codex for the database documentation anyway just in case.

    in a nutshell, thogh, theres no easy way to make this change with the existing SQL queries. This is a problem I also need to solve for my wpmu install so I am working on it.

  5. andrea_r
    Moderator
    Posted 15 years ago #

    "you'd need to have a version of the plugin that pulls all the most recent N posts from all blogs, not the most recent post from each of N blogs (the latter being how this plugin is structured)."

    It was done this way to avoid going thru each and every single blog to find the latest post. If we narrow it down to *just* the posts that have been updated, there's less queries.

    If you really want to, since MU does not index all posts etc for large queries, this might be helpful:
    http://premium.wpmudev.org/project/post-indexer

    It's a better start anyway.

  6. azizpoonawalla
    Member
    Posted 15 years ago #

    Andrea,

    you just confirmed what I've been learning about the database structure. I am a newbie to SQL queries so I was wondering if there was just some elegant way to query all posts without it being such a massive load, and I'm somewhat relieved to see there isn't. I am curious about the post indexer project, I guess that would be the best solution (or to use the sitewide post tags blog plugin somehow). Personally, I think the way you originally authored the plugin makes more sense - if we allow N most recent sitewide posts overall, then blogs with frequent updates would drown out ones with fewer. Still, it might be worth doing the plugin anyway as it seems a lot of people want it, and I can use the practice as I learn more about plugin writing.

  7. azizpoonawalla
    Member
    Posted 15 years ago #

    ah, just noticed that the post indexer is premium only. doh...

    actually there is another way to do it (less elegantly, but not as purely brute force). We could retrieve the last M updated posts from each blog on the first query instead of just one, and then sort through those for the most recent N (< M * number of blogs) posts. This might not be fully accurate but its a hybrid approach that will work well enough. the modification to my plugin is pretty straightforward, too. I'll start with that.

  8. oregondean
    Member
    Posted 15 years ago #

    Azizpoonawalla - Thanks for understanding the challenge. I have a modest number of blogs (less than 100) and low visitor counts so brute force is one option for an initial approach to get things going.

    Andrea - Thanks for your response (and your initial work on the plug in). I will pass along my tweaks (when I am done) in case you wish to incorporate any of them.

    I can see how to pull from more blogs initially using the modified date and I am sure could whittle down the list later.

    But under the current plugin structure, my specific question is how to change the last loop to not iterate by modified date, rather to loop by the post-date. I am not up enough on PHP/SQL to create that myself, yet!

    I will look at the premium post-indexer as well.

    Thanks,
    Dean

  9. andrea_r
    Moderator
    Posted 15 years ago #

    We (as in ron and I) did re-work the plugin, but mostly now we parse the sitewide feed to show the most recent posts.

    More reliable, lighter load, no extra db queries.

  10. azizpoonawalla
    Member
    Posted 15 years ago #

    I can see the appeal of the RSS feed over a query, but there are some things like author email (to get the gravatar), number of comments, etc that might be challenging to extract. Are database queries more "expensive" than regular PHP function calls?

  11. oregondean
    Member
    Posted 15 years ago #

    We seem to have strayed and perhaps overcomplicated my question a bit. I am good with the current approach of capturing the most recently modified blogs as an array in $blogs.

    The current code then grabs the info about the most recent post from each of those blogs. That's OK as well.

    What I want to do now is resort the blogs in $blogs by the post date. Can you show me the code to add post_date to the $blogs array and then resort the array on post_date?

    Once that is done the rest of the code runs unmodified.

    Dean

  12. andrea_r
    Moderator
    Posted 15 years ago #

    The post date cannot be added to the blogs query without making it a prohibitively expensive query.

    Look at the sort functions on php.net. To do what you are trying to do you will have to sort the second array.

    Mr. Andrea

  13. azizpoonawalla
    Member
    Posted 15 years ago #

    oregondean, thats the point we are trying to make - the blogs table doesn't have a "post date" because it contains information about blogs, not posts. The post date is only stored in the dedicated tables for each blog. To find the most recent post over all blogs, youd need to query EVERY blog seperately, and find the latest post. The plugin as is, queries the blog db to find the most recently updated blogs, and then only queries that subset to find the most recent posts.

    I suppose you could do a third query to try and JOIN the results of the first two queries to get results you could sort accordingly, but that would also be expensive because you are issuing essentially redundant queries to what came before.

    the modifications that Andrea and I discuss above would work, but neither one is a simple mod to the existing plugin. I will, however, try to implement mine soon. If you're so inclined, voting for my plugin at the WPMU competition (it's labeled "Yet Another Recent Posts Plugin" there) would really help with motivation :)

  14. oregondean
    Member
    Posted 15 years ago #

    azizpoonawalla - I voted for your plug in ;-)

    I understadnd what both you and andrea_r have written. I do not want to doa global search or join. What I would like to do is this ...

    1) Find the most recently modified blogs ... already done (this find sthe most recently modified blogs and not necessarily the most recent blog posts - this is OK given the resource trade off).
    2) Find the most recent 1 post for the blogs found in step #1 ... already done
    3) Insert a new algorithm (that I do not know the syntax to do and am asking for help. I beleive this step requeres next to no resources because it is using the existing two small arrays and does not go back to either the blog or post databases.

    1. Add to $blogs one more field called recent_post_date
    2. Iterate through $blogs and set recent_post_date to the value of post_date in the corresponding post array.
    3. resort $blogs by the new recent_post_date
    4. resume the existing code - the existing code will work unmodified - the only difference is $blogs is sorted by the post date, not the modified date.

    I think this is a fairly simple modification (actually an extension) of the existing code. Perhaps I am still missing something.

    Can you help with code to do the above three steps?

    Thanks,

    Dean

  15. oregondean
    Member
    Posted 15 years ago #

    wow - sorry for the many typos above!

    ds

About this Topic

  • Started 15 years ago by oregondean
  • Latest reply from oregondean