using WP_Query with custom SQL in wordpress

views:

294

answers:

using WP_Query with custom SQL in wordpress

Hi. I am writing a plugin for wordpress and I want to create my own search. I have tried to alter the wordpress search, but what I am doing is very specific with the SQL query. I am comparing lat and long coordinates and getting posts based on that.

I can display posts by using the standard wpdb query, but then I don't get the other features like paging. I'd like to be able to use my SQL statement with the WP_Query function. If I'm right in thinking, I should then be able to use the paging and other features which come from the $posts global variable.

Is this right?? I've googled for hours but can't find anything for plugins outside of using args to select categories etc. I simply need to send a complete SQL command - nothing else.

Many thanks....

+1 A:

You might not end up with the most optimal of SQL queries, but if you get an array of all the post IDs you want to use (by using your own SQL first), then using WP_Query along with the arguments post__in and posts_per_page, WordPress will handle everything for you (including SQL limits, pagination and so forth).

TheDeadMedic 2010-06-12 12:06:51

thanks for replying... that does sound like it would work! My only issue (I think) is that I am doing the database call after the page has loaded. I use a shortcode to load the plugin function and this then does the query. I guess if I change the SQL query to a header function that would work?

Matt Facer 2010-06-12 12:40:22

Are you using the shortcode in a single page, or a post that'll be inside the loop?

TheDeadMedic 2010-06-12 13:04:19

the short code would be inside a loop. I create a new page and in the body of the content, I put [example]. The example then calls my function which will query the custom SQL

Matt Facer 2010-06-12 13:13:54

In which case you'll have to generate your own pagination (see `paginate_links()`) and feed the current page (most likely from a `$_GET` variable) into WP_Query with the `paged` var.

TheDeadMedic 2010-06-12 18:11:14

I found that I could change from using WP_query and use query_posts($args).. the args I have used are as you suggested, the Post IDS from my first query. This now means the page works with the paging links "out of the box". Thanks :) Do you know if there is any way I can maintain the structure of post IDs? So if I have an array of ids(3,6,9) - when I then use that in the post__in - can it return the posts in that order?

Matt Facer 2010-06-12 19:14:32

I think the best approach would be to filter `posts_orderby` with a function, returning `"FIELD ($wpdb-posts.ID, " . implode(',', $post_IDs) . ")"`, where $post_IDs is the array of your post IDs. See this MySQL thread on ordering using the `IN` clause - http://forums.mysql.com/read.php?97,210905,210918#msg-210918

TheDeadMedic 2010-06-12 20:02:04

fan-bloody-tastic!!! worked a treat that did. Thank you!!

Matt Facer 2010-06-12 20:45:43

No problem ;) Glad it works!

TheDeadMedic 2010-06-12 21:08:10

There's a filter named posts_where_paged that will give you the WHERE portion of the SQL query that's being generated. Modify that with your extra needed SQL comparisons. This way you don't change the way WordPress loads the page, you just change the data that it retrieves from the database.

If the data that you're pulling isn't in the normal set of tables that is queried during search then you will also have to alter the tables being looked at with the posts_join_paged filter. (at least I think it's that one ;) )

Look around line 2376 of wp-includes/query.php to see the filters you can use to modify the db query.

Gipetto 2010-06-12 15:47:16

ansaurus

tags:

views:

answers:

using WP_Query with custom SQL in wordpress

related questions