Format MySQL Select into Associative Array in CakePHP

Question

I like how CakePHP automatically loops through the results of MySQL queries and formats them in a nice map for you.

Here's a sample query that I'm using:

# Inside some model
return $this->query("

  SELECT 
    Profile.id,
    SUM( IF( HOUR(Log.event_one) > 3, 1, 0 ) ) as EventOne

  FROM profiles Profile
  JOIN logs Log ON Log.id = Profile.log_id

  WHERE Profile.id = {$pUserId}
");

CakePHP would return a map like the following as the result:

array
  0
    array
      'Profile'
          array
            'id' => 23
      '0'
          array
            'EventOne' => 108
  1
    array
      'Profile'
          array
            'id' => 23
      '0'
          array
            'EventOne' => 42
  2
    ...

What I'm trying to do is have the result be something like this:

array
  'Profile'
      array
        'id' => 23

  'Events'
  #   ^   I want to be able to specify this key
      array
        'EventOne' => 108

Any ideas?

Hmm, must be very cakephp related, as I don't know a bare-(my)sql package which would give you such a thing.... — Wrikken, Jan 13 '12 at 17:02
Please can you show your PHP code that executes the query and retrieves the array shown above? — DaveRandom, Jan 13 '12 at 17:09
Oops... You're right. I've been in this project using CakePHP long enough that I hadn't done this sort of thing in plain PHP in a while. The first example query in my original post would definitely not return what I proposed. — rodrigo-silveira, Jan 13 '12 at 17:26

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

You can't do that directly

The top level array keys are derived from the table name which mysql says the field relates to - in your case it's a calculated field and therefore (according to mysql) belongs to no table - hence the 0 array key.

Post processing

What you can do however, is post process the result so that it is the format you want:

public function getStuff() {
    // The query call in the question can very easily be a normal find call
    $return = $this->query("don't use query unless you have no choice");

    foreach($return as &$row) {
        $row['Events'] = $row[0];
        unset($row[0]);
    }

    return $return;
}

Format MySQL Select into Associative Array in CakePHP

1 Answers1

You can't do that directly

Post processing