0

I have tow custom fields field_city and field_priority for a user profile. Now I want to fetch the priority for a city like chicago. ( all I want is max priority for a city but first trying to fetch all )

I am trying with following queries but they are not giving me any result, I am getting a blank array. it works when I remove WHERE clause but not with WHERE for a city.

$query = "SELECT p.field_priority_value, p.entity_id, b.field_city_value FROM field_data_field_priority p LEFT JOIN field_data_field_city b on p.entity_id = b.entity_id WHERE b.field_city_value='chicago'";   
    $result = db_query($query);
    $res = $result->fetchAll();

Tried following code with drupal query function.

$query = db_select('field_data_field_priority', 'p');
    $query->join('field_data_field_city', 'd', 'p.entity_id = d.entity_id');
    $query->fields('p');
    $query->condition('d.field_city_value','chicago','=');
    $result = $query->execute();
    $res = $result->fetchAll();
0

In the original query, you're using LEFT JOIN, so that will return results for the priority table, even if there is no successful join to the city table.

To match that query, use ->leftJoin instead of ->join

https://api.drupal.org/api/drupal/includes%21database%21select.inc/function/SelectQuery%3A%3AleftJoin/7.x

Otherwise, your query should be correct and you don't have a priority for chicago.

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.