时间戳

首页 > 解决方案 > CakePHP：如何在 Cakephp 4 中使用多个 LEFT JOIN 进行编码，其中一个 LEFT JOIN 与主表无关

cakephp - CakePHP：如何在 Cakephp 4 中使用多个 LEFT JOIN 进行编码，其中一个 LEFT JOIN 与主表无关

问题描述

目标：显示列表：会员 ID/会员姓名/访问日期/会员等级

这是我想在 CakePHP 4 中编写的 SQL 语句：

SELECT visits.member_id, members.mbr_name, visits.created, batches.grade_id
FROM visits
LEFT JOIN members ON members.mbrid = visits.member_id
LEFT JOIN batches ON batches.member_id = members.mbrid
WHERE batches.batch = '2020' AND (batches.sub_batch = '0' OR batches.sub_batch = '1')
ORDER BY visits.created DESC

如您所见，第二个 LEFT JOIN 子句仅与成员相关，而不是与主表访问相关。

这是我在 CakePHP 4 中编码的方式：

$this->Visits->find()
        ->contain('Members.Batches', function (Query $q) {
          return $q
            ->select('member_id', 'batch', 'sub_batch', 'grade_id')
            ->where(['Batches.batch' => '2020', 'Batches.sub_batch' => '0']);
        })
        ->contain('Members')
        ->order(['Visits.created' => 'DESC'])
    );

在调试模式下，我可以看到生成了两 (2) 个 SQL。不是我所期望的：

SELECT 
  Visits.id AS Visits__id, 
  Visits.member_id AS Visits__member_id, 
  Visits.vst_datetime AS Visits__vst_datetime, 
  Visits.location_id AS Visits__location_id, 
  Visits.created AS Visits__created, 
  Visits.modified AS Visits__modified, 
  Members.id AS Members__id, 
  Members.mbrid AS Members__mbrid, 
  Members.mbr_name AS Members__mbr_name, 
  Members.mbr_type AS Members__mbr_type, 
  Members.created AS Members__created, 
  Members.modified AS Members__modified 
FROM 
  visits Visits 
  LEFT JOIN members Members ON Members.mbrid = (Visits.member_id) 
ORDER BY 
  Visits.created DESC 
LIMIT 
  20 OFFSET 0

SELECT 
  Batches.member_id AS Batches__member_id 
FROM 
  batches Batches 
WHERE 
  (
    Batches.member_id in (
      187, 1471, 1470, 1463, 250, 350, 1000, 
      501, 300, 255, 254, 253, 3, 303, 215, 
      305, 202, 201
    ) 
    AND Batches.batch = '2020' 
    AND Batches.sub_batch = '0'
  )

标签： cakephporm

解决方案

这是为我提供所需 sql 的代码：

$visits = $this->Paginator->paginate($this->Visits->find()
        ->select(['Members.mbrid', 'Members.mbr_name', 'Visits.created', 'Batches.grade_id'])
        ->leftJoinWith('Members')
        ->leftJoinWith('Members.Batches')
        ->where(['Batches.batch' => '2020', 'Batches.sub_batch' => '0'])
        ->order(['Visits.created' => 'DESC'])
    );

正如@ndm 所建议的那样，使用leftJoinWith。

推荐阅读