Announcement

Collapse
No announcement yet.

Filter Help with subquery

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    PHP 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 $entityManagerLog $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,
            ]);
        }
    }
    Last edited by czcpf; 04-30-2024, 09:22 PM.

    Leave a comment:


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

    Leave a comment:


  • czcpf
    replied
    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

    Code:
    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
    );​

    Leave a comment:


  • yuri
    commented on 's reply
    You can also use LEFT JOIN sub-query. It should support LIMIT.

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

    Leave a comment:


  • yuri
    replied
    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?

    Code:
    "cr2.createdAt>:" => "createdAt"
    Code:
    "cr2.createdAt" => null,
    BTW, distinct is not needed when you use a sub-query where-clause.

    Leave a comment:


  • czcpf
    started a topic Filter Help with subquery

    Filter Help with subquery

    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 Code:
    <?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();
        }
    }
Working...
X