Fetch post meta in the same query as the main loop

Question

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.

Answer 1

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