Filter Help with subquery

[czcpf]

Hello,

Scenario

I'm implementing a custom Filter as shown below. Here I'm using a $subQuery to retrieve the 'latest' LaserComplianceReport by accountId where the 'latest' report is determined by the createdAt field. The subquery ideally would return a single id of the `latest` LaserComplianceReport in the database. The full query then only returns LaserComplianceTasks whose LaserComplianceReportId is 'in' the result of the subquery.

Problem Description

This works fine except for the scenario when two LaserComplianceReports exists in the database with the identical createdAt. Since my database only stores createdAt down to the millisecond (ie 2024-04-25 18:42:52) it is possible that two LaserComplianceReports are created down to the same millisecond. If this occurs the subquery below returns a list of more than 1 id.

What I've Tried

I tried using select("MAXid)") instead of select("id") in the subquery but that did not appear to work to make sure it only returned 1 result. It worked sometimes and other times the filter returned 0 results.

I also tried adding ->limit(0,1) to the subquery to always limit the result to 1 in case two LaserComplianceReports had the same createdAt date but mysql doesn't allow you to add limit to a subquery...

Could someone help me adjust either my subquery or query so that only 1 LaserComplianceReportId comes back even if two reports have the same createdAt date?

<?php

namespace Espo\Modules\MyModule\Classes\Select\LaserComplianceTask\PrimaryFilters;

use Espo\{
    Core\Select\Primary\Filter,
    Modules\MyModule\Entities\LaserComplianceReport,
    Modules\MyModule\Entities\LaserComplianceTask,
    ORM\Query\Part\Condition as Cond,
    ORM\Query\SelectBuilder as QueryBuilder
};

class Latest implements Filter
{
    public function apply(QueryBuilder $queryBuilder): void
    {
        // a list of id's of only the most recent laser compliance reports trick to limit only 1 result
        // MAX:(id) is used in case two compliance Reports have the same createdAt date
        // not working on production server. need to come up with another way to limit to 1 result if same createAt
        $subQuery = QueryBuilder::create()
            ->select("id")
            ->from(LaserComplianceReport::ENTITY_TYPE)
            ->leftJoin(LaserComplianceReport::ENTITY_TYPE, "cr2", [
                "cr2.accountId:" => "accountId",
                "cr2.createdAt>:" => "createdAt",
                "cr2.deleted" => false,
            ])
            ->where([
                "cr2.createdAt" => null,
            ])
            ->build();

        $queryBuilder
            ->where(
                Cond::in(Cond::column("laserComplianceReportId"), $subQuery)
            )
            ->distinct();
    }
}​

[yuri]

Hi, It's more SQL-related question rather than Espo. Maybe try to craft an SQL that will work for you then replicate it with the query builder.

Maybe EXISTS sub-query will work for you? https://github.com/espocrm/espocrm/b...dition.php#L75

Doesn't these two conflict with each other?

"cr2.createdAt>:" => "createdAt"

"cr2.createdAt" => null,

BTW, distinct is not needed when you use a sub-query where-clause.

[yuri]

You might also need to use 'laserComplianceTask.accountId' in your sub-query.

[yuri]

You can also use LEFT JOIN sub-query. It should support LIMIT.

[czcpf]

Thank you for your comments. I've been testing MYSQL queries and the following one works. It will select the id's corresponding to each account's non-deleted latest laser compliance report. Here, even if there is a duplicate row with the same createdAt date time it will still only return a single record. The only bit I cannot get into query builder is

WHERE (cr1.account_id, cr1.created_at) IN ... maybe you can help there? This is where condition using two columns

SELECT cr1.id
FROM laser_compliance_report AS cr1
WHERE cr1.id IN
(
   SELECT MAX(id)
   FROM laser_compliance_report
   WHERE (cr1.account_id, cr1.created_at) IN
    (
      SELECT account_id, MAX(created_at)
      FROM laser_compliance_report
      WHERE deleted = false
      GROUP BY account_id
   )
   GROUP BY account_id, created_at
);​

[yuri]

Row constructor is supported: https://github.com/espocrm/espocrm/b...ssion.php#L784

[czcpf]

Thank you so much for your help. For anyone else. Here is the full apply filter code

<?php

namespace Espo\Modules\MyModule\Classes\Select\ComplianceReport\PrimaryFilters;

use Espo\{
    Core\Select\Primary\Filter,
    Modules\MyModule\Entities\ComplianceReport,
    ORM\Query\Part\Expression,
    ORM\Query\SelectBuilder as QueryBuilder,
    ORM\Query\Part\Condition as Cond,
    ORM\EntityManager,
    Core\Utils\Log
};

class Latest implements Filter
{
    private EntityManager $entityManager;
    private Log $log;

    public function __construct(EntityManager $entityManager, Log $log)
    {
        $this->entityManager = $entityManager;
        $this->log = $log;
    }

    public function apply(QueryBuilder $queryBuilder): void
    {
        //we want to select the latest, non-deleted compliance report for each accountId
        //the code below will ensure only 1 result comes back for each accountId even if createdAt
        //is the same for two different compliance reports

        //MYSQL EXAMPLE
        //SELECT cr1.*
        //FROM compliance_report AS cr1
        //WHERE cr1.id IN(
        //   SELECT MAX(id)
        //   FROM compliance_report
        //   WHERE (cr1.account_id, cr1.created_at) IN(
        //      SELECT account_id, MAX(created_at)
        //      FROM compliance_report
        //      WHERE deleted = false
        //      GROUP BY account_id
        //   )
        //   GROUP BY account_id, created_at
        //);

        $subQuery1 = QueryBuilder::create()
            ->select(["accountId", "MAX:(createdAt)"])
            ->from(ComplianceReport::ENTITY_TYPE)
            ->where(["deleted" => false])
            ->group(["accountId"])
            ->build();

        $subQuery2 = QueryBuilder::create()
            ->select("MAX:(id)")
            ->from(ComplianceReport::ENTITY_TYPE)
            ->where(
                Cond::in(
                    Expression::row(
                        Expression::column("accountId"),
                        Expression::column("createdAt")
                    ),
                    $subQuery1
                )
            )
            ->group(["accountId", "createdAt"])
            ->build();

        //$sql2 = $this->entityManager->getQueryComposer()->compose($subQuery2);
        //$this->log->warning('Latest.php ComplianceReport - apply() #55 $sql2= ', [$sql2]);

        $queryBuilder->where([
            "id=s" => $subQuery2,
        ]);
    }
}
​