I have two tables services
and service_requests
. service_requests
table has foreign key service_id
referencing services
table.
I have to select data from services
and service_requests
where services.id = service_requests.service_id
ORDER BY COUNT(service_requests.service_id) DESC
This is what I'm doing in my controller
$servicesTable = TableRegistry::get('services');
$featuredServices = $servicesTable->find('all')
->select(['ServiceRequests.service_id', 'count' => 'COUNT(ServiceRequests.service_id)'])
->select($servicesTable)
->join([
'table' => 'service_requests',
'alias' => 'ServiceRequests',
'conditions' => ['Services.id' => 'ServiceRequests.service_id'],
])
->group('service_id')
->order(['Count' => 'DESC'])
->limit(10);
$this->set('featuredServices', $featuredServices);
and printing in view as
if (!empty($featuredServices)):
foreach($featuredServices as $service):
echo $service->title;
endforeach;
endif;
But it is not working. Also printing echo $featuredServices;
only prints the sql string SELECT.......
. Both tables are not associated with the controller I'm using in.
EDIT 2
What I want a query like this
SELECT ServiceRequests.service_id AS `ServiceRequests__service_id`, COUNT(ServiceRequests.service_id) AS `count`, Services.id AS `Services__id`, Services.service_category_id AS `Services__service_category_id`, Services.title AS `Services__title`, Services.description AS `Services__description` FROM services Services INNER JOIN service_requests ServiceRequests ON Services.id = ServiceRequests.service_id GROUP BY service_id ORDER BY Count DESC LIMIT 10
This sql query is working fine when running in phpMyAdmin
and this query is generated by debug($featuredServices)
of
$featuredServices = $servicesTable->find('all')
->select(['ServiceRequests.service_id', 'count' => 'COUNT(ServiceRequests.service_id)'])
->select($servicesTable)
->join([
'table' => 'service_requests',
'alias' => 'ServiceRequests',
'conditions' => ['Services.id' => 'ServiceRequests.service_id'],
])
->group('service_id')
->order(['Count' => 'DESC'])
->limit(10);
This is only generating sql query on debug. How can I execute this so that I could get the result instead of sql query.
This can be achieved by the table association
Your ServicesTable :
public function initialize(array $config)
{
parent::initialize($config);
$this->table('services');
$this->displayField('id');
$this->primaryKey('id');
======== use this line ===============
$this->hasOne('Requests'); // for one to one association
======== OR ===============
$this->hasMany('Requests'); // for one to many association
============ more specific ==========
$this->hasMany('Requests', array(
'foreignKey' => 'service_id'
));
}
Your RequestsTable :
public function initialize(array $config)
{
parent::initialize($config);
$this->table('requests');
$this->displayField('id');
$this->primaryKey('id');
========= add this line ============
$this->belongsTo('Services');
========= or more specific ============
$this->belongsTo('Services', array(
'foreignKey' => 'service_id',
'joinType' => 'INNER',
));
}
Now in controller method :
public function test()
{
$this->loadModel('Services');
$query = $this->Services->find('all', array('contain' => array('Requests')))->limit(10);
//debug($query);
$services= $query->toArray();
debug($services);
$this->set('services', $services);
}
for more specific info about find query, please see the link http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html
and more information about table associations, See link: http://book.cakephp.org/3.0/en/orm/associations.html