WordPress.org

Ready to get started?Download WordPress

Forums

Custom author query (11 posts)

  1. Scott Bressler
    Member
    Posted 4 years ago #

    I'm trying to query for all posts by a given user that do not have a certain meta_key. How would I do this join properly?

    Here's my current query in its entirety:

    $attachments = $wpdb->get_results( $wpdb->prepare( "
    			SELECT ID
    			FROM $wpdb->posts
    			WHERE post_author = %d
    				AND post_type = 'attachment'
    			ORDER BY post_date DESC
    			LIMIT 0, 5",
    			$curauth->ID ));

    Now I need to exclude any posts that are by that user that have a meta_key of 'media'. Any suggestions?

    Also, if there's a way to do this using get_posts, please do fill me in! I couldn't get it to work even without filtering out from postmeta using get_posts (but that's for a later topic...).

    Thanks,
    Scott

  2. MichaelH
    Member
    Posted 4 years ago #

    <?php
    //return all posts for author_id=1 with custom field 'cf1', but exclude any posts that have custom key value of cf1_value
    $author_id = 1;
    $args=array(
      'author' => $author_id,
      'meta_value'=> 'cf1_value',
      'meta_key'=>'cf1',
      'meta_compare'=>'!=',
      'post_type' => 'post',
      'post_status' => 'publish',
      'posts_per_page' => -1,
      'caller_get_posts'=> 1
    );
    $my_query = null;
    $my_query = new WP_Query($args);
    if( $my_query->have_posts() ) {
      echo 'List of Posts';
      while ($my_query->have_posts()) : $my_query->the_post(); ?>
        <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
        <?php
      endwhile;
    }
    wp_reset_query();  // Restore global post data stomped by the_post().
    ?>

    Note that code causes this SQL to be used:

    SELECT   wp_posts.* FROM wp_posts
    JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
    WHERE 1=1  AND (wp_posts.post_author = 1)
    AND wp_posts.post_type = 'post'
    AND (wp_posts.post_status = 'publish')
    AND wp_postmeta.meta_key = 'cf1'
    AND wp_postmeta.meta_value != 'cf1_value'
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC
  3. Scott Bressler
    Member
    Posted 4 years ago #

    Thanks for your help, Michael. Unfortunately that doesn't quite work I don't think, because I'm looking for all posts by say, user 1, but not the posts that even have a meta_key of 'cf1'. So if there are three posts by user 1, one of which has a meta_key of 'cf1' (regardless of meta_value), then only two posts should be returned.

    Perhaps there is a good way to do this in one post, but I'm thinking that I might need to get all the posts by user 1, then join the result separating IDs with commas, then get all the posts that have a meta_key of 'cf1' in the ID list that I just created. Then do an array_diff of IDs for the ones without the 'cf1' meta_key.

    Easier way?

  4. MichaelH
    Member
    Posted 4 years ago #

    I was afraid you'd say that ;)

    Could just get all posts for user 1, then in the loop do this:

    <?php
    $cf1='';
    $cf1 = get_post_meta($post->ID, 'cf1', true);
    if (!$cf1){
    //do you post display stuff
    }
    ?>

    Of course if you need proper pagination that wouldn't work.

  5. Scott Bressler
    Member
    Posted 4 years ago #

    Perhaps I'm missing something, and I've never really done pagination before (at the query level, at least), but how would that work/not work with regards to pagination? I tried digging through core earlier to understand how it really does pagination, but I quickly got lost in wp-db.php...

    Also, the downside to that approach is that I'll be hitting the
    database constantly, right?

    I'm going to try my multi-step approach above, though that might very well fail with pagination as well. Will report back here when I've got something.

  6. MichaelH
    Member
    Posted 4 years ago #

    how would that work/not work with regards to pagination

    Because you are filtering "outside" the query.

    Also, the downside to that approach is that I'll be hitting the database constantly, right?

    Not sure how bad it would be...but here's the SQL to get user 2 posts where those posts don't have cf1 as a custom field (didn't test this too much)

    $no_cf1_posts_user2 = $wpdb->get_results("SELECT   wp_posts.* FROM wp_posts
    WHERE 1=1 AND (wp_posts.post_author = 2)
        AND wp_posts.post_type = 'post'
        AND (wp_posts.post_status = 'publish')
        AND wp_posts.ID NOT IN (
        SELECT DISTINCT wp_postmeta.post_id
        FROM wp_postmeta
        WHERE wp_postmeta.meta_key = 'cf1')
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC");
  7. Scott Bressler
    Member
    Posted 4 years ago #

    Wow, thank you - that's almost exactly it. Whew, this is a doozy! Here's your query above touched up a bit and simplified to work seemingly perfectly for my purposes:

    $attachments = $wpdb->get_results( $wpdb->prepare( "
    		SELECT *
    		FROM $wpdb->posts
    		WHERE post_author = %d
    			AND ID NOT IN (
    				SELECT DISTINCT post_id
    				FROM $wpdb->postmeta
    				WHERE meta_key = 'media-credit')
    			AND post_type = 'attachment'
    			OR (post_type = 'post'
    				AND post_parent = '0'
    				AND post_status = 'publish')
    		GROUP BY ID
    		ORDER BY post_date DESC" ,
    		$curauth->ID ));
  8. Scott Bressler
    Member
    Posted 4 years ago #

    Unfortunately, it's not exactly the most optimal query, as you suspected. The much more verbose code that follows is slightly better because it's more specific about which rows in postmeta to look at. In my veryyyy informal speed tests, the following runs about 30% faster:

    /**
     * Joins fields from an array of objects, returning a string with the fields separated by the given separator.
     */
    function joinKeys($toJoin, $key, $separator) {
    	for ($i = 0; $i < count($toJoin); ++$i)
    		$ids .= $toJoin[$i]->$key . ($i < count($toJoin) - 1 ? $separator : '');
    	return $ids;
    }
    
    // media inline with posts
    $all_attachments = $wpdb->get_results( $wpdb->prepare( "
    			SELECT ID
    			FROM $wpdb->posts
    			WHERE post_author = %d
    				AND (post_type = 'attachment'
    					OR (post_type = 'post'
    						AND post_parent = '0'
    						AND post_status = 'publish')
    				)
    			ORDER BY post_date DESC",
    			$curauth->ID ));
    
    $all_ids = joinKeys($all_attachments, 'ID', ',');
    
    $freeform = $wpdb->get_results( "
    			SELECT post_id
    			FROM $wpdb->postmeta
    			WHERE meta_key = 'media-credit'
    				AND post_id IN ($all_ids)" );
    
    $freeform_ids = joinKeys($freeform, 'post_id', ',');
    
    $attachments = $wpdb->get_results( "
    			SELECT *
    			FROM $wpdb->posts
    			WHERE ID IN ($all_ids)
    				AND ID NOT IN ($freeform_ids)
    			ORDER BY post_date DESC" );

    I then use this to display it:

    <?php if ($attachments) : ?>
    <div id="posts-and-media">
    	<h3>Posts and images by <?php echo $curauth->display_name; ?></h3>
    	<ul>
    	<?php foreach ($attachments as $post) : ?>
    		<li>
    			<?php setup_postdata($post);
    			if ( $post->post_type == 'attachment' )
    				echo wp_get_attachment_link($post->ID, 'thumbnail');
    			else
    				echo the_title();
    			?>
    		</li>
    	<?php endforeach; ?>
    	</ul>
    </div>
    <?php endif; ?>

    A) Any suggested optimizations to the above?
    B) Is there a way to take advantage of the Loop instead of my custom looping? I'm not that familiar with how the Loop is made, or thus how to make one myself...

  9. MichaelH
    Member
    Posted 4 years ago #

    a. you're way beyond me now ;)
    b. your setup_post data is fine -- a loop is usually a result of query_posts or new WP_Query

  10. Scott Bressler
    Member
    Posted 4 years ago #

    Is there a way to create a WP_Query from SQL code rather than using an args array?

  11. Mark / t31os
    Moderator
    Posted 4 years ago #

    So you're wanting posts(attachments, or whatever), created by one particular user where a particular meta key does not exist for that post, is that correct?

    This is an example query, but perhaps you can take away something from it..

    SELECT * FROM wp_posts
    WHERE ( 1=1
    AND NOT EXISTS
    ( SELECT * FROM wp_postmeta WHERE meta_key = 'fruit' AND wp_postmeta.post_id = wp_posts.ID )
    AND wp_posts.post_status = 'publish'
    AND wp_posts.post_author = 1
    AND wp_posts.post_type = 'post'
    )
    GROUP by ID
    ORDER by post_date DESC

    ..you'd replace fruit with the appropriate meta key... etc...

    I'm no mysql guru, so i can't say it's 100% perfect, but thought i'd give it a shot..

Topic Closed

This topic has been closed to new replies.

About this Topic