WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Order query by meta_value with multiple custom fields (2 posts)

  1. posykrat
    Member
    Posted 3 years ago #

    hi,

    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 :

    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 !

  2. posykrat
    Member
    Posted 3 years ago #

    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

Topic Closed

This topic has been closed to new replies.

About this Topic