2

User fields are:

  field_age (text field)
  field_sex (text field)
  field_vaccinations_taken (node reference field with multiple values, referenced to vaccination content type)

Vaccination content type's fields are:

 field_age_lower
 field_age_upper
 field_for_sex

Previously I had to list users with certain age group and certain sex who were eligible for a particular vaccination. I was using these queries:

$query = db_select('field_data_field_age', 'a');

$query->join('field_data_field_sex', 's', 'a.entity_id = s.entity_id');
$query->join('users', 'u', 's.entity_id = u.uid');
$query->groupBy('a.entity_id');   
$query->fields('u', array('mail'));

    if($sex == "Both"){
    $query->fields('s', array('field_sex_value'));
 }
    else {
    $query->fields('s', array('field_sex_value'))->condition('field_sex_value', $sex,'=');
}

 $query->fields('a', array('field_age_value','entity_id'))
  ->condition('field_age_value', $age_lower,'>=')
  ->condition('field_age_value', $age_upper,'<=');
$result = $query->execute();

This worked fine as the query->join worked good.

But now I need to check one more condition: If user has already taken the vaccination.

since the column entity_id has repeating values in the table field_data_field_vaccinations_taken, I could not use $query->join to join the table . As seen in the table, there are multiple entries for 16, 17 and so on.

enter image description here

Is it still possible to join the table ? If yes, how can I check if the nid exists in field_vaccinations_taken which is a node reference field?

If no, what would be the alternate to achieve this ?

I am new to using queries in drupal. So please forgive if I am going wrong way or missing something.

Would be grateful for any corrections/suggestions. Thank you.

Your Answer

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

Browse other questions tagged or ask your own question.