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?

Looks like there is a workaround by changing the query so that each column is aliased as 'Table__Field'. The PDOStatement getColumnMeta function for PostgreSQL doesn't return the table name, while MySQL does, which may be causing the problem in the first place. If there is a better real solution, please let me know. — netfire
– netfire, Commented Jul 19, 2012 at 20:16
$this->Table1->query('SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 USING (Field)'); - It'd be in your interest to actually use cake instead of cheating and using query. Query is not intended to be used for queries that can easily be performed using standard methods. Any internal changes to cake (like your use of the Table__Field convention used for deconstructing fields in that driver) will mean your code breaks in the future. — AD7six
– AD7six, Commented Mar 16, 2013 at 17:18

user3610419 · Accepted Answer · 2014-05-07 02:04:10Z

2

You have to build your query like this:

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

answered May 7, 2014 at 2:04

user3610419

313 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taryn East Over a year ago

Hiya, you seem new here. It's great that you've provided a potential answer for this question. Can you also provide a bit of explanation of how your solution solves the problem? You may not know it, but lots of newbies come to the S/O pages looking for solutions to their problems (which may subtly differ from the question above) and want to know whether your solution might help them too. an explanation lets them know as well as teaching them something new :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related