CakePHP Model-based Array Population for Custom Query Results in PostgreSQL

Question

I'm in the process of migrating a database from MySQL to PostgreSQL and am using CakePHP to access the data. The normal model methods (find, delete, create, etc), seem to work as expected, but when I run custom queries using the query method, it doesn't seem to populate the resulting array how I'd expect.

For example, this code when run with CakePHP with MySQL as the database:

$results = $this->Table1->query('SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 USING (Field)');

Produces the following array

array(
   array('Table1' => array(<Table1Result1>), 'Table2' => array(<Table2Result1>))
   array('Table1' => array(<Table1Result2>), 'Table2' => array(<Table2Result2>))
   ...
   array('Table1' => array(<Table1ResultN>), 'Table2' => array(<Table2ResultN>))       
)

When I run a similar query using PostgreSQL as the database, I get the following array:

array(
   array(0 => array(<Table1and2Result1Combined>))
   array(0 => array(<Table1and2Result2Combined>))
   ...
   array(0 => array(<Table1and2ResultNCombined>))
)

Is there a way to get the PostgreSQL result to be returned in the same way as the MySQL one?

Answer 1

You have to build your query like this:

SELECT
  table1.field1 AS "Table1__field1",
  table1.field2 AS "Table1__field2"
FROM
  table1
;