How do I expose the data stored in serialized fashion?

Question

I have a table is the database, structure of which is

function suggestions_schema() {
  $schema['suggested_users'] = array(
    'description' => 'Suggested Users',
    'fields' => array(
      'uid' => array(
        'description' => 'User ID of the user for which suggestions are created.',
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
      ),
      'suggested_uid' => array(
        'description' => 'User Id of the stylist users being suggested.',
        'type' => 'blob',
        'not null' => TRUE,
      ),
    ),
  );
}

uid stores the userID and Suggested UID stores the the uid of the users in serialised fashion.

eg for the same is

uid - 1
suggested_uid - a:3:{i:0;s:3:"287";i:1;s:3:"293";i:2;s:3:"455";}

which means that uid 1 has 3 suggested uid as 287, 293 and 455.

I am exposing the data of this table in views by:

function suggestions_views_data() {
  $table['suggested_users'] = array(
    'table' => array(
      'group' => 'Suggested Users',
        'base' => array(
          'field' => 'uid',
          'title' => 'Suggested Users',
          'help' => 'Suggested Users'
        ),
      ),
      // UID
      'uid' => array(
        'title' => t('User ID'),
        'help' => t('User Id'),
        'field' => array(
          'handler' => 'views_handler_field_numeric',
          'click sortable' => TRUE,
        ),
        'filter' => array(
          'handler' => 'views_handler_filter_numeric',
        ),
        'sort' => array(
          'handler' => 'views_handler_sort',
        ),
        'relationship' => array(
          'base' => 'users',
          'base field' => 'uid',
          'relationship field' => 'uid',
          'handler' => 'views_handler_relationship',
          'label' => t('User Id'),
        ),
        'argument' => array(
          'handler' => 'views_handler_argument_numeric',
        ),
      ),
      // Suggested UID
      'suggested_uid' => array(
        'title' => t('Suggested UID'),
        'help' => t('Suggested UID.'),
        'field' => array(
          'handler' => 'views_handler_field_serialized',
          'click sortable' => TRUE,
        ),
        'filter' => array(
          'handler' => 'views_handler_filter_serialized'
        ),
        'sort' => array(
          'handler' => 'views_handler_sort'
        ),
        'relationship' => array(
          'base' => 'users',
          'base field' => 'uid',
          'relationship field' => 'suggested_uid',
          'handler' => 'suggestions_views_handler_relationship',
          'label' => t('User Id'),
        ),
        'argument' => array(
          'handler' => 'views_handler_argument_serialized',
        ),
      ),
    );
  return $table;
}

The data gets exposed and result in views is like

User ID: 1
Suggested Uid: Array ( [0] => 287 [1] => 293 [2] => 455 )

Now, I want to create a Relationship from the serialised data(Suggested UID) so that I can access the user details(Picture, Name etc) in the views. So in order to do so I have created a relationship handler:

class suggestions_views_handler_relationship extends views_handler_relationship {

  /**
   * Called to implement a relationship in a query.
   */
  function query() {
    // Figure out what base table this relationship brings to the party.
    $table_data = views_fetch_data($this->definition['base']);
    $base_field = empty($this->definition['base field']) ? $table_data['table']['base']['field'] : $this->definition['base field'];

    $this->ensure_my_table();

    $def = $this->definition;
    $def['table'] = $this->definition['base'];
    $def['field'] = $base_field;
    $def['left_table'] = $this->table_alias;
    $def['left_field'] = $this->real_field;
    if (!empty($this->options['required'])) {
      $def['type'] = 'INNER';
    }

    if (!empty($this->definition['extra'])) {
      $def['extra'] = $this->definition['extra'];
    }

    if (!empty($def['join_handler']) && class_exists($def['join_handler'])) {
      $join = new $def['join_handler'];
    }
    else {
      $join = new views_join();
    }

    $join->definition = $def;
    $join->options = $this->options;
    $join->construct();
    $join->adjusted = TRUE;

    // use a short alias for this:
    $alias = $def['table'];

    $this->alias = $this->query->add_relationship($alias, $join, $this->definition['base'], $this->relationship);

    // Add access tags if the base table provide it.
    if (empty($this->query->options['disable_sql_rewrite']) && isset($table_data['table']['base']['access query tag'])) {
      $access_tag = $table_data['table']['base']['access query tag'];
      $this->query->add_tag($access_tag);
    }
  }
}

Now if I add this relationship, the views query becomes:

SELECT suggested_users.uid AS uid, suggested_users.suggested_uid AS suggested_users_suggested_uid
FROM {suggested_users} suggested_users
INNER JOIN {users} users ON suggested_users.suggested_uid = users.uid
WHERE (( (suggested_users.uid = '1' ) ))
LIMIT 5 OFFSET 0

But what I want is that the data in the suggested_uid(data stored in the serialised fashion) should behave as an individual data set. Eg for the same is

Views Row 1

uid - 1

suggested UID - 287

Views Row 2

uid - 1

suggested UID - 293

Views Row 3

uid - 1

suggested UID - 455

One of the way to achieve this is to create a SQL view (https://www.drupal.org/node/2458947).

Can someone please help me to figure out what's the optimal way to achieve this? Is there any other way using which I can show achieve this?

Thanks

Answer 1

But what I want is that the data in the suggested_uid(data stored in the serialised fashion) should behave as an individual data set.

If you're trying to query specific values from a serialized data field, you're going to encounter difficulties when querying for that data.

I'd suggest revising the database schema for your one to many relationship.

For example:

/**
 * Implements hook_schema
 */
function suggestions_schema() {
  $schema['suggested_users'] = array(
    'description' => 'Suggested Users',
    'fields' => array(
      'uid' => array(
        'description' => 'User ID of the user for which suggestions are created.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
      ),
      'suggested_uid' => array(
        'description' => 'User Id of the stylist users being suggested.',
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
    ),
    'primary key' => array('uid', 'suggested_uid'),
    'indexes' => array(
      'uid' => array('uid'),
    ),
    'foreign keys' => array(
      'user' => array(
        'table' => 'users',
        'columns' => array('uid' => 'uid', 'suggested_uid' => 'uid'),
      ),
    ),
  );
}

This is similar to what Drupal core does for the users_roles table, matching UID to RID.

Another perhaps easier way to do it (less custom) is to simply add an entity_reference field on the user account to reference the other "suggested users"