Why is filtering by custom country multi-select in a Drupal View not working?

Question

I have a searchable directory listing a group of users, made with a Drupal View. The user profile includes a list of countries where they have relevant work experience, and this data is stored in a custom country multi-select field in Civi.

I want users searching the directory to be able to search based on this country information, and so I have exposed the Views filter to users so that they can select one or more countries and view a list of contacts who have experience in those countries.

Only it doesn't work.

The query that the view generates is:

SELECT civicrm_contact.id AS id, civicrm_contact.first_name AS civicrm_contact_first_name, civicrm_contact.last_name AS civicrm_contact_last_name
FROM 
{civicrm_contact} civicrm_contact
LEFT JOIN {civicrm_value_public_profile_12} civicrm_value_public_profile_12 ON civicrm_contact.id = civicrm_value_public_profile_12.entity_id
WHERE (( (civicrm_value_public_profile_12.countris_88  = 1013) ))

(this example is searching on the country Australia, which has the code 1013). In this instance the query returns no results.

Looking at the database, the column civicrm_value_public_profile_12.countris_88 does include two records that include the country code 1013.

Whatever I put into this search field I get zero results.

The settings.php file has been updated to include reference to the table for Views.

The country codes that are stored in the custom database field are not comma separated in cases where the user has selected more than one country when they created their profile. For example, where a user has selected both Thailand and Australia the field contains: 11211031 rather than, for example 1121,1031. Civi seems happy enough with this, so I'm assuming that the Views handler for this field type is broken?

Answer 1

Views is going to have a tough time handling this field, it generally likes a better table structure. I wouldn't call this a views handler problem, but an implementation problem - using the CiviCRM multi-valued custom field is just going to be problematic for views.

You could try getting views to create a different sql query with a LIKE instead of equal to the country id, but that'll be flaky.

I'd consider instead using a Drupal user field to store that value.