WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] [closed] How to perform this custom query? (10 posts)

  1. antoniowp
    Member
    Posted 2 years ago #

    Hi.

    I'm posting here because only moderators can post in 'Advanced' forum.

    This is my custom query:

    "Fetch the most recent post in category 'Foo' published by the latest 15 authors who have published in category 'Foo'".

    In other words:

    "Fetch the latest 15 posts in category 'Foo' but do not repeat authors, only one post per author".

    Currently I have a similar query: "Fetch latest 15 posts with category 'Foo'". I need the same query but restricting only one post per author.

    This current query is using WP_Query().

    The question is: How can I perform my custom query? Can I use WP_Query() or other function? Will I need $wpdb database class?

    Thank you very much.

  2. I'm posting here because only moderators can post in 'Advanced' forum.

    I'm sure that's not correct but I've moved your post to WP-Advanced.

  3. antoniowp
    Member
    Posted 2 years ago #

    Thank you Jan.

    Clicking on "Add new" in WP-Advaced takes me to the end of the page where I can read:

    "New Topic in this Forum
    Only moderators can post or move things into this forum."

    But no new-topic form is loaded.

  4. Good to know! Hard for me to see that as I get to view and post in all the forums.

    Also my questions are never that advanced. :)

  5. esmi
    Forum Moderator
    Posted 2 years ago #

    Suggestion: Post a new topic in the Hacks forum with a note referencing this topic, then add a modlook tag. All of the forum mods will then get pinged within a few hours or so and one of us can move the new topic into WP-Advanced and close this topic down.

    Does that sound OK?

  6. antoniowp
    Member
    Posted 2 years ago #

    Thank you esmi.

    I'm sorry I don't understand.

    The topic is already in WP-Advanced, thanks to Jan.

    The topic is not about publishing in this forum, but the question about custom query I am asking.

    Anyway for future questions I will consider your comment.

    Thank you.

  7. wspencer
    Member
    Posted 2 years ago #

    Hmmm....I'm wondering if you could do something like this.....

    $used_authors = array();
    
    $the_query = new WP_Query( $args ); 
    
         while ($the_query->have_posts()) : $the_query->the_post();
    
           $this_author = get_the_author_meta( 'ID' );
    
           if ( in_array( $this_author, $used_authors ) ) {
    
              continue;
           }
    
    // All your display/HTML code would go here	
    
    // Right before the end of the loop
    
         $used_authors[] = get_the_author_meta( 'ID' );
    
    endwhile;

    Basically, my thoughts were that before the loop starts, you'd set up an array of authors that have been used already and save it in a variable. In this case, I used the variable $used_authors. Since the loop hasn't started, we want that array to be empty so we just define it as an array.

    Then right after the start of the loop, we grab the ID of the current post's author and save that in a variable called $this_author.

    Now before anything in the loop is being displayed, we want to check out if the author has already had an article used in the current loop. So we'll use an IF statement and the PHP function in_array() to see if the current author ID is somewhere in our $used_authors array. Since this is the first iteration of the loop, the array is empty and our test will be false. This means the loop will go about it's business and display the data from the current post however you have it coded to do so.

    Then right after the current post's data is displayed, we want to add the current post's author ID into our $used_authors array. Once that's finished, the loop starts over we start the process again.

    Now that the $used_authors array has some data in it, there's a chance our IF statement will end up being TRUE. If the current post's author ID is found in our $used_authors array, we'll use the continue command to skip past the display part of the loop and jump right to the next post, where the test is run once again.

    I didn't test this out and there might be an easier way, but that's probably what I'd try. :)

  8. Chase Miller
    Member
    Posted 1 year ago #

    Sorry for the late reply, but if you are still looking for a solution, you can try something like this:

    function cm_getLatest($iCategory = null, $bRestrictByAuthor = true) {
    global $wpdb;
    if (!is_numeric($iCategory)) { return array(); }
    $bRestrictByAuthor = (!$bRestrictByAuthor) ? '' : ' GROUP BY p.post_author';
    $sQuery = sprintf("SELECT * FROM %s p LEFT OUTER JOIN %sterm_relationships r ON r.object_id = p.ID
    LEFT OUTER JOIN %sterm_taxonomy x ON x.term_taxonomy_id = r.term_taxonomy_id
    LEFT OUTER JOIN %sterms t ON t.term_id = x.term_id
    WHERE p.post_status = 'publish' AND t.term_id = '%s'%s ORDER BY p.post_date ASC", $wpdb->posts, $wpdb->prefix, $wpdb->prefix, $wpdb->prefix, $iCategory, $bRestrictByAuthor);
    $aResults = $wpdb->get_results($sQuery);
    return $aResults;
    }

    Usage would be:
    cm_getLatest(CatID, boolean - Limit to 1 post per author)

    To list all posts with unique authors in category 4, you could use:
    print '
    print var_export(cm_getLatest(4, true), true);

    LAMP geek always looking for work,
    Chase C. Miller

  9. chenli
    Member
    Posted 1 year ago #

    Suggestion: Post a new topic in the Hacks forum with a note referencing this topic, then add a modlook tag. All of the forum mods will then get pinged within a few hours or so and one of us can move the new topic into WP-Advanced and close this topic down.
    [link removed]

  10. antoniowp
    Member
    Posted 1 year ago #

    Just in case it helps, this SQL query in $wpdb has been working right:

    <?php
        $postnr = 8; //number of posts
        $catid = 7;  //category of posts to fetch
        $consulta = "
            SELECT MAX($wpdb->posts.ID) AS ID, $wpdb->posts.post_author, $wpdb->posts.post_title
            FROM $wpdb->posts
            INNER JOIN (SELECT MAX($wpdb->posts.post_date) AS fecha, $wpdb->posts.post_author AS autor
            FROM $wpdb->posts
            JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
            JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id
                AND $wpdb->term_taxonomy.taxonomy = 'category'
                AND $wpdb->term_taxonomy.term_id = $catid)
             WHERE $wpdb->posts.post_type = 'post'
                AND $wpdb->posts.post_status = 'publish'
                AND $wpdb->posts.post_date < NOW()
             GROUP BY autor
             ORDER BY fecha DESC
             LIMIT $postnr) AS consulta
        ON $wpdb->posts.post_date = fecha AND $wpdb->posts.post_author = autor AND $wpdb->posts.post_status = 'publish'
        GROUP BY $wpdb->posts.post_date, $wpdb->posts.post_author
        ORDER BY $wpdb->posts.post_date DESC
    "; ?>

    As it is using NOW() SQL function, beware of time change in daylight saving timezones.

    Maybe solutions commented by wspencer and chase also do the job but as this is working it is enough for me. I guess it is a very heave query but I can't see any other simple query.

    The alternative is using a dynamic blogroll.

Topic Closed

This topic has been closed to new replies.

About this Topic