WordPress query order posts by meta key value

1 Views

I would like to get ordered all my posts with multiple cutom fields (WordPress 3).
Exemple, I’ve 2 custom couple meta_key/meta_value : 

" order_submenuexpositions / numeric "
" display_submenuexpositions / boolean "

I tried like this, but the result it’s not orderer by meta_value from “order_submenuexpositions” meta_key :

Read More
SELECT * FROM wp_posts 
LEFT JOIN wp_postmeta wpostmetaOrder ON ( wp_posts.ID = wpostmetaOrder.post_id AND wpostmetaOrder.meta_key = 'order_submenuexpositions' )
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
LEFT JOIN wp_term_relationships wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_postmeta.meta_key = 'display_submenuexpositions'
AND wp_postmeta.meta_value = 'true'
AND wp_posts.post_status = 'publish' 
AND wp_posts.post_type = 'post' 
AND wp_term_taxonomy.taxonomy = 'category'
ORDER BY wpostmetaOrder.meta_value ASC,wp_postmeta.meta_value ASC

how can i do it ?

thank you !

ok resolved, it’s because meta_value sql field type is longtext and i tried to order on integer value..it’s doesn’t work.you have to cast type on integer, like this : 

SELECT * FROM wp_posts 
LEFT JOIN wp_postmeta wpostmetaOrder ON ( wp_posts.ID = wpostmetaOrder.post_id AND wpostmetaOrder.meta_key = 'order_submenuexpositions' )
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
LEFT JOIN wp_term_relationships wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_postmeta.meta_key = 'display_submenuexpositions'
  AND wp_postmeta.meta_value = 'true'
  AND wp_posts.post_status = 'publish' 
  AND wp_posts.post_type = 'post' 
  AND wp_term_taxonomy.taxonomy = 'category'
ORDER BY CAST(wpostmetaOrder.meta_value AS SIGNED) ASC,wp_postmeta.meta_value ASC`
Post Views: 1

Related posts

Leave a Reply

1 comment

  1. You can actually do this without using a complex SQL query for future reference.

    Here is an Example:

    $args = array(
'posts_per_page' => 250,
'post_type' => 'books',
'meta_query' => array(

    array(
        'key' => 'author',
        'value' => $author_name,
        'compare' => '=='
    ),

    array(
        'key' => 'publisher_id',
        'value' => $publisher_id,
        'compare' => '!=',
        'type' => 'NUMBER'
    )
)
);

$the_query = new WP_Query( $args );  while ( $the_query->have_posts() ) : $the_query->the_post();

// LOOP GOES HERE

endwhile;

    You can add additional arrays inside “Meta_query” to dig even deeper and you can also learn about the different types of ‘compare’ and ‘type’ options available at the wordpress codex.

    http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters