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!

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;`. — Dave, Nov 21 '12 at 03:51
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). — Sandy, Nov 21 '12 at 04:16
Edit the question and add the actual SQL query(s) it's running. Can them directly without error? — Dave, Nov 21 '12 at 04:32
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. — Sandy, Nov 21 '12 at 04:43
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 — Sandy, Nov 21 '12 at 05:04

score 3 · Accepted Answer · edited Mar 06 '14 at 07:27

3

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

edited Mar 06 '14 at 07:27

Sadikhasan

18,365
21
80
122

answered Nov 21 '12 at 01:52

fedmich

5,343
3
37
52

2

Isn't it `'fields' => array('Word.id', 'Word.word')`? – lukedays Nov 21 '12 at 02:05
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 =] – Sandy Nov 21 '12 at 03:46
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. – Sandy Nov 21 '12 at 05:05

score 2 · Answer 2 · answered Mar 06 '14 at 09:01

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

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

2 Answers2

Use find('list')