getting result as single dimension array from cakephp query

Question

I am in a situation where in I need to execute some very big queries, 25~30 joins to generate some periodical reports.

Now we already have these queries created and working, i just want to reuse them and thus used cake model's query method.

say my code in model is like:

$this->query(
    'select emp.name,mngr.designation 
     from employee emp,manager mngr 
     where manager.emp_id=emp.id'
)

Result I get back is like:

Array
(
    [0] => Array
        (
            [emp] => Array
            (
                [name] => "Tom"
            )
            [mngr] => Array
            (
                [designation] => "Developer"
            )
        )
    [1] => Array
        (
            [emp] => Array
            (
                [name] => "Thomas"
            )
            [mngr] => Array
            (
                [designation] => "Developer Manager"
            )
        )
)

Is there a way i can get the following plain vanila structure from the cakephp resultset

Array
(
    [0] => Array
        (
            [0]=>"Tom"
            [1]=>"Developer"
        )
    [1] => Array
        (
            [0]=>"Thomas"
            [1]=>"Developer Manager"
        )
)

or associations only at column level but not at table level

Array
(
    [0] => Array
        (
            [name]=>"Tom"
            [designation]=>"Developer"
        )
    [1] => Array
        (
            [name]=>"Thomas"
            [designation]=>"Developer Manager"
        )
)

You've tagged three different Cake versions? Which one are you actually using? — Ben Graham, Oct 11 '12 at 06:29

score 1 · Answer 1 · answered Oct 11 '12 at 06:27

1

Write your query as a View and create a Cake model whose 'table' is that view.

If you can't do this, then you will need to just iterate over your results and turn them into the format you'd like. I think you will find the Set class very helpful here.

answered Oct 11 '12 at 06:27

Ben Graham

2,089
17
21

Thanks Ben, seems like i will need to loop through myself or as you suggested use Set class to restruct the result, only worry is that its a report and looping for a huge dataset will be extra cost given that cake is already doing similar stuff while creating one. – tom _1 Oct 11 '12 at 07:30

score 1 · Accepted Answer · answered Oct 11 '12 at 13:37

1

You can get something similar by using aliases this way:

$this->query(
    'select emp.name AS emp__name, mngr.designation AS mngr__destination 
     from employee emp,manager mngr 
     where manager.emp_id=emp.id'
)

Double underscore __ is important! Take a look also on Sub-queries topic.

answered Oct 11 '12 at 13:37

bancer

7,475
7
39
58

Thanks bancer, I did find out same. it is to do more with virtualFieldSeparator, Will explain same as another answer – tom _1 Oct 11 '12 at 15:10

score 1 · Answer 3 · answered Oct 11 '12 at 15:20

On digging up a bit more into the MySql.php file of Cake framework there is a method resultSet which does all the mapping/associations.

public function resultSet($results) {
    $this->map = array();
    $numFields = $results->columnCount();
    $index = 0;

    while ($numFields-- > 0) {
        $column = $results->getColumnMeta($index);
        if (empty($column['native_type'])) {
            $type = ($column['len'] == 1) ? 'boolean' : 'string';
        } else {
            $type = $column['native_type'];
        }
        if (!empty($column['table']) && strpos($column['name'], $this->virtualFieldSeparator) === false) {
            $this->map[$index++] = array($column['table'], $column['name'], $type);
        } else {
            $this->map[$index++] = array(0, $column['name'], $type);
        }
    }
}

Here statement to note is

if (!empty($column['table']) && strpos($column['name'], $this->virtualFieldSeparator) === false)

so if the column name contains a virtualfieldseparator which by default is __ that column will get associated with index 0 instead of the tablename.

getting result as single dimension array from cakephp query

3 Answers3