title: CakePHP 3 : select data from multiple table
body: <p>I have two tables <code>services</code> and <code>service_requests</code>. <code>service_requests</code> table has foreign key <code>service_id</code> referencing <code>services</code> table.</p>
  
  <p>I have to select data from <code>services</code> and <code>service_requests</code> where <code>services.id = service_requests.service_id</code> <code>ORDER BY COUNT(service_requests.service_id) DESC</code></p>
  
  <p>This is what I'm doing in my controller</p>
  
  <pre><code>$servicesTable = TableRegistry::get('services');
  $featuredServices = $servicesTable-&gt;find('all')
                            -&gt;select(['ServiceRequests.service_id', 'count' =&gt; 'COUNT(ServiceRequests.service_id)'])
                            -&gt;select($servicesTable)
                            -&gt;join([
                              'table' =&gt; 'service_requests',
                              'alias' =&gt; 'ServiceRequests',
                              'conditions' =&gt; ['Services.id' =&gt; 'ServiceRequests.service_id'],
                            ])
                            -&gt;group('service_id')
                            -&gt;order(['Count' =&gt; 'DESC'])
                            -&gt;limit(10);
  
  
          $this-&gt;set('featuredServices', $featuredServices);
  </code></pre>
  
  <p>and printing in view as</p>
  
  <pre><code>if (!empty($featuredServices)):
    foreach($featuredServices as $service):
       echo $service-&gt;title;
    endforeach;
  endif;
  </code></pre>
  
  <p>But it is not working. Also printing <code>echo $featuredServices;</code> only prints the sql string <code>SELECT.......</code>. Both tables are not associated with the controller I'm using in.</p>
  
  <blockquote>
    <p>EDIT 2</p>
  </blockquote>
  
  <p>What I want a query like this</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This sql query is working fine when running in <code>phpMyAdmin</code> and this query is generated by <code>debug($featuredServices)</code> of</p>
  
  <pre><code>$featuredServices = $servicesTable-&gt;find('all')
                    -&gt;select(['ServiceRequests.service_id', 'count' =&gt; 'COUNT(ServiceRequests.service_id)'])
                    -&gt;select($servicesTable)
                    -&gt;join([
                        'table' =&gt; 'service_requests',
                        'alias' =&gt; 'ServiceRequests',
                        'conditions' =&gt; ['Services.id' =&gt; 'ServiceRequests.service_id'],
                      ])
                    -&gt;group('service_id')
                    -&gt;order(['Count' =&gt; 'DESC'])
                    -&gt;limit(10);
  </code></pre>
  
  <p>This is only generating sql query on debug. How can I execute this so that I could get the result instead of sql query.</p>