Return results grouped by table while transferring from php mysql to PDO_mysql drivers

Problem

I'm upgrading an old cakephp 1.3 website to use cake 3.x (and beyond) but its sql calls are not returning results in the same format as they did previously. More specifically, it appears that the cake 1.3 calls group the requested fields by their table name but cake 3 does not (see example below).

Ideally i would like the cake 3.0 results to look identical to the cake 1.3 results so that i don't have to make any significant changes to the code.

Example

The Query (edit: not an actual one, abridged for simplicity):

    $sql =  "SELECT pm.id, pmp.id  
    FROM product_models as pm  
    LEFT JOIN product_model_prices as pmp  
        ON pmp.item = pm.item  
    LIMIT 1";

In cake 1.3: $result = $this->ProductModel->query($sql);

    Array(  
          [0] => Array(  
               [pm]  => Array( [id] => 38354 ),  
               [pmp] => Array( [id] => 599874 )  
        )  
    )

In cake 3.0: $result = ConnectionManager::get('default')->execute($sql)->fetchAll('assoc');

    [  
        (int) 0 => [ 'id' => '599874' ]  
    ]

*note: The missing group-by-table in cake 3 is what i'm trying to resolve. Fixing that will also fix the vanishing id. I should have used a simpler example.

Under the hood Cake 1.3's query() method appears to simply call something along the lines of $dbConnection->query($sql) So my guess is that this has less to do with cake and is instead being done by the mysql driver. Cake 1.3 is using the now defunct mysql driver while cake 3 is using PDO_mysql. To that end i've attempted to use a number of its various fetch modes but none seems to give me what i need.

I also briefly entertained the idea of switching to mysqli but that doesn't appear to be possible in cake 3+ .

Any help would be very much appreciated.

Solution

The issue with your query is that in the result set both pm.id and pmp.id will appear as id, and so when they are written to an associative array the second value will override the first, giving an array with just one id key and value. Note that this behaviour would be identical under any of the mysql, mysqli and PDO drivers. Somewhere under the hood Cake 1.3 must have been rewriting the queries to alias the columns being selected to overcome the problem. You can do something similar with your query to partially workaround the problem. I don't think you'll be able to get the exact same structure, but at least something close:

SELECT pm.id AS pm, pmp.id AS pmp
FROM product_models as pm  
LEFT JOIN product_model_prices as pmp  
     ON pmp.item = pm.item

This will return an array that looks like

[  
    (int) 0 => [ 'pm' => '38354', 'pmp' => '599874' ]  
]

which at least has the same top-level structure.

Note you have a LIMIT clause in your query with no ORDER BY clause. This is essentially meaningless and there is no guarantee that running this query twice will return the same result. See Is it safe to use LIMIT without ORDER BY.