Any way to provide custom select query to the loop?

planetthoughtful
(@planetthoughtful)

18 years, 2 months 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

Viewing 15 replies - 1 through 15 (of 36 total)

1 2 3 →

lellie
(@lellie)

18 years, 2 months 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.

Thread Starter planetthoughtful
(@planetthoughtful)

18 years, 2 months 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

Thread Starter planetthoughtful
(@planetthoughtful)

18 years, 2 months ago

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

Kafkaesqui
(@kafkaesqui)

18 years, 2 months 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.

Thread Starter planetthoughtful
(@planetthoughtful)

18 years, 2 months 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

Thread Starter planetthoughtful
(@planetthoughtful)

18 years, 2 months 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

lellie
(@lellie)

18 years, 2 months 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!!!

thedesigncoalition
(@thedesigncoalition)

17 years, 6 months 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?

Chris_K
(@handysolo)

17 years, 6 months 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.

thedesigncoalition
(@thedesigncoalition)

17 years, 6 months 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?

moshu
(@moshu)

17 years, 6 months 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.

thedesigncoalition
(@thedesigncoalition)

17 years, 6 months 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.

moshu
(@moshu)

17 years, 6 months ago

See http://codex.wordpress.org/Template_Hierarchy

thedesigncoalition
(@thedesigncoalition)

17 years, 6 months 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.

Kafkaesqui
(@kafkaesqui)

17 years, 6 months 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;