CakePHP generates wrong query for the count when paginating a custom find

Question

I have this custom find method that is used with pagination as well. But the query it generates is wrong. For some reason it is putting fields into the SELECT for no reason. I don't even specify them in the count part of the find method:

$query['fields'] = array('COUNT(*)');

The error I'm gettign is this:

Error: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous

But Cake generates this query for no obvious reason:

SELECT COUNT(*), Country.id, State.id, ClientStatus.id, SalesStatus.id, id FROM...

What I found out so far is that the query works when I remove the CompanyType from the contain Array. CompanyType is a HABTM associations, I don't apply conditions to that one I just want to return the data. Cake should generate a separate query. However, I don't understand how the idwithout any prefix makes it into the query. :(

When I debug the $query variable I only see the count set, no other fields. Why is Cake adding the other fields here, especially the lonely id? How can I resolve this issue?

The find method:

public function _findSearch($state, $query, $results = array()) {
    $this->Behaviors->unload('Tags.Taggable');
    $this->unbindModel(array(
        'hasAndBelongsToMany' => array(
            'Tag',
            //'CompanyType'
        )
    ), false);

    if ($state === 'before') {
        $query['contain'] = array(
            'Country',
            'State',
            'SalesStatus',
            'ClientStatus',
            'CompanyType',
        );
        $query['fields'] = array(
            $this->alias . '.id',
            $this->alias . '.company',
            $this->alias . '.company2',
            $this->alias . '.company3',
            $this->alias . '.street',
            $this->alias . '.postal_code',
            $this->alias . '.city',
            $this->alias . '.selection_ranking',
            $this->alias . '.role',
            'Country.id',
            'Country.country',
            'State.id',
            'State.name',
            'ClientStatus.id',
            'ClientStatus.name',
            'SalesStatus.id',
            'SalesStatus.name'
        );
        if (isset($query['operation']) && $query['operation'] === 'count') {
            $query['fields'] = array('COUNT(*)');
                                    //debug($query);
        }
        return $query;
    }
    if (($state === 'after') && isset($query['operation']) && $query['operation'] === 'count') {
        return $results[0][0]['COUNT(*)'];
    }
    return $results;
}

floriank floriank · Accepted Answer · 2014-10-15T20:22:35

Try unloading and reloading the containable behavior with autoFields = false.

$this->Behaviors->unload('Containable');
$this->Behaviors->load('Containable', array(
    'autoFields' => false
));

None of the fields besides the COUNT(*) is declared nor needed but containable adds them. So far no issue. The issue is that it adds the "id" without prefix which then causes the SQL error.

From it's documentation:

autoFields: (boolean, optional) auto-add needed fields to fetch requested bindings. DEFAULTS TO: true

The workaround is to reload the behavior with autoFields false. This seems to be a core bug... :(

CakePHP generates wrong query for the count when paginating a custom find

1 Answers