CakePHP's list problem

Question

Hi there.

I have this table in my DB:

Group

 - ID-Name
 - 1 -abc
 - 2 -def
 - 3 -ghi

Pages

 - id-group_id-name
 - 1 -1       -home
 - 2 -1       -about us

Now I wanted to make a select box that groups them by 'group' using:

function add() {

$this->set('pages', $this->Page->find('list', array('fields' => array('Page.id', 'Page.name', 'Page.group_id'))));

}

In my add.ctp:

echo $form->input('group_id', array('options' => $pages));

The output:

<select name="data[Page][id]" id="PageId">
<optgroup label="1">
<option value="1">Home</option>
<option value="2">About Us</option>
</optgroup>
</select>

I wanted the optgroup to display the actual group name not the group id like:

<select name="data[Page][id]" id="PageId">
<optgroup label="abc">
<option value="1">Home</option>
<option value="2">About Us</option>
</optgroup>
</select>

I have tried this one:

$this->Page->find('list', array('conditions' => 'Group.id = Page.id', 'fields' => array('Page.id', 'Page.name', 'Group.name')));

But 'Group.id' and 'Group.name' is unknown.

Thanks!

Answer 1

I'm not sure whether your Page and Group models are properly associated:

http://book.cakephp.org/view/78/Associations-Linking-Models-Together

But at the very least, you probably need to load the Group model in the Page controller where you have the $this->Page->find(..) lookup like so:

$this->loadModel('Group');

$this->Page->find('list', array('conditions' => 'Group.id = Page.id', 'fields' => array('Page.id', 'Page.name', 'Group.name')));

Answer 2

Try this:

$this->Page->find('list', array(
    "fields" => array("Page.id", "Page.name", "Group.name"),
    "joins" => array(
        array(
            "table" => "groups",
            "alias" => "Group",
            "type" => "INNER",
            "conditions" => array("Group.id = Page.id")
        )
    ),
    "order" => array(...) // whatever ordering you want
));

Answer 3

also, 'group' => 'Group.name' is another param you can set for find. It will setup a GROUP BY in the SQL.

Answer 4

Took me a while ...

$this->Page->find('list', array('conditions' => 'Group.id = Page.id', 'fields' => array('Page.id', 'Page.name', 'Group.name'),'recursive'=0));

Otherwise find("list") uses recursive = -1 and won't load the group data