Cakephp3 Sumof Containable Data

Question

I have an Articles model and Ratings model. An Article hasMany Ratings. I want to find the 10 highest rated Articles in the past 60 days.. The problem as I see it is that i have to get the sumOf 'score' on a containable model.

This is what I have tried:

$articles = $this->Articles->find()
     ->where( 'Articles.publish >' => (new Time())->subDays(60))
     ->contain([
          'Ratings' => function ($q) {
               return $q->select(['id', 'article_id', 'total' => func()->sum('score')
          ]);
      },
      ])
      ->order(['Ratings.total' => 'DESC']); 

The other approach would be to query Ratings first to tge the 10 highest total scores then leftJoin on Articles. This doesn't seem very efficient though as I have 2500+ articles and I don't want to add overhead by totalling score for Articles that won't be included as they are to old to be included.

Answer 1

Please check below code using left join

        $query = $this->Articles->find();
        $query->limit( 10 );
        $query->where( ['Articles.created >' => (new Time())->subDays(60)]);
        $query->select( [
            'Articles.id',
            'Articles.title',
            'sumrating' => $query->func()->sum( 'Ratings.rating' ),
            'Articles.created',
            'Articles.modified'
        ] );
        $query->leftJoin(
            [ 'Ratings' => 'ratings' ],
            [ 'Ratings.article_id = Articles.id' ]
        );
        $query->group( [ 'Articles.id' ] );
        $query->order( [ 'sumrating'=> 'DESC' ] );

        $articles = $query ;
        $this->set(compact('articles'));