0

I have a custom post type "staff" and each as a custom field "_staff_purpose" (describing its function).

I would like that the archive-staff.php template display all staff members, sorted by their _staff_purpose, so that i can then loop through the result using the _staff_purpose as a heading. The result i'd like to have is like this:

ID  |   post_title    |  _staff_purpose
-----------------------------------------
1   |   Tracy Chap.   |  administrator
-----------------------------------------
2   |   John Doe.     |  teacher
-----------------------------------------
3   |   Robert Smith  |  teacher
-----------------------------------------

The raw SQL should be this:

SELECT * FROM `bj_posts` p

LEFT JOIN (
    SELECT post_id, meta_value as purpose
    FROM `bj_postmeta` pm 
    WHERE  `meta_key`='purpose'
    ) pm ON p.ID=pm.post_id
WHERE p.`post_type`='staff' AND p.`post_status`='publish'
ORDER BY purpose ASC, post_title ASC

I've tried this:

add_action( 'pre_get_posts', 'fetch_staff_people' );

function fetch_staff_people( $query )
{

    if ( is_page_template('archive-staff.php') && $query->is_main_query() )
    {
        $query->set( 'post_per_page', '-1' );
        $query->set( 'meta_key', '_staff_purpose' );
        $query->set('orderby', 'meta_value title'); // sort by purpose, then by staff name.
        $query->set( 'order', 'ASC ASC' );


        } else { 

          return;
        }

    return $query;
}

The query seems to work but the custom field value does not appear in the loop.

  • You need to show us your loop code. – Wyck Aug 28 '14 at 14:19
  • I'm not sure if I understand your question. Do you want to fetch the post meta in the main query so its value is stored in the $post object used in the loop? I think you can't using WP Query, so you can't do it in the preg_get_posts action hook. – cybmeta Aug 28 '14 at 16:36
  • Yes, that's exactly what i want. – pixeline Aug 28 '14 at 16:48
  • To access the meta data use get_post_meta( get_the_ID(), '_staff_purpose', true ); in your loop. Note also the double sorting will work in this instance as you're sorting ASC. – bonger Aug 28 '14 at 20:21
  • @bonger that would trigger a sql query for each entry in the loop. That's what i want to avoid. In the end, i've used a raw sql query with get_results(). – pixeline Aug 28 '14 at 21:02
0

You can't use double sorting option. You can try this code once:

add_action( 'pre_get_posts', 'fetch_staff_people' );
function fetch_staff_people( $query )
{
  if ( is_post_type_archive('staff') && $query->is_main_query() )
  {
    $query->set( 'post_per_page', '-1' );
    $query->set( 'meta_key', '_staff_purpose' );
    $query->set('orderby', 'meta_value_num'); // sort by purpose, then by staff name.
    $query->set( 'order', 'ASC' );
  }
}

Note: I used the is_post_type_archive() function in the conditional tag. It is checking the archive page. See the Codex

|improve this answer|||||
  • the double sorting I can live without. It's the inclusion of the meta_key in the sql results that I'm seeking to know how to do. I've now included the raw SQL that i would like to express in Wordpress' query logic. – pixeline Aug 28 '14 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.