Correct syntax in drupal for pivoting profile_values table?

By fjelds on 20 Apr 2007 at 10:53 UTC

Hi all,

This great forum has provided me with most of my questions; I wish all sites had such great search functionalities. I couldn't however find an answer to this question though: How to pivot the profile_values tables?

Short version: How to pivot the profile_values table so that it's laid out horizontally like this: [ uid | value1 | value2 | ... | valueX ] instead of vertically as it is now?

Long version: I have to build up a table containg information about a user, and only one record should be used pr. user. I need the values in the profile_values table as part of this as it contains demographic information. Therefore I believe I need to pivot the table and have settled for an approach I've used before: "put null in a value field unless it equals an indexing number and finally group by id" (which might not be a good way of doing it, and I welcome suggestions of doing it another way). However, when I put this SQL into a .inc file it just doesn't work and the MySQL error message is of little help saying "refer to the manual". So I was wondering if anyone have already done this (pivoting the profile_values table that is) and would like to share the SQL? This is the SQL statement I think would work:

SELECT uid, group_concat(if(fid = 1, value, null)) AS Birthday,
            group_concat(if(fid = 2, value, null)) AS Gender,
            group_concat(if(fid = 3, value, null)) AS `Job Title`
FROM profile_values
GROUP BY uid;

But how to represent this in Drupal? Probably like this but I'm not sure:

SELECT p.uid, group_concat(if(p.fid = 1, p.value, null)) Birthday,
            group_concat(if(p.fid = 2, p.value, null)) Gender,
            group_concat(if(p.fid = 3, p.value, null)) `Job Title`
FROM {profile_values} p
GROUP BY p.uid;

Thanks for any help and I hope it'll help others too.

Comments

"provided me with most of my

fjelds commented 20 April 2007 at 11:13

"provided me with most of my questions" should read "provided me with answers to most of my questions" :-)

Here's the solution. It's a

fjelds commented 20 April 2007 at 20:34

Here's the solution. It's a bit of hard coding, if I have the need then one day I will generize it (or if someone asks for it).

Lets assume you have bday, gender, country, state and city data collected in your profile_values table like this:

[ uid | fieldname | value ]
[  1  |    bday   |   x1  ]
[  1  |   gender  |   x2  ]
[  1  |  country  |   x3  ]
[  1  |    state  |   x4  ]
[  1  |     city  |   x5  ]
[  2  |    bday   |   y1  ]
[  2  |   gender  |   y2  ]
[  2  |  country  |   y3  ]
[  2  |    state  |   y4  ]
[  2  |     city  |   y5  ]

Then if you want to retrieve this data on a pr. user record basis like this

[ uid | bday   | gender | country | state |  city  ]
[  1  |   x1   |   x2   |    x3   |  x4   |   x5   ]
[  2  |   y1   |   y2   |    y3   |  y4   |   y5   ]

... then you can use the following sql and then just pick out the data from the node.

$query = "
      SELECT   u.uid uid,
               u.name name,
               pv.bday bday,
               pv.gender gender,
               pv.country country,
               pv.state state,
               pv.city city
      FROM     {users} u
      INNER JOIN (
          SELECT p_v.uid uid,
          GROUP_CONCAT(if(p_v.fid = 1, p_v.value, NULL)) bday,
          GROUP_CONCAT(if(p_v.fid = 2, p_v.value, NULL)) gender,
          GROUP_CONCAT(if(p_v.fid = 3, p_v.value, NULL)) country,
          GROUP_CONCAT(if(p_v.fid = 4, p_v.value, NULL)) state,
          GROUP_CONCAT(if(p_v.fid = 5, p_v.value, NULL)) city
          FROM {profile_values} p_v
          GROUP BY p_v.uid) pv ON u.uid = pv.uid
      GROUP BY u.name
  ";

  // Execute the query while limiting the number of results
  $nblimit = 10; // max nodes to show, edit this value
  $result = db_query_range($query, 0, $nblimit);

  // Defines the 3 columns of our table
  $headers = array(
      t('Uid'),
      t('Name'),
      //t('birthday'),
      t('gender'),
      t('country'),
      t('state'),
      t('city')
    );

  // Initializes the rows
  $rows = array();

  // Loop over each found nodes
  while ($node = db_fetch_object($result))
  {
    // Add a row to our table
    $editLink = ($node->uid == $currentUid)?l(t('edit'), 'node/'. $node->nid .'/edit'):'';
    $rows[] = array(
      $node->uid,
      l($node->name, 'user/'. $node->uid),
      //$node->bday,
      $node->gender,
      $node->country,
      $node->state,
      $node->city
    );
  }

It is likely that you have different information stored so what you do is you look at your profile_fields table to see which fid match to which field name and take a note of that. Then in the INNER JOIN SELECT statement you create a GROUP_CONCAT(if(p_v.fid = X, p_v.value, NULL)) fieldname, for each fid (without the last comma for the last GROUP_CONCAT line), where X corresponds to the fid number and the fieldname is the actual name you want to refer to it as.

Please post genericised PIVOT query

jleflar23 commented 6 July 2010 at 19:53

I'm very curious how you would genericize this statement to expand regardless of the number of additional fields. I currently have 19 custom profile fields (and growing). Also, I have several early users who did not have to fill in the full profile information, and the only way to get them to show up is to use a LEFT JOIN.