CakePHP: Column not found: 1054 Unknown column

Question

I'm having some trouble finding out where I'm getting this error from:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'HomeVisitorDemographic.Last Name' in 'field list'

firstly $this->set('homeVisitor', $this->HomeVisitorDemographic->find('list')); works fine.

The issue occurs when I try to run:

$this->set('homeVisitor', $this->HomeVisitorDemographic->find('list', array(
    'fields' => array('id','Last Name'),
)));

Now most stuff I've read, will say that the problem is that the 'Last Name' field doesn't exist, which is exactly what the error says. The SQL that cake is trying to execute is the following:

SQL Query: SELECT `HomeVisitorDemographic`.`id`, `HomeVisitorDemographic.Last Name`
    FROM `cake`.`HomeVisitorDemographics` AS `HomeVisitorDemographic` WHERE 1 = 1

And from my database:

mysql> describe HomeVisitorDemographics;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| Last Name        | varchar(70)  | NO   |     | NULL    |                |
| First Name       | varchar(70)  | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

So, I'm really kinda confused why I'm not able to pull the field. Maybe I'm just tired and overlooking something simple? I can see right there that the field does exist. It does pull the Last Name from other pages on the site as well, so I know it exists and the data is in there. My debugging level is set to 2, so the cache should expire after 10 seconds. Any ideas?

Answer 1

Fieldnames with spaces...

are problematic. If you look closely at the sql that's being generated:

SELECT 
    `HomeVisitorDemographic`.`id`, 
    `HomeVisitorDemographic.Last Name`,
FROM
    `cake`.`HomeVisitorDemographics` AS `HomeVisitorDemographic`
WHERE 
    1 = 1

The generated query is looking for a field named HomeVisitorDemographic.Last Name, also as indicated in the error message (though in the error message, it's easy to read it as table.field by mistake).

The 'best' solution in this scenario is to rename the field to something more sensible - such as "last_name".

Escaped values aren't escaped again

If changing the schema isn't possible, an alternative solution is to escape the fieldname yourself:

$this->HomeVisitorDemographic->find('list', array(
    'fields' => array(
        'id',
        '`HomeVisitorDemographic`.`Last Name`' # <- note backticks
    )
));

That should generate the query:

SELECT 
    `HomeVisitorDemographic`.`id`, 
    `HomeVisitorDemographic`.`Last Name`,
FROM
    `cake`.`HomeVisitorDemographics` AS `HomeVisitorDemographic`
WHERE 
    1 = 1