How to do a Custom Query for all posts NOT in an array of post ids

  • Resolved cmoloney

    (@cmoloney)


    16 years, 1 month ago

    Hello I am doing a custom query for all posts in a particular category (cat_ID = 329) that have a custom key (‘agendadate’) and then the results are sorted by the values of that custom key.

    I would now like to add a statement so the query omits posts whose ids are listed in a previously created array called $exclude.

    Is this possible?

    Here is what I was thinking for the custom query string (the line in question is the 3rd AND statement):

    $querystr = "
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id = 329
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.ID != $exclude
AND $wpdb->postmeta.meta_key = 'agendadate'
AND $wpdb->postmeta.meta_value >= $today
ORDER BY $wpdb->postmeta.meta_value ASC ";

$show = $wpdb->get_results($querystr, OBJECT);

    The != operator doesn’t seem to work and I don’t really know SQL. It doesn’t work for single post IDs either.

    Any suggestions? Thanks.

Viewing 5 replies - 1 through 5 (of 5 total)

  • what is $exclude? I don’t see a definition for it in there.

    Thread Starter cmoloney

    (@cmoloney)

    $exclude is the arraay of post IDs that I don’t want included in the search results.

    On the first page of the agenda category, I display the latest entries in all of the nine subcategories under agenda. The post IDs of those categories are put into the array called $exclude.

    At the bottom of that first page on all subsequent pages there is a list of more events – agenda posts – ordered by the custom field so the soonest event is listed first.

    I don’t want to repeat the special call out posts in the list. That is why I put their IDs in an array to be filtered from the results.

    I hope that makes sense. Any ideas?

    Well, I can’t see the code you have from the description, but if $exclude is in an array, then it won’t recognize that. An array is a list of key=> value pairs that there would be no post match to (I’ve never seen a single post ID looks like “Array ([0]=>2 [1]=>6)” and so on. Usually it’s just one digit).

    But like I said, you’re not showing the code on how you come up with $exclude – but it sounds like if you print_r(‘exclude’); you ‘ll end up with a list of key=>value pairs 9as mentioned above) – and it won’t exclude them because there’s no match for it.

    You’d have to explode and do a foreach to pull in comparisons to flag the ones you don’t want to include. I’m thinking, though, that a “foreach” might be a bit much for the database query so another method might be better.

    For example eave you the $exclude line, then your query will pull in all the posts using your query. Then you could just use a piece of code in your Loop that would pass by the ID’s you don’t want:

    if(have_post()) : while(have_posts(): the_post();
if(post->ID == '2') continue; // skips over any posts that have the id of "2"
    Thread Starter cmoloney

    (@cmoloney)

    Thanks for your help doodlebee. Based on your suggestion I came up with a solution that works although perhaps its not the prettiest.

    I found that statement
    AND $wpdb->posts.ID !=
    works for single post ids, but it wasn’t working when i tried to feed it the $exclude array. The $exclude array was created by putting in this line of code
    $exclude[i++]=$post->ID;
    after the few feature entry displays at the top of the page.

    So I just created a super long $querystr using a foreach loop as shown here: 

    $excludequerystr = '';
 foreach($exclude as $nodice) { $excludequerystr = $excludequerystr . " AND $wpdb->posts.ID != " . $nodice; } endforech;
 $querystr = "
	SELECT * FROM $wpdb->posts
	LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
	LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
	LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
	WHERE $wpdb->term_taxonomy.term_id = 329
	AND $wpdb->term_taxonomy.taxonomy = 'category'
	AND $wpdb->posts.post_status = 'publish'
	" . $excludequerystr . "
	AND $wpdb->postmeta.meta_key = 'agendadate'
	AND $wpdb->postmeta.meta_value >= $today
	ORDER BY $wpdb->postmeta.meta_value ASC
	LIMIT 10
    ";

 $show = $wpdb->get_results($querystr, OBJECT);

    In case it was confusing you, here is an example of a page I created that has a few feature posts at the top and then the remaining posts listed below and on subsequent pages.

    https://www.theargentimes.com/travel/

    However on that page it was fine to display the results by most recent post id instead of sorting by a custom field.

    At the moment, the page I am working on is not ready, but for those who might be reading this in the future here is the page: https://www.theargentimes.com/agenda/

    hi, thanks for the info! I was able to use the code on this page: https://hotguam.com

    Worked like a charm!

    My only concern is that this code will be obsoleted in future upgrades.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘How to do a Custom Query for all posts NOT in an array of post ids’ is closed to new replies.