Question 187562 in Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress CPT MySQL query sorting issue

I am trying to list records from db of a CPT as under:

$paged = ( get_query_var( 'paged' ) ) ? get_query_var( 'paged' ) : 1;   
    
    $taxanomiess = array('featured','mid-range','luxury','budget');
    
    foreach($taxanomiess as $taxanomyy){        
    
        $args = array(
         'post_type' => 'hotels',
         'posts_per_page' => 20,
         'post_status' => 'publish',
         'orderby' => array('menu_order' => 'ASC', 'date' => 'DESC'),
         'order' => 'DESC',
         'paged' => $paged,
         'tax_query' => array(
                            array(
                                'taxonomy' => 'style',
                                'field' => 'slug',
                                'terms' => $taxanomyy,
                                'operator' => 'IN'
                            )
                        ) 
        );   
     
    $loop = new WP_Query( $args );

    ?>              
    <ul class="hotel-list">    
      <?php                 
        
        // Start the loop.
        while ( $loop->have_posts() ) : $loop->the_post();
          get_template_part( 'templates/hotel', 'page' );
        endwhile; // End the loop.

PROBLEM:

I want to list all FEATURED hotels at the TOP of page and then MID RANGE and so on. But this query listing 20 records mixed (‘featured’,’mid-range’,’luxury’,’budget’) but in order as asked on every page. I am expecting to show all FEATURED records in few first pages then MID RANGE On next pages and so on.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you Caleb for your detailed response and time.

    I tried to solve the issue by writing custom query & joins, here is the code successfully running on my website.

    $paged = ( get_query_var( 'paged' ) ) ? get_query_var( 'paged' ) : 1;
$taxonomy_slug = 'taxonomy name';
$post_type = 'CPT name';
$taxonomies = array( 'mid-range', 'luxury', 'budget', 'featured' );

// Retrieve the post IDs using a $wpdb query
$query = $wpdb->prepare("SELECT SQL_CALC_FOUND_ROWS {$wpdb->posts}.ID, {$wpdb->posts}.post_title
FROM {$wpdb->posts}
INNER JOIN {$wpdb->term_relationships} ON ({$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id)
INNER JOIN {$wpdb->term_taxonomy} ON ({$wpdb->term_relationships}.term_taxonomy_id = {$wpdb->term_taxonomy}.term_taxonomy_id)
INNER JOIN {$wpdb->terms} ON ({$wpdb->term_taxonomy}.term_id = {$wpdb->terms}.term_id)
WHERE {$wpdb->posts}.post_type = %s
AND {$wpdb->posts}.post_status = %s
AND {$wpdb->term_taxonomy}.taxonomy = %s
AND {$wpdb->terms}.slug IN ('" . implode("','", $taxonomies) . "')
GROUP BY {$wpdb->posts}.ID
ORDER BY MIN(FIELD({$wpdb->terms}.slug, '" . implode("','", $taxonomies) . "')), {$wpdb->posts}.ID DESC", $post_type, 'publish', $taxonomy_slug);

$post_ids = $wpdb->get_col($query);

$args = array(
    'post_type' => $post_type,
    'posts_per_page' => 20,
    'post_status' => 'publish',
    'post__in' => $post_ids,
    'paged' => $paged,
    'orderby' => 'post__in', // Use custom callback function
    'order' => 'DESC',
);

$loop = new WP_Query($args);

            
// Start the loop.
while ( $loop->have_posts() ) : $loop->the_post();
     // CODE here

    endwhile; // End the loop. ?>


  2. 0

    The simplest way of accomplishing this would be to set a meta key when a term is assigned to a hotel (using the set_object_terms action), with the ordering number in it:

    Featured: 1
Mid-range: 2
Luxury: 3
Budget: 4

    This will allow ordering the query by meta value:

    $loop = new WP_Query( array(
    'meta_key' => '_hotel_ordering',
    'orderby'  => array(
        'meta_value_num' => 'ASC',
        'menu_order'     => 'ASC',
        'date'           => 'DESC',
    ),
) );

    You could accomplish the same functionality by setting the menu_order property to the correct number on term save, but that exposes a change in order too easily IMO.

    Note that this approach means that adding new terms in the future may require updating meta values. For example, if "Premium" is added as the second spot, then existing values greater than 1 will need to be changed to free the use of 2.

    Something like this (untested):

    add_action( 'set_object_terms', static function ( $post_id, $terms, $term_ids, $taxonomy ) : void {

    // Run only if terms are set for 'style' taxonomy.
    if ( 'style' !== $taxonomy ) {
        return;
    }

    $hotel_order = 100;

    // Check for Featured term ID.
    if ( in_array( $featured_term_id, $term_ids ) ) {
        $hotel_order = 1;

    // Check for Mid-range term ID.
    } else if ( in_array( $midrange_term_id, $term_ids ) ) {
        $hotel_order = 2;

    // Check for Luxury term ID.
    } else if ( in_array( $luxury_term_id, $term_ids ) ) {
        $hotel_order = 3;

    // Check for Budget term ID.
    } else if ( in_array( $budget_term_id, $term_ids ) ) {
        $hotel_order = 4;
    }

    update_post_meta( $post_id, '_hotel_ordering', $hotel_order );
}, 10, 4 );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search