create an optgroup with cakephp 3.x

i'm novice in cakephp, and i'm having dificulties in something like this:

starting with this sql query:

select service_group.name as service_group_name, service_group.id as service_group_id, campaign.name as campaign_name, campaign.id as campaign_id        from group_service        join group_service_campaign on group.service.id = group_service_campaign.group_service_id        join campaign on group_service_campaign.campaign_id = campaign.id        where campaign.active = 'Y'        and campaign.id in ($in)        and campaign.company_id = ${_SESSION['companyId']}        order by group_service.name, campaign.name

In result, i have something like this:

service_group_name | service_group_id | campaign_name | campaign_id |
service_1 | 01 | travel to sicilia | 14 |
service_1 | 01 | travel to greece | 15 |
service_2 | 02 | play the xyz game | 22 |
service_2 | 02 | play the return of xyz game | 234 |

and so on....

I converted it to this in my controller:

$var_campaign = $this->loadModel('Service_Group');  $var_campaign = $this->Group_Service->find('list', ['keyfield' => 'campaign_id', 'valueField' => 'campaign_name', 'groupField' => 'service_group_name'])              ->join(['table' => 'service_group_campaign',                  'type' => 'inner',                  'conditions' => 'Service_Group.id = service_group_campaign.service_group_id'              ])              ->join(['table' => 'campaign',          'type' => 'inner',          'conditions' => 'service_group_campaign.campaign_id = campaign.id'      ]);      $var_campaign->where(['campaign.active' => 'Y']);      $var_campaign->order(['grou_service.name,campaign.name']);

As can see, one thing i don't found an equivalent AS in querybuilder in cake, this is my first difficult.

My other difficult is to how use this to create an select form element with optgroup.

I've searched for examples and questions similar, but all i tried don't work.

My view:

echo $this->Form->create();
echo $this->Form->input('id', array('label' => 'Services :', 'type' => 'select', 'empty'=> '-- Select --', 'options' => $var_campaign));
echo $this->Form->select("field", $var_campanha);
echo $this->Form->end();



When i use find('list' ...) in query statement, i get a blank response, if i use find('all' ...) i get a not complete response, because all campaign names dont come in response, only the services group names with their id's.

My optgroup show a plain text like:

{"id": -1, "name": "Active Proposals","mod": "W","company_id": 10}

and so on.

Where i'm going wrong ?

thanks in advance.

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at https://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.