Can I do a treatement of returning values directly in SQL query

Question

I was wondering if it was possible for a SQL query to return datas using some entries in the SQL. I'm sorry if it's not explicite (not english here, i guess you figured it out), so let's see an example :

$req = $bdd->prepare('SELECT u.u_id, u.u_name, um.um_id, um.um_name, um.um_value
FROM users as u
LEFT JOIN users_metas as um ON um.um_uid = u.u_id');
$prepare->execute();
$datas = $prepare->fetchAll();

So here, i'm gonna have in $datas an array like

$datas = array(
 0 =>
  u_id => 1
  u_name => name
  um_id => 1
  um_name => meta_name1
  um_value => meta_value1
 1 =>
  u_id => 1
  u_name => name
  um_id => 2
  um_name => meta_name2
  um_value => meta_value2
 ...
);

What I would like it's something like :

$datas = array(
 0 =>
  u_id => 1
  u_name => name
  meta_name1 => meta_value1
  meta_name2 => meta_value2
 1 =>
  u_id => an other user
  ...
);

I already have this after I manually take care of $datas, but i was wondering if I could reach this presentation with no treatement, only with SQL ?

I hope you guys will understand me, I'll do my best to be explicite if you have some questions.

Regards.

Answer 1

MySQL solution

Transforming a table-valued expression into a table by converting unique values from one column in the table into multiple columns in the output is known as a pivot. Unlike some other DBMSes, MySQL doesn't have a PIVOT or CROSSTAB function so you can't write a one size fits all solution to this problem.

If you know all the possible values of um_name in advance, you can emulate this behaviour using aggregate functions.

SELECT u.u_id, u.u_name,
MAX(CASE WHEN um_name = 'meta_name1' THEN um_value END) meta_name1,
MAX(CASE WHEN um_name = 'meta_name2' THEN um_value END) meta_name2
FROM users as u
LEFT JOIN users_metas as um ON um.um_uid = u.u_id
GROUP BY u.u_id, u.u_name;

The downside of this solution is that you have to keep adding MAX(CASE WHEN um_name = '...' THEN um_value END) columns to the query for every possible value of um_name.

In theory you could generate this query in PHP by selecting all the distinct um_name values first, but this wouldn't make your code simpler or faster so there's not a lot of point.

PHP solution

As an alternative, it's only a few lines of PHP to convert your current output from fetchAll() into the format you desire.

$out = [];
array_walk($datas, function($v) use(&$out) {
    $out[$v["u_id"]] = (isset($out[$v["u_id"]]) ? $out[$v["u_id"]] : [])
        + ["u_id" => $v["u_id"],
           "u_name" => $v["u_name"],
           $v["um_name"] => $v["um_value"]];
});
$out = array_values($out);

This doesn't set the keys if the record doesn't exist in the users_metas table, so you'll need to check with isset before accessing a particular value if it isn't present for all users.