php - 将带有自连接的原始 sql 查询翻译为学说 queryBuilder 查询

Question

嗨，我有 sql 查询：

select r.criterion_id, r.logical_state_id, r.created_at from(
select  acr.criterion_id, max(acr.created_at) as max_date  from audit_company_result acr
    where acr.audit_company_id = 42
    group by acr.criterion_id
) as x inner join audit_company_result as r on r.criterion_id = x.criterion_id and r.created_at = x.max_date

我想翻译成学说 QueryBuilder 形式。我有一个实体：

审计公司：

class AuditCompany
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="datetime")
     */
    private $createdAt;

    /**
     * @ORM\Column(type="datetime", nullable = true)
     */
    private $startedAt;

    /**
     * @ORM\Column(type="datetime")
     */
    private $plannedEnding;

    /**
     * @ORM\Column(type="datetime", nullable = true)
     */
    private $endingAt;

    /**
     * @ORM\Column(type="boolean")
     */
    private $isCertification;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Company", inversedBy="companyAudits")
     * @ORM\JoinColumn(nullable=false)
     */
    private $company;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Audit", inversedBy="companyAudits")
     * @ORM\JoinColumn(nullable=false)
     */
    private $audit;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\AuditCompanyResult", mappedBy="auditCompany")
     */
    private $auditCompanyResults;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Employee")
     * @ORM\JoinColumn(nullable=false)
     */
    private $responsibleEmployee;

    /**
     * @ORM\Column(type="datetime", nullable=true)
     */
    private $manualStartedAt;

标准：

class Criterion
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="text", nullable=true)
     */
    private $info;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $name;

    /**
     * @ORM\Column(type="text")
     */
    private $algorithmInfo;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $comparableType;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\CategoryCriterion", inversedBy="criteria")
     * @ORM\JoinColumn(nullable=false)
     */
    private $categoryCriterion;

    /**
     * @ORM\Column(type="text")
     */
    private $logicalMeasureInfo;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\CriterionLogicalStateValue", mappedBy="criterion")
     */
    private $criterionLogicalStateValues;

    /**
     * @ORM\Column(type="boolean")
     */
    private $isAutomatic;

审计公司结果：

class AuditCompanyResult
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Criterion")
     * @ORM\JoinColumn(nullable=false)
     */
    private $criterion;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\AuditCompany", inversedBy="auditCompanyResults")
     * @ORM\JoinColumn(nullable=false)
     */
    private $auditCompany;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $comment;

    /**
     * @ORM\Column(type="boolean")
     */
    private $autoVerified;

    /**
     * @var datetime $created
     *
     * @ORM\Column(name="created_at", type="datetime", nullable = false)
     */
    private $createdAt;

    /**
     * @var datetime $updated
     *
     * @ORM\Column(name="updated_at", type="datetime", nullable = false)
     */
    private $updatedAt;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\CriterionLogicalStateValue")
     * @ORM\JoinColumn(nullable=false)
     *
     */
    private $logicalState;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     */
    private $criterionValue;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $source;

标准逻辑状态值：

class CriterionLogicalStateValue
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $name;

    /**
     * @ORM\Column(type="boolean")
     */
    private $logicalState;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Criterion", inversedBy="criterionLogicalStateValues")
     * @ORM\JoinColumn(nullable=false)
     */
    private $criterion;

我只能从上面的 sql 查询中编写子查询：

$qb = $this->getEntityManager()->getRepository(AuditCompanyResult::class)->createQueryBuilder('acr');
        $result = $qb->select('acr')
            ->addSelect('partial cr.{id}')
            ->addSelect($qb->expr()->max('acr.createdAt'))
            ->leftJoin('acr.criterion','cr')
            ->groupBy('cr.id')
            ->where('acr.auditCompany=42')
            ->getQuery()->getArrayResult();

我不知道如何在 QueryBuilder 中编写整个查询。有自加入。与文章中的完全相同：https ://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

但是如何将原始 sql 查询转换为 QueryBuilder？我很乐意提供帮助。此致

标签： phpsqldoctrinesymfony4