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!

Caudate

Double check that $this->recursive (in your model) is set to -1. Eg - before your find() in your model, set $this->recursive = -1;, or if you're in your Controller: $this->Word->recursive = -1;. – Privative

Same error! It's weird because I only have around 400 rows. It's hosted on hostdime, so it's not a noob server config error (hopefully). – Caudate

Edit the question and add the actual SQL query(s) it's running. Can them directly without error? – Privative

Just updated the original post. Yes, the SQL can be run directly with no problem. I believe I've narrowed it down to the loop that prints the words out, however I have no idea how I could optimize the loop to use less memory or even if that would help me at all. – Caudate

I have a feeling it was a server side issue (it's a shared hosting environment). It seems to be working ok now, not having changed anything from last time it bombed. Thanks guys – Caudate

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

Mattie

Isn't it 'fields' => array('Word.id', 'Word.word')? – Beery

I'm trying the following and still the same error: $this->set('allWords', $this->Word->find('all', array('order' => array('Word.text ASC'), 'fields'=>array('Word.id')))); If I comment this line out, no problem. I only have like 400 rows.. not millions =] – Caudate

I have a feeling it was a server side issue (it's a shared hosting environment). It seems to be working ok now, not having changed anything from last time it bombed. Thanks guys. I will mark this as the accepted answer, as it did solve what I initially asked. – Caudate

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'
));

Lynnette