2

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?

2

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.

  • What is it about the table structure that Views doesn't like? Would it be feasible to change CiviCRM to create a table structure that is better suited to the CMSs with which it integrates? – Graham Nov 16 '16 at 11:16
  • 1
    Take a look in multi-valued custom fields, CiviCRM just crams them all in a single field as a blob. Doing multi-value custom fields in an sql-proper way would be non-trivial (it's what Drupal did with custom fields for version 7), and would make the interface more complex as well. As an aside - this is why you should make your values pretty short for multi-valued custom fields, so they don't overflow. – Alan Dixon Nov 16 '16 at 22:21

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.