In CakePhp, how can I retrieve only one column from my database?

Question

I have a very simple application with 1 table, each row contains a word, it's definition, and an example.

The definition and example fields are varchars(5000).

On every page, I have a sidebar which displays the list of words. After a certain number of words I started getting the following error:

Error: Allowed memory size of 33554432 bytes exhausted

The code in the controller that sets the variable used in the Element:

$this->set('allWords', $this->Word->find('all', array('order' => array('Word.text ASC'))));

I suspect that the find method reads in all the row data, including the definition and example, which I really don't need at this moment, and this causes the error.

Is there any way to just read the id and the word, and not the definition and example values for each row?

Update

Inside my Element I loop through the $allWords array to print out each word along with a link:

echo '<h3>Words ('.count($allWords).')</h3>';
echo $this->Html->link('Add new', array('controller' => 'words', 'action' => 'add'));
echo '<br/><br/>';

foreach($allWords as $thisWord)
{
    echo $this->Html->link($thisWord['Word']['text'], array('controller' => 'words', 'action' => 'edit', $thisWord['Word']['id']));

    if( ($thisWord['Word']['example'] == '') || ($thisWord['Word']['definition'] == '') )
    {
        echo '&nbsp;' . $this->Html->image('warning.png');
    }
    echo '<br \>';
}

It appears that if I comment out the inner part of the foreach loop, I don't get the memory error.

The SQL output in this case is:

SELECT `Word`.`id` FROM `idioms`.`words` AS `Word` WHERE 1 = 1 ORDER BY `Word`.`text` ASC`

with 339 rows affected.

Thanks!

Answer 1

Pass the fields parameters in your associated array

'fields'=>array('Word.id','Word.word')

can you try this?

$this->set('allWords', $this->Word->find('all', array('order' => array('Word.text ASC'), 'fields'=>array('Word.id','Word.word') )));

for more information http://book.cakephp.org/2.0/en/models/retrieving-your-data.html

Answer 2

Use find('list')

An easy way to get a list of a single field value is to use find('list'):

$values = $this->find('list', array(
    'fields' => array('id', 'text'),
    // ^ what to use for key and value of `$values`
    'order' => Word.text'
));

if text is the displayField for the relevant model, it's not necessary to specify fields at all, as primary key field, and display field are the default values:

$values = $this->find('list', array(
    'fields' => array('id', 'text'),
    'order' => Word.text'
));