Custom Views handler for multiple fields [#990632]

To merlinofchaos and all others in the drupal community with views handlers skills:

I need to write a very versatile handler for a particular client.

I have a custom table where I store a variety of "settings" and their values. The schema can be thought of as:

pkey    |    nid    |    setting    |    value

The handler I have extends views_handler_field. The options_form is a list of all "settings" as described above:

SELECT `setting` FROM {m29_apply_settings} GROUP BY `setting` ORDER BY `setting`

Then, the view passes in the nid as an argument. I add_where the setting they select in the options_form. The entire handler is here (You can assume every value is a '0' or '1'):

class m29_organization_settings_field extends views_handler_field {
  function query() {
    $this->ensure_my_table();
    
    // Add the field and a where clause depending on the option selected.
    $this->field_alias = $this->query->add_field($this->table_alias, $this->real_field);
    
    $this->query->add_where('m29_apply_settings', $this->table_alias .".setting = '%s'", $this->options['m29_apply_settings']['setting']);
    
    $this->add_additional_fields();
  }
  
  function render($values) {
    $value = $values->{$this->field_alias} .''; // Force a string
    
    if ($value === '0' || $value === '1') {
      return ($value == 0 ? 'No' : 'Yes');  
    }
    
    return check_plain($value);
  }

  function options_form(&$form, &$form_state) {
    parent::options_form($form, $form_state);
    
    $res = db_query("SELECT `setting` FROM {m29_apply_settings} GROUP BY `setting` ORDER BY `setting`");
    while ($rec = db_fetch_object($res)) {
      $options[$rec->setting] = $rec->setting;
    }
    
    $form['m29_apply_settings']['setting'] = array(
      '#type' => 'select',
      '#title' => t('Select a setting'),
      '#options' => $options,
      '#default_value' => $this->options['m29_apply_settings']['setting'],
    );
  }
}

This handler works perfectly when I add a single field to the view, but does not work when I add multiple copies of this same field.

For example, Say the array of settings options was {setting1, setting2, setting3, setting4} and I wanted to add setting1 and setting2 to the view. The way I have implemented it, the views query will join on my custom table (m29_apply_settings), and create a single alias which it then proceeds to compare against the settings values as followed:

WHERE m29_apply_settings.setting = "setting1" 
AND m29_apply_settings.setting = "setting2"

Rather, what I need is to LEFT JOIN my custom settings table for each time this handler is added to the view. This way, I can get a separate alias and have something like followed:

WHERE m29_apply_settings_setting_1.setting = "setting1" 
AND m29_apply_settings_setting_2.setting = "setting2"

For those of you who understand my predicament, can you offer any solutions? For those of you who do not, what other information can I provide that will bring more clarity as to what I am trying to accomplish?

Comments

Comment #1

j.helmer commented 6 December 2010 at 18:28

All -

In the description of the issue, I wrote the following:

"I have a custom table where I store a variety of "settings" and their values. The schema can be thought of as:

pkey&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;nid&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;setting&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;value

I obviously thought html entities would work. For clarity, let me redraft the schema:

PKEY     |     NID     |     SETTING     |     VALUE

Comment #2

dawehner

German

commented 6 December 2010 at 18:56

Status:

Active

» Postponed (maintainer needs more info)

Can you pastebin your hook_views_data definition, too?
There the issue can be fixed.

Comment #3

j.helmer commented 6 December 2010 at 21:16

Here is everything:

In m29_organization.module:

/*
 * Implement hook_views_api().
 */
function m29_organization_views_api() {
  return array(
    'api' => 2.0,
    'path' => drupal_get_path('module', 'm29_organization') . '/includes'
  );
}

In includes/m29_organization.views.inc:

/**
 * Implementation of hook_views_handlers().
 */
function m29_organization_views_handlers() {
  return array(
    'info' => array(
      'path' => drupal_get_path('module', 'm29_organization') .'/includes',
    ),
    'handlers' => array(
      // field
      'm29_organization_settings_field' => array(
        'parent' => 'views_handler_field',
      ),
    ),
  );
}          

/**
 * Implementation of hook_views_data().
 */
function m29_organization_views_data() {
  $data = array();

  // ---------------------
  // settings base table
  // ---------------------
  $data['m29_apply_settings'] = array(
    // Table info.
    'table' => array(
      'group' => t('Custom Application Settings'),
      'base' => array(
        'field' => 'vid',
        'title' => t('Custom Application Settings'),
        'help' => t('Custom settings for the IHR Database Application Suite.'),
        'weight' => 0,
      ),
      'join' => array(
        'node' => array(
          'left_field' => 'nid',
          'field' => 'nid',
        ),
      ),
    ),
  );
  
  $data['m29_apply_settings']['value'] = array(
    'title' => t('Value'),
    'help' => t('The setting\'s value'),
    'field' => array(
      'handler' => 'm29_organization_settings_field',
      'click sortable' => TRUE,
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
  );
  
  
  return $data;
}

Thanks for the quick reply!

Comment #4

dawehner

German

commented 6 December 2010 at 21:31

Status:

Postponed (maintainer needs more info)

» Fixed


  $data['m29_apply_settings'] = array(
    // Table info.
    'table' => array(
      'group' => t('Custom Application Settings'),
      'base' => array(
        'field' => 'vid',
        'title' => t('Custom Application Settings'),
        'help' => t('Custom settings for the IHR Database Application Suite.'),
        'weight' => 0,
      ),
      'join' => array(
        'node' => array(
          'left_field' => 'nid',
          'field' => 'nid',
        ),
      ),
    ),
  );
  
  $data['m29_apply_settings'] = array(
    // Table info.
    'table' => array(
      'group' => t('Custom Application Settings'),
      'base' => array(
        'field' => 'vid',
        'title' => t('Custom Application Settings'),
        'help' => t('Custom settings for the IHR Database Application Suite.'),
        'weight' => 0,
      ),
      'join' => array(
        'node' => array(
          'left_field' => 'nid',
          'field' => 'nid',
          'type' => 'LEFT',
        ),
      ),
    ),
  );

This or 'left' should do it.

Comment #5

j.helmer commented 7 December 2010 at 18:30

@dereine - Thank you again for your efforts, but this does not solve the issue, assuming that I am understanding you correctly.

If I am correct, you are saying that the only change I need to make is to add a 'type' => 'LEFT', to the handler.

However, this will not address my request.

Looking again at this schema:
PKEY | NID | SETTING | VALUE

You will see I am trying to accomplish the following:

WHERE m29_apply_settings_setting_1.setting = "setting1"
AND m29_apply_settings_setting_2.setting = "setting2"

However, using the hook_views_data I have currently implemented, or the one you have offered, the views query is something like this:

SELECT ...........,   
m29_apply_settings.value AS m29_apply_settings_value
FROM node node
LEFT JOIN m29_apply_settings m29_apply_settings ON node.nid = m29_apply_settings.nid
WHERE ..................
AND ((m29_apply_settings.setting = 'setting1') AND (m29_apply_settings.setting = 'setting2'))

Instead, I would need the equivalent of:

SELECT ...........,   
m29_apply_settings_1.value AS m29_apply_settings_value_1,
m29_apply_settings_2.value AS m29_apply_settings_value_2,
FROM node node
LEFT JOIN m29_apply_settings m29_apply_settings_1 ON node.nid = m29_apply_settings_1.nid
LEFT JOIN m29_apply_settings m29_apply_settings_2 ON node.nid = m29_apply_settings_2.nid
WHERE ..................
AND ((m29_apply_settings_1.setting = 'setting1') AND (m29_apply_settings_2.setting = 'setting2'))

Any ideas?

Comment #6

j.helmer commented 7 December 2010 at 22:39

I know there's a way to do it, because CCK does something similar with multi-select CCK fields when you choose a DELTA.

Comment #7

j.helmer commented 7 December 2010 at 23:09

Status:

Fixed

» Active

  function ensure_my_table() {
    $join = $this->get_join();
    if (!isset($join->extra)) {
      $join->extra = array();
    }
    $delta = isset($this->options['setting_name']) ? $this->options['setting_name'] : -1;
    if ($delta != -1) {
      $join->extra[] = array(
        'field' => 'setting',
        'value' => $delta,
        'numeric' => FALSE,
      );
    }

    $this->table_alias = $this->query->add_table($this->table, $this->relationship, $join);
    
    return $this->table_alias;
  }