By darrindickey on

I'm using Drupal 7 with the current mySQL version. I've got a custom profile field table that includes an entity_id (which corresponds to the user id) and a value field, which has one of a number of text strings that users can select as a personal interest. The number of strings a user can select for their profile is variable.

I need to query out the logged in user's list of interests, then query out any users who have any matching interests and their user id. Then I need to pull the user name of each result and list with their matching interests. I have a query/subquery script that partially works:

$subquery = db_select('table')                          
    ->fields('table', array('value'))
    ->condition('table.entity_id', $user->uid, '=');

$query = db_select('table', 't')
    ->fields('p', array('entity_id', 'value'))
    ->condition('value', $subquery, 'IN')
    ->condition('entity_id', $user->uid, '<>')
    ->orderBy('entity_id', 'ASC')
    ->groupBy('entity_id');
$result = $query->execute();

$items = array();
			
	foreach ($result as $num) {
		$items[] = array($num->entity_id);
	}

$block['content'] = theme('item_list', array('items' => $items));

This query returns multiple rows:

  • entity_id value
  • entity_id value
  • entity_id value
  • entity_id value

What I need is:

entity_id: value, value, value, value

entity_id: value, value

entity_id: value, value, value

I've searched and searched and cannot figure out how to do this. Can anyone help?

⋅ Categories: Drupal 7.x

Comments

Anonymous’s picture

Anonymous (not verified) commented

I think you'd able to use something like mysql's group_concat function but why do it in the sql at all? You've got the data, albeit in the wrong format, why not just loop through the results and construct an array that fits in with what you're trying to do? Something like this maybe:

$results = $query->execute();
$items = array();
foreach ($results as $result) {
  if (!isset($$items[$result->entity_id])) {
    $items[$result->entity_id] = array();
  }
  $items[$result->entity_id][] = $result->value;
}

Hope that helps

darrindickey’s picture

darrindickey commented

I tried GROUP_CONCAT and kept getting an error, but I may have been using it incorrectly. I'm fairly new at this.

I tried your suggestion like this:

$block['subject'] = t('Users with similar interests');
   				if(user_access('access content')){
					
			    	
			$subquery = db_select('field_data_field_passions')
					  ->fields('field_data_field_passions', array('field_passions_value'))
					  ->condition('field_data_field_passions.entity_id', $user->uid, '=');
					
			$query = db_select('field_data_field_passions', 'p')
				   ->fields('p', array('entity_id', 'field_passions_value'))
				   ->condition('field_passions_value', $subquery, 'IN')
				   ->condition('entity_id', $user->uid, '<>')
				   ->orderBy('entity_id', 'ASC');
			$results = $query->execute();
			
			var_dump ($results);

			$items = array();

			foreach ($results as $result) {
  				if (!isset($items[$result->entity_id])) {
    				$items[$result->entity_id] = array();
  				}
  				$block['content'] = $items[$result->entity_id][] = $result->field_passions_value;
			}

It returns no 'entity_id' and just one of the values. Like it's returning just one row and that's it. Wouldn't I need to loop through the values and then assign them to a distinct return of the entity id? So I'd only get the entity_id once and all of the value rows for it?