CakePHP 3：从多个表中选择数据

h9a6wy2h 于 2022-11-11 发布在 PHP

关注(0)|答案(3)|浏览(151)

我有两个表services和service_requests。service_requests表具有引用services表的外键service_id。
我必须从services和service_requests中选择数据，其中services.id = service_requests.service_idORDER BY COUNT(service_requests.service_id) DESC
这就是我在控制器中所做的

$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);

和打印，因为

if (!empty($featuredServices)):
  foreach($featuredServices as $service):
     echo $service->title;
  endforeach;
endif;

但是它不起作用。另外，打印echo $featuredServices;只打印sql字符串SELECT.......。这两个表都与我正在使用的控制器没有关联。
编辑2
我想要这样的查询

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

此sql查询在phpMyAdmin中运行时工作正常，并且此查询是由debug($featuredServices)生成的

$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);

这只是在调试时生成sql查询。我如何执行此操作，以便可以获得结果而不是sql查询。

cakephp

来源：https://stackoverflow.com/questions/38218382/cakephp-3-select-data-from-multiple-table

3条答案

按热度按时间

lf3rwulv1#

首先，如果你在cakePHP查询中使用select（），那么find（'all'）是不必要的，因此find（）就足够了。然后，我认为你的执行问题可以通过放置-〉execute（）来解决;在查询的结尾。它不常使用，但有时会有帮助。

赞(0）回复(0）举报 2022-11-11

ruyhziif2#

这可以通过表关联来实现
您的服务表：

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'
    ));     
}

您的请求表：

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',
    ));

}

现在在控制器方法中：

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);
}

有关查找查询的更多具体信息，请参见链接http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html
以及有关表关联的更多信息，请参见链接：http://book.cakephp.org/3.0/en/orm/associations.html

赞(0）回复(0）举报 2022-11-11

j9per5c43#

然后通过$service_requests-〉services访问关联的数据。
这是最干净、最简单的方法。
查看如何将它们链接在一起：）http://book.cakephp.org/3.0/en/orm/associations.html

赞(0）回复(0）举报 2022-11-11