Fetch user's realname (from Name Field) within database query (or an alternative method)?

Question

I'm building a custom field for a form based off of the values stored in a user reference field. The options for this field are being built from the following database query:

<?php 
// Fetch the users referenced in the provided node
$args = array(':nid' => $nid);
$result = db_query("
    SELECT 
        field_data_field_user_ref.field_user_ref_target_id, 
        users.name
    FROM field_data_field_user_ref
        JOIN users
            ON field_data_field_user_ref.field_user_ref_target_id=users.uid
    WHERE entity_id = :nid", $args);
$arr = $result->fetchAll();

// Add these users as an array to be displayed
$users = array();
foreach ($arr as $val) {
    $crafters[$val->field_user_ref_target_id] = $val->name;
}

...returning the array in the form of...

uid 1 => name 1
uid 2 => name 2
uid 3 => name 3

This works fine, except for the fact that the user's username is displayed instead of their "real name." In my case, the Real Name is being generated by a Name Field (from the Name project). I can't seem to figure out a way to grab the real name from the database. Not sure if it is even stored in there.

Is there an alternative way to grab each user's real name? I'm assuming I could use some application of user_load but I don't know if there's a performance hit associated with loading an unknown number of users one by one just to grab their names. Probably will never be more than a handful of users, but I'd rather keep it optimized.

Answer 1

The Name project used entity fields that can be queried in the DB, just like any other field. All you need to know is the machine name of field that you attached to the user entity. To use your example above:

$result = db_query("
    SELECT 
        field_data_field_user.field_user_target_id, 
        nf.*,
        users.name
    FROM field_data_field_user
        JOIN users
            ON field_data_field_user.field_user_target_id=users.uid
        JOIN field_data_field_MY_NAME_FIELD AS nf
            ON nf.entity_id=users.uid AND nf.entity_type = 'user' AND nf.bundle = 'user'
    WHERE field_data_field_user.entity_id = :nid", $args);

All you need to do is replace MY_NAME_FIELD with the machine name of your actual field. The result will be rows that contain name columns associated with the user reference.