WordPress.org

Ready to get started?Download WordPress

Forums

Any way to provide custom select query to the loop? (37 posts)

  1. planetthoughtful
    Member
    Posted 8 years ago #

    Hi All,

    I'm building a plugin to provide post tagging for my WP blog (yes, I'm aware plugins already exist for this, I'm doing this more for my own plugin learning curve), and I'm storing the tags via custom fields (ie in wp_postmeta).

    I've created a page based on a template in which I intend to display all posts with a given tag (so, you would click on a tag link in a post, and it should take you to this page with the tag name in the URL).

    I'm trying to work out if there's a way to provide a custom SELECT query to The Loop - something like:

    SELECT p.* FROM wp_posts p, wp_postmeta pm WHERE p.ID = pm.post_id and meta_key='tags' and meta_value='value_of_tag'

    I've spent a couple of hours trying to find something to tell if this is possible or not in the docs, and the few other tag plugins I've found just link to technorati, not to a page where all entries with that tag are displayed.

    Any help appreciated!

    Much warmth,

    planetthoughtful

  2. lellie
    Member
    Posted 8 years ago #

    I've been dying to do this for absolutely ages.. but no one has ever helped me :( It should be easy but it doesn't seem to be.

  3. planetthoughtful
    Member
    Posted 8 years ago #

    @lellie: I think I've figured it out.

    To display posts derived from your own SELECT query, try something like (pseudo code):

    $myposts = $wpdb->get_results("SELECT SOMETHING FROM THE DB", ARRAY_A); // only field really needed is ID from wp_posts

    Then, where you would have your normal loop, use:

    for ($i=0; $i < count($myposts); $i++):
    $post = get_post($myposts[$i][ID], OBJECT);
    setup_postdata($post);

    /* And now just include the template tags for the
    content you want to display as you would in a
    normal loop (ie the_excerpt, the_content,
    the_permalink and so on */

    endfor;

    So, basically the select query goes above the loop, and the code below that replaces the loop.

    Hope this helps.

    Much warmth,

    planetthoughtful

  4. planetthoughtful
    Member
    Posted 8 years ago #

    I should probably think about adding this to the doc wiki. Anyone else think it might be useful information to have over there?

  5. Kafkaesqui

    Posted 8 years ago #

    If you do you might want to simplify it through a mod of the pre-1.5 loop format:

    <?php
    $myposts = $wpdb->get_results("SELECT * FROM $wpdb->posts, $wpdb->postmeta WHERE ID = post_id and meta_key='tags' and meta_value='value_of_tag'");

    if($myposts) : foreach($myposts as $post) : setup_postdata($post);
    ?>

    ...

    <?php endforeach; endif; ?>

    If one selects and stores all post(s) table content in their $myposts array, get_post is not required here.

  6. planetthoughtful
    Member
    Posted 8 years ago #

    Thanks for the tip, Kafkaesqui. I think I will put this on the wiki, if only to save other plugin authors who want to roll their own select queries for post loops a few headaches. I just need to figure out where it needs to go.

    Much warmth,

    planetthoughtful

  7. planetthoughtful
    Member
    Posted 8 years ago #

    Okay, I've created a draft Codex article at http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query

    I'll leave it in draft for a couple of days in case anyone has any comments / edits / changes they'd like me to make, and if I don't get any feedback, I'll move it over to the 'New page created' category for Codex editors to review.

    Much warmth,

    planetthoughtful

  8. lellie
    Member
    Posted 8 years ago #

    ooo.. I presume this doesn't have to just be used on pages but could be used in many many places.. I've asked for help with this before and every time I've had no response! I shall have a play with it later! THANKYOU!!!

  9. thedesigncoalition
    Member
    Posted 7 years ago #

    Hi Everyone,
    I am using this method on my archive pages and it is working great (I am ordering my posts by rating). The only problem is that no matter which category I go to, It displays all the posts. Is there something I can change or add to only show the current category?

  10. Chris_K
    Member
    Posted 7 years ago #

    Sounds like your theme uses index.php for all views. Check out Template_Hierarchy -- you may want to create a category.php or achive.php template page.

  11. thedesigncoalition
    Member
    Posted 7 years ago #

    I might not understand you correctly but...

    My theme is the default theme and yes, index.php does display all posts and that is fine. but my problem is that i have made a custom archive.php with a custom query in the loop and it is displaying all posts and not just the certain category. I am not sure but I think it has something to do with the lines

    <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>

    because in the original default archive.php it had

    <?php if (is_category()) { $posts = query_posts($query_string . '&orderby=title&order=asc'); } ?>
    <?php while (have_posts()) : the_post(); ?>

    Doesnt the new one need something like if(is_category...?
    I have no idea. Please excuse my ignorance but I am new to wordpress and not a programmer at all, ust a designer.

    ps. if you are reading this Kaf, using a custom query caused your post_image plugin to stop working. It worked before and the reating plugin still works with the new query. any ideas?

  12. moshu
    Member
    Posted 7 years ago #

    I am using this method on my archive pages and it is working great (I am ordering my posts by rating). The only problem is that no matter which category I go to, It displays all the posts.

    The archive Page has nothing to do with the categories. You either go to a category - and view it's "archives", i.e. all the posts in that specific category... or you go to the archive page, which is THE archive of the blog, not of any specific category.

  13. thedesigncoalition
    Member
    Posted 7 years ago #

    Ok well I might have worded that wrong but the problem is still the same. When you click on a link on the menu under "Categories" it brings you to a page listing "Archives" of that category based on the template archive.php.

  14. moshu
    Member
    Posted 7 years ago #

  15. thedesigncoalition
    Member
    Posted 7 years ago #

    moshu, I don't think you get the problem. I read the template heirarchy and I know I can make category.php to be the template instead. I can change the template and customize the template, that is all working great. But ever since I used a custom query, I can't figure out why it is displaying all posts regardless of the category. (and know i don't want to make a custom template for every category)

    Take a look for yourself.
    http://travisdahl.com/wp/archives/category/punk/nofx/

    try clicking on a different category link. and look at which posts show up.

    I want it to work just like the default install. the only reason I edited it is to get it to order by rating and not date or title.

  16. Kafkaesqui

    Posted 7 years ago #

    Change of my $myposts line from above (to provide by category querying):

    $myposts = $wpdb->get_results("SELECT * FROM $wpdb->posts, $wpdb->post2cat, $wpdb->postmeta WHERE ID = $wpdb->postmeta.post_id AND ID = $wpdb->post2cat.post_id AND category_id = $cat AND meta_key='tags' and meta_value='value_of_tag'");

    The global WordPress var $cat is available on any category query, and holds the category ID for the current category.

    ps. if you are reading this Kaf, using a custom query caused your post_image plugin to stop working. It worked before and the reating plugin still works with the new query. any ideas?

    It's due to Post Image expecting to be run in WordPress' standard posts loop (i.e. The Loop). You can alter this by commenting or editing out the following lines at the start of the plugin's post_image() function:

    if(!in_the_loop())
    return;

  17. thedesigncoalition
    Member
    Posted 7 years ago #

    Thanks Kaf!

  18. thedesigncoalition
    Member
    Posted 7 years ago #

    K one more question Kaf, I did what you said and it is working beutifully!

    Now I have another instance of the loop on the same page (a top 10 list) which will do the same exact thing except instead of the current category, I want it to query the parent category of the current.

    Is there a way to do it more locally so it doesnt affect the main one?

    For instance put something like:
    ("category=" . $cat->cat_ID . "&numberposts=10")

    but I don't know where to put that in:
    <?php if ($pageposts): ?>
    <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>

  19. Kafkaesqui

    Posted 7 years ago #

    Well, $cat->cat_ID would hold no value, since $cat is a basic PHP variable holding a single value (the category ID).

    If you want to collect the category parent ID for the current category, you can try this:

    <?php $cat_object = $wp_query->get_queried_object(); ?>

    With that, $cat_object->category_parent will hold what you're looking for.

  20. thedesigncoalition
    Member
    Posted 7 years ago #

    like this?


    <?php $cat_object = $wp_query->get_queried_object("category=" . $cat_object->category_parent . "&numberposts=10"); ?>
    <?php if ($pageposts): ?>
    <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>

    That doesn't seem to work but i am pretty sure thats not right. Is there something I need to change in there?

  21. Kafkaesqui

    Posted 7 years ago #

    <?php
    $cat_object = $wp_query->get_queried_object();
    $pageposts = new WP_Query("category=" . $cat_object->category_parent . "&numberposts=10");
    ?>
    <?php if ($pageposts): ?>
    <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>

  22. Kafkaesqui

    Posted 7 years ago #

    Actually, looking over that I think you'll run into problems. So try a loop like:

    <?php
    $cat_object = $wp_query->get_queried_object();
    if ($cat_object->category_parent) :
    $pageposts = new WP_Query("category=" . $cat_object->category_parent . "&numberposts=10");

    while ($pageposts->have_posts() ) : $pageposts->the_post(); ?>

    ~template tags 'n stuff go here~

    <?php endwhile; endif; ?>

  23. thedesigncoalition
    Member
    Posted 7 years ago #

    Kaf, Does this go before, after, or inside the other loop?

    Right now I put it after to test it, and its not returning the right parent category info. i need the parent of the subcategory I am viewing.

    I can currently get the title of the desired parent by using:
    <?php $cat = get_the_category(); $cat = $cat[0]; echo $cat->cat_name; ?>

    Do i need to apply something similair for that for this loop?

  24. Kafkaesqui

    Posted 7 years ago #

    "Does this go before, after, or inside the other loop?"

    It *is* a loop, meaning what it is you're using at this point -- "I have another instance of the loop on the same page (a top 10 list) which will do the same exact thing except instead of the current category, I want it to query the parent category of the current." -- you would use the above code there as your loop; making sure to replace ~template tags 'n stuff go here~ with something useful, of course.

    I (again) tested locally the code I gave above, and for me it is displaying the posts from the parent category of any child category.

    Finally, I don't know if it's an issue here, but $cat is a global WordPress var and reusing it is considered, at the very least, bad form. I'd recommend a change to your code, like such:

    <?php $thiscat = get_the_category(); $thiscat = $thiscat[0]; echo $thiscat->cat_name; ?>

  25. thedesigncoalition
    Member
    Posted 7 years ago #

    hmm, thats what I thought. Its so weird though because the posts it is displaying is from a totally unrelated category that has absolutely nothing to do with the current page. Maybe it has something to do with my query:

    <?php
    $topposts = $wpdb->get_results("SELECT $wpdb->posts.*, $wpdb->postmeta.meta_key FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id LEFT JOIN $wpdb->post2cat ON $wpdb->post2cat.post_id = $wpdb->posts.ID WHERE $wpdb->post2cat.category_id = '$cat' AND $wpdb->postmeta.meta_key = 'ratings_score' AND $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_date < NOW() ORDER BY $wpdb->postmeta.meta_value DESC", OBJECT);
    ?>
    <div id="top10_list">
    <h2>Top 10
    <?php $thiscat = get_the_category(); $thiscat = $thiscat[0]; echo $thiscat->cat_name; ?> Albums</h2>
    <?php
    $cat_object = $wp_query->get_queried_object();
    if ($cat_object->category_parent) :
    $topposts = new WP_Query("category=" . $cat_object->category_parent . "&numberposts=10");
    while ($topposts->have_posts() ) : $topposts->the_post(); ?>
    <!--content-->
    <?php endwhile; endif; ?>
    </div>

  26. Kafkaesqui

    Posted 7 years ago #

    Oh boy.

    The first, but not most important, issue in your code is: get_the_category() is an 'in The Loop' function, in that it gets its values from the posts, and not the category query, object. Using it as you are might cause unexpected results. You could work strictly off $cat_object from my code and call the category's name from that (echo $cat_object->cat_name), or display the current category's title with the single_cat_title() template tag.

    Now the most important... my stuff from above is screwing you up. Use of category and numberposts as arguments to WP_Query won't work, as they are specific to get_posts() (the correct ones are cat and showposts, respectively). I'm not sure why I didn't see this as I was testing locally, etc. But no excuses, and sorry for the misleading code.

    With that, this version of your second $topposts line *will* work for you:

    $topposts = new WP_Query("cat=" . $cat_object->category_parent . "&showposts=10");

  27. thedesigncoalition
    Member
    Posted 7 years ago #

    Awesome! there we go. Except it is not ordering them according to my query, by rating.

    Also, if this is the parent category I want it to show the top 10 of itself but since that code is for parent, it shows nothing. Is there an if statement we can write or something?

    thanks again for all your help Kaf!

  28. thedesigncoalition
    Member
    Posted 7 years ago #

    This might be an easier solution. Due to the layout it looks like I am going to have to run 2 querys anyway so is there a wp global variable for parent category? because if there is, I can just change $wpdb->post2cat.category_id = '$cat'

    would stil need some kind of if though in case it is the parent

  29. Kafkaesqui

    Posted 7 years ago #

    Ah, missed the part about ordering by rating. For that you would have to go with your original query line and nix the WP_Query call (as well as use of the standard Loop). That sort of sucks, but not completely since it will still work.

    Anyway, here it is wrapped up; it will switch between parent (when on a child) cat and current (when on a parent) cat:

    http://wordpress.pastebin.ca/246228

    Note this outputs the current category name using single_cat_title(). If this is not expected behavior (though it matches the original code) and you want the name of the "top list" category, replace the single_cat_title() template tag with this complicated bit:

    <?php echo $GLOBALS['wp_object_cache']->cache['category'][$querycat]->cat_name; ?>

    If that won't work (not all setups give up the $GLOBALS array), a query will solve things:

    <?php echo $wpdb->get_var("SELECT cat_name FROM $wpdb->categories WHERE cat_ID = '$querycat'"); ?>

    EDIT: Posted before seeing the reply just above it.

  30. Kafkaesqui

    Posted 7 years ago #

    "This might be an easier solution."

    Ok, let's start over and have you provide a blow-by-blow of everything that is truly needed here. Because at this point I've lost the plot.

Topic Closed

This topic has been closed to new replies.

About this Topic