WordPress.org

Ready to get started?Download WordPress

Forums

Custom query + sort by custom fields + pagination (6 posts)

  1. Lista155
    Member
    Posted 4 years ago #

    To get straight to the question:

    What I have is a category called 'hotels', with unlimited posts in it. Each post is one hotel, which may or may not have 'URL' custom field leading to it's web site.
    Now, since those with that custom field are more important, I need to have them displayed first, and only then will the posts without that custom field appear.

    The pagination must work, obviously.

    Since I haven't been able to solve this on my own, I'm asking for your help. To be completely concrete, I haven't been able to come up with the query needed.

    Cheers,
    Lista

  2. MichaelH
    Member
    Posted 4 years ago #

    Can't meet your pagination requirements, but this works for the rest:

    <?php
    $used_ids=array();
    $category = 'Hotel';
    $custom_field = 'URL';
    $cat_id = get_cat_ID($category);
    $args=array(
      'cat' => $cat_id,
      'meta_key'=> $custom_field,
      '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 in category '. $category . ' with custom field ' . $custom_field;
      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
        the_meta();
        $used_ids[]=$my_query->post->ID;
      endwhile;
    }
    wp_reset_query();  // Restore global post data stomped by the_post().
    
    $args=array(
      'cat' => $cat_id,
      'post__not_in'=> $used_ids,
      '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 in category '. $category . ' but without custom field ' . $custom_field;
      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
      the_meta();
      endwhile;
    }
    wp_reset_query();  // Restore global post data stomped by the_post().
    ?>
  3. vtxyzzy
    Member
    Posted 4 years ago #

    Hey MichaelH,

    I can construct a query to get the posts into an array in the correct order, and I can paginate from the array. But I am having problems displaying the posts.

    I tried using setup_postdata() as I loop through the array and it works for the_content(), but fails for the_permalink(), the_tags() and the_meta(). Each of those functions returns the data for the page with the template, not the posts.

    Any suggestions?

  4. MichaelH
    Member
    Posted 4 years ago #

  5. vtxyzzy
    Member
    Posted 4 years ago #

    Well, I don't have a solution. It's just too much coding to reproduce the_permalink(), the_tags(), the_title(), the_title_attribute(), the_time(), the_author(), the_category(), edit_post_link(), and comments_popup_link().

    I was hoping that I was missing something that would let me use those functions in the normal manner. However, for reference, here is the query that retrieves the posts in question:

    $querystr = "
        SELECT wposts.*, wpostmeta.meta_value, if(isnull(wpostmeta.meta_value),1,0) as sorturl
        FROM $wpdb->posts wposts
          JOIN $wpdb->term_relationships wtermrel ON
            (wposts.ID = wtermrel.object_id)
          LEFT JOIN $wpdb->postmeta wpostmeta ON
             (wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'URL')
        WHERE 1=1
        AND wtermrel.term_taxonomy_id = $hotel_taxonomy_id
        AND wposts.post_status = 'publish'
        AND wposts.post_type = 'post'
        AND wposts.post_date < NOW()
        ORDER BY sorturl, UPPER(wposts.post_title)
     ";

    I chose to use $hotel_taxonomy_id hard-coded rather than join an additional table.

  6. vtxyzzy
    Member
    Posted 4 years ago #

    Here is a solution to the problem. It is based on the default theme index.php, so some tweaking may be necessary.

    Feedback is welcome.

Topic Closed

This topic has been closed to new replies.

About this Topic