Select nodes based on entity reference value

Question

I have a content type student, and it has an Entity Reference field named as field_offline_course_reference and a email field field_offline_course_student_ema

I need a list of all emails of students based on field_offline_course_reference value.

How can I change the following code to achieve what I want to do?

$courseinfo = $theCourseId;    
$query = db_select('node', 'n');

$query->join("field_data_field_offline_course_reference", "t", "t.entity_id = n.nid");
$query->fields("n", array("nid"))
    ->condition('n.type', 'students')
    ->condition('t.entity_id', $courseinfo);


$result = $query->execute();
    foreach ($result as $record) {
       var_dump($result);
}

Answer 1

You need to JOIN on nid and vid (entity fields are stored per nid/vid), add a JOIN to your email field table, set the fields to select the email value, and set your WHERE clause to entity reference target.

$query = db_select('node', 'n');
$query->join("field_data_field_offline_course_reference", "t", "t.entity_id = n.nid AND t.revision_id = n.vid");
$query->join("field_data_field_offline_course_student_ema", "e", "e.entity_id = n.nid AND e.revision_id = n.vid");
$query->fields("e", array("field_offline_course_student_ema_value"))
    ->condition('n.type', 'students')
    ->condition('t.field_offline_course_reference_target_id', $courseinfo);