CakePHP 3 : select data from multiple table

Question

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.

score 0 · Answer 1 · answered Jul 06 '16 at 09:37

0

link the tables together and then access associated data via $service_requests->services.

This is the cleanest and easiest way to do it.

See this for how to link them together :) http://book.cakephp.org/3.0/en/orm/associations.html

answered Jul 06 '16 at 09:37

Valentin Rapp

432
6
11

`ServicesTable` already has `hasMany` association with `ServiceRequests` and `ServiceRequestsTable` has association `belongsTo` with `Servises` – Anuj TBE Jul 06 '16 at 09:47
then you can call it like i said with $service->requests->services without needing $servicesTable = TableRegistry::get('services'); Just get find a request with find() and debug it. you will see the associated objects are in the array. debug($service_req->find()->where(PUTCONDITIONHERE)); – Valentin Rapp Jul 06 '16 at 13:37
I'm using `$servicesTable = TableRegistry::get('Services);` because I'm not using it in `ServicesController.php` I'm using `Services` model in some other Controller which is neither associated with `Services ` nor `ServiceRequests` – Anuj TBE Jul 06 '16 at 15:21
okaya then its alright. It should work tho with my suggestion ! good luck :) – Valentin Rapp Jul 07 '16 at 08:31

score 0 · Accepted Answer · answered Jul 06 '16 at 22:28

0

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

answered Jul 06 '16 at 22:28

Pradeep

9,667
13
27
34

I have to group result by `ServiceRequests.service_id` and sort by `COUNT(ServiceRequests.service_id) DESC` but this is not working when adding `group()` and also ahve to select only those `services` which are in `service_requests` – Anuj TBE Jul 07 '16 at 17:34
try this code : $query = $this->Services->find('all', array('contain' => array('Requests'))) ->group('Requests.service_id') ->order(array('Requests.service_id' => 'DESC')) ->limit(2); – Pradeep Jul 07 '16 at 17:56
this gives error as `Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Requests.service_id' in 'group statement'` **SQL CODE** : `SELECT Services.id AS 'Services__id', Services.service_category_id AS 'Services__service_category_id', Services.title AS 'Services__title', Services.description AS 'Services__description', Services.created AS 'Services__created' FROM services Services GROUP BY Requests.service_id ORDER BY Requests.service_id DESC LIMIT 2` – Anuj TBE Jul 07 '16 at 18:03
make sure the table name and columns name given are correct.Match with your table – Pradeep Jul 07 '16 at 18:09
yes they are correct. also I have to order by `COUNT` of `Requests.service_id` – Anuj TBE Jul 07 '16 at 18:10
try this code : $query = $this->Services->find()->contain(array('Requests' => function ($q) { return $q->select(array('service_count' => $q->func()->count('Requests.service_id'),'id','service_id','created','modified'));})); $query->group('Requests.service_id'); $query->order(array('service_count' => 'DESC')); – Pradeep Jul 07 '16 at 19:34
still same error. I think the error is with `contain` because there is no foreign key of `Requests` in `Services`. Instead `Services` is associated with `hasMany` `Requests` association. What I have to do is to find the invert result of association. – Anuj TBE Jul 08 '16 at 06:00
Please see `Edit 2` in the question. The query I have posted is working in phpmyadmin and I want the same result but the code in controller on debug genereates only query. How could I make it to execute – Anuj TBE Jul 08 '16 at 06:26

score 0 · Answer 3 · answered Jul 08 '16 at 12:02

First of all, if you use select() in a cakePHP query, then find('all') is not necessary, therefore find() should be enough. Then, I think your execution problem could be solved by placing ->execute(); at the end of the query. It is not often used, but it helps sometimes.

CakePHP 3 : select data from multiple table

3 Answers3