CakePHP : display data from multiple models

I am working on a service request application on CakePHP 2.7.8. I have to display a list of clients offering requested service in user's area.

For this I have a service_requests table in the database to keep track of requests made by users.

CREATE TABLE `service_requests` (
  `id` char(36) NOT NULL,
  `customer_id` char(36) DEFAULT NULL,
  `customer_address_id` char(36) DEFAULT NULL,
  `service_id` char(36) DEFAULT NULL,
  `service_area_id` char(36) DEFAULT NULL,
  `status_code` int(11) DEFAULT NULL,
  `status` varchar(30) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Note here service_id and service_area_id which are foreign keys of two different models services and service_areas respectively.

services.sql

CREATE TABLE `services` (
  `id` char(36) NOT NULL,
  `title` varchar(45) DEFAULT NULL,
  `description` text,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

and service_areas.sql

CREATE TABLE `service_areas` (
  `id` char(36) NOT NULL,
  `postal_id` char(36) DEFAULT NULL,
  `area_name` varchar(45) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `postal_id_idx` (`postal_id`)
)

I have another table to maintain list of services provided by clients (service providers).

CREATE TABLE `client_services` (
  `id` char(36) NOT NULL COMMENT '      ',
  `client_id` char(36) DEFAULT NULL,
  `service_id` char(36) DEFAULT NULL,
  `charge` float DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id_idx` (`client_id`),
  KEY `service_id_idx` (`service_id`)
)

and another table to maintain list of service areas covered under service providers.

CREATE TABLE `client_service_areas` (
  `id` char(36) NOT NULL,
  `client_id` char(36) DEFAULT NULL,
  `service_area_id` char(36) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id_idx` (`client_id`),
  KEY `service_area_id_idx` (`service_area_id`)
) 

---

serviceReqest model : serviceRequest.php

class ServiceRequest extends AppModel {

    public $displayField = 'status';

/**
 * belongsTo associations
 *
 * @var array
 */
    public $belongsTo = array(
        'Customer' => array(
            'className' => 'Customer',
            'foreignKey' => 'customer_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'CustomerAddress' => array(
            'className' => 'CustomerAddress',
            'foreignKey' => 'customer_address_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'Service' => array(
            'className' => 'Service',
            'foreignKey' => 'service_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'ServiceArea' => array(
            'className' => 'ServiceArea',
            'foreignKey' => 'service_area_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    );
}

On requesting view action by accessing:

http://localhost/service_requests/view/<service_request_id>

it displays result from service_requests

Now what I want to do is to display a list below this view containing client's name who provides requested service in the requested service area (there can be many service providers offering same service in same service area).

This means showing a list of clients who provide services for Carpenter in Ghaziabad

The client's name and other details are fetched from clients table.

This is your data model:

You can retrieve the information you need by joining clients with client_services and client_service_areas, filtering by service_id and service_area_id, and grouping by clients.id.

The following should work:

public function view($id){

    // retrieve ServiceRequest data 
    $serviceRequest = $this->ServiceRequest->findById($id);

    // retrieve Client data        
    $options['joins'] = array(
        array('table' => 'client_services',
            'alias' => 'ClientService',
            'type' => 'INNER',
            'conditions' => array(
                'Client.id = ClientService.client_id',
            )
        ),
        array('table' => 'client_service_areas',
            'alias' => 'ClientServiceArea',
            'type' => 'INNER',
            'conditions' => array(
                'Client.id = ClientServiceArea.client_id',
            )
        ),

    );

    $options['conditions'] = array(
        'ClientService.service_id'=>$serviceRequest['ServiceRequest']['service_id'],
        'ClientServiceArea.service_area_id'=>$serviceRequest['ServiceRequest']['service_area_id']
    );

    $options['group'] = array('Client.id');

    $Client = ClassRegistry::init('Client');
    $clients = $Client->find('all', $options);
    $this->set(compact('serviceRequest','clients'));
}

_{Note: The above code is untested and lacks error handling}