问题描述:

I'm using CakePHP to query some data from a database. The code was made in the old-fashioned way, doing the query directly from a string:

$query = "SELECT

SUM(IF(p2.status = 'ACTIVE', p2.stock, 0)) AS total_stock,

Product.*,

Brand.*,

Merchant.*,

Item.*,

Tenant.*

FROM

products p2,

products Product

LEFT JOIN

brands Brand ON Product.brand_id = Brand.id

LEFT JOIN

items Item ON Product.item_id = Item.id

LEFT JOIN

merchants Merchant ON Product.merchant_id = Merchant.id,

tenants Tenant

WHERE

Tenant.id = Product.tenant_id

AND Product.id = ?

AND Product.group_hash = p2.group_hash

GROUP BY Product.id";

$product = $this->queryFirst($query, array($id));

The code is working fine, when I dump the $product variable it show an associative array like this:

Array (

[0] => Array

(

[total_stock] => 0

)

[Product] => Array

(

[id] => 23640

[type] => PRODUCT

...

So later I can do someFunc($product['Product']['id'] and it works still fine.

I tried this same code in a recovery machine and now when I dump the results it shows:

Array (

[0] => Array

(

[total_stock] => 0

[id] => 23640

[type] => PRODUCT

...

)

And of course when I do someFunc($product['Product']['id'] in the recovery it shows:

NOTICE: index not found: Product

I checked the versions of CakePHP and PHP in both production and recovery machines. The CakePHP version for both is 2.4.

The version of PHP in production is 5.5.25 and in recovery is 5.5.34, so the recovery version is newer, it shouldn't be a problem, right?

The version of MySQL are

RECOVERY: mysql Ver 14.14 Distrib 5.5.49, for debian-linux-gnu (x86_64) using readline 6.2

and

PRODUCTION: mysql Ver 14.14 Distrib 5.5.42, for debian-linux-gnu (x86_64) using readline 6.3

I'm using the same Git branch on both to be sure. I believe the problem could be in one of the files not versioned, so I checked also the core.php config files for both but there are no relevant differences.

Any ideas?

UPDATE:

The queryFirst function is defined like this:

function queryFirst($query, $params = array())

{

$data = $this->query($query, $params);

if (isset($data[0])) {

return $data[0];

}

return $data;

}

网友答案:

Different versions of MySQL

Although I can't find a specific reference, this has come up before and is almost certainly down to the version of MySQL.

Why does it happen

This behavior is down to the way CakePHP asks mysql "what table does this field belong to?" for result sets, to determine the array keys used - and the answer varies depending on the version of MySQL. In 2.4 the relevant method is getColumnMeta, in 1.x it was mysql_fetch_field. This also manifests itself when views are used and MySQL sometimes returns the source table name, sometimes returns the view name and sometimes returns nothing.

With the query in the question the second field in the result set is id (Product id). On one server getColumnMeta returns Product for table, and on the other it returns null, with the end result being that the query's returned array structure differs.

The relevant code is here (what table does this belong to?) and here (reindexing of results to be [table][field] indexed) if you'd like to look around and confirm/deny whether this is the cause in your case.

This undesirable behavior is also the main reason why CakePHP stopped doing that in 3.x, and always uses queries of the form:

SELECT Foos.id AS `Table__field`

Where the field alias determines the array indexes used in the results.

How to fix it

You may be able to affect this by aliasing fields or being explicit with the returned fields (not using .*), maybe.

Alternatively, convert the query to a find('first', $params) call - as CakePHP can consume it's own dogfood :). The query method should be avoided unless absolutely necessary, the existence of queryFirst (and the query in the question) suggests using query unnecessarily.

相关阅读:
Top