3

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

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"

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.