Modify main loop query for paged and meta key WordPress

Maor Barazany comments:

I can't use sticky in this case, since I handle this meta_key to show it as an additional column in the edit posts admin panel and I also use some Ajax to let the admin change the status from featured / not featured and so on.
The meta_value is being properly changed in the db, and I want to find a way to use this meta key to implement that.

I don't want to use the sticky in here.

Any other ideas will be thankful.

John Cotton comments:

I think you're on a hiding to nothing with having different queries for different pages. You'd have to store info and pass it between them to get anywhere.

I'd got for a custom SQL statement and a wpdb->get_results, using LIMIT X, Y in the SQL to determine which page content gets displayed.

Then you could order by "has meta/does have meta" followed by whatever else (date? title?) and job will be done!

JC

John Cotton comments:

The code would look something like this:

$pagenum = isset( $_GET['paged'] ) ? absint( $_GET['paged'] ) : 0;

if ( empty($pagenum) ) $pagenum = 1;

$per_page = (int) get_user_option( $edit_per_page );

if ( empty( $per_page ) || $per_page < 1 ) $per_page = 20;


$results = $wpdb->get_results("SELECT p.*, m.meta_value FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} m ON p.id = m.post_id AND m.meta_key = '_pull_leading3' WHERE p.post_status = 'publish' AND p.post_type = 'post' ORDER BY meta_value DESC, post_date DESC LIMIT ". (($pagenum - 1) * $per_page) .", ".$per_page );

post_date DESC

is where you can add/alter whatever other ordering you want.

JC

Maor Barazany comments:

This method doesn't really solve the issue as is.
First, the<strong> $_GET['paged']</strong> appears to be 0 , but this can be managed by using the <strong>global $paged;</strong> variable.

Anyway, I change bit your code and tried using this code -


global $paged;
$pagenum = ( $paged == (int)0 ) ? 1 : absint( $paged ) ;
$per_page = (int) get_option( 'posts_per_page' );
if ( empty( $per_page ) || $per_page < 1 ) $per_page = 7;
$results = $wpdb->get_results("SELECT p.*, m.meta_value FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} m ON p.id = m.post_id AND m.meta_key = '_pull_leading3' WHERE p.post_status = 'publish' AND p.post_type = 'post' ORDER BY meta_value DESC, post_date DESC LIMIT ". (($pagenum - 1) * $per_page) .", ".$per_page );


I get my seven posts, BUT - I get all posts and not only these that they are not flagged as featured, and in the home page I want this loop to retrieve only posts that are not featured, since the featured posts are handled in another loop and they must not be repeated twice in the homepage.
So, I tried to add the meta value to the custom SQL statement


$results = $wpdb->get_results("SELECT p.*, m.meta_value FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} m ON p.id = m.post_id AND m.meta_key = '_pull_leading3' AND m.meta_value = 'off' WHERE p.post_status = 'publish' AND p.post_type = 'post' ORDER BY meta_value DESC, post_date DESC LIMIT ". (($pagenum - 1) * $per_page) .", ".$per_page );


This gave me on the homepage the right posts that are not featured.
But, when going to site.com/page/2, I got the other 7 posts and one post that should be there natively wasn't there since it was featured.

To make it clear, suppose I have 20 posts in my db.
I assign to show 7 posts per page, and also my homepage shows 3 featured posts.
Suppose posts 3,10,11 are marked as "featured"
So I will have these posts displayed in the featured area.
Then, the hompage should show 7 posts - 1,2,4,5,6,7,8 - without 3 - since it is already in the homepage.
Then, page #2 should show 9,10,11,12,13,14,15 - including also posts 10 and 11, since post 10 should be there natively and in the 2nd page we don't see the featured posts anymore... and so on for the other pages.

The problem is that if I use the meta_value as a select filter to the query, I will not get posts 10,11 in the 2nd page.

I hope it is more clear now what I want to achieve..

I will be glad for more possibilities

Maor Barazany comments:

I tried another way, in my leading foreach loop I use this -


$leading = get_posts('showposts=5&meta_key=_pull_leading3&meta_value=on');

foreach ($leading as $post) :

setup_postdata($post);
$ids[] = get_the_ID();
//some code to show posts data

endforeach;


so now I have an array with ID's of the posts that are featured.

Now, in the main loop I tried this -


query_posts(array(
'posts_per_page' => 7,
'paged' => $paged,
'post__not_in' => $ids
));


Now, if posts 3, 10, 11 are marked for example as featured, the main loop (beside the featured loop) in the homepage does exclude post #3 and shows 1,2,4,5,6,7,8
Page 2 does also show 10, 11 in there, but post #8 is also showed in page 2, and it was also in page 1.

Any ideas?

Maor Barazany comments:

<strong>$paged</strong> is a global variable.
I use global $paged and get the value.
for page #1 it is 0 and have to be changed to 1.
I already tried that, unfortunately it's not the solution.

Joachim Kudish comments:

does this help? http://wpseek.com/blog/2011/custom-post-type-pagination/89/

Good luck.