SubmissionQueryBuilder.inc.php

<?php
 
namespace APP\Services\QueryBuilders;
 
use Illuminate\Database\Capsule\Manager as Capsule;
 
class SubmissionQueryBuilder extends \PKP\Services\QueryBuilders\PKPSubmissionQueryBuilder {
 
   protected $seriesIds = null;
 
   protected $orderByFeaturedSeq = null;
 
   public function filterBySeries($seriesIds) {
      if (!is_null($seriesIds) && !is_array($seriesIds)) {
         $seriesIds = array($seriesIds);
      }
      $this->seriesIds = $seriesIds;
      return $this;
   }
 
   public function orderBy($column, $direction = 'DESC') {
      // Bring in orderby constants
      import('classes.submission.SubmissionDAO');
      switch ($column) {
         case ORDERBY_SERIES_POSITION:
            $this->orderColumn = 'po.series_position';
            break;
         default:
            return parent::orderBy($column, $direction);
      }
      $this->orderDirection = $direction;
 
      return $this;
   }
 
   public function orderByFeatured() {
      $this->orderByFeaturedSeq = true;
   }
 
   public function appGet($q) {
 
      if (!empty($this->seriesIds)) {
         $q->leftJoin('publications as publication_s', 's.current_publication_id', '=', 'publication_s.publication_id');
         $q->whereIn('publication_s.series_id', $this->seriesIds);
      }
 
      // order by series position
      if ($this->orderColumn === 'po.series_position') {
         $this->columns[] = 'po.series_position';
         $q->leftJoin('publications as po', 's.current_publication_id', '=', 'po.publication_id');
         $q->groupBy('po.series_position');
      }
 
      if (!empty($this->orderByFeaturedSeq)) {
         if (!empty($this->seriesIds)) {
            $assocType = ASSOC_TYPE_SERIES;
            $assocIds = $this->seriesIds;
         } elseif (!empty($this->categoryIds)) {
            $assocType = ASSOC_TYPE_CATEGORY;
            $assocIds = $this->categoryIds;
         } else {
            $assocType = ASSOC_TYPE_PRESS;
            $assocIds = [$this->contextId ? $this->contextId : CONTEXT_ID_NONE];
         }
         $q->leftJoin('features as sf', function($join) use ($assocType, $assocIds) {
            $join->on('s.submission_id', '=', 'sf.submission_id')
               ->on('sf.assoc_type', '=', Capsule::raw($assocType));
            foreach ($assocIds as $assocId) {
               $join->on('sf.assoc_id', '=', Capsule::raw(intval($assocId)));
            }
         });
 
         // Featured sorting should be the first sort parameter. We sort by
         // the seq parameter, with null values last
         $q->groupBy(Capsule::raw('sf.seq'));
         $this->columns[] = 'sf.seq';
         $this->columns[] = Capsule::raw('case when sf.seq is null then 1 else 0 end');
         array_unshift(
            $q->orders,
            array('type' => 'raw', 'sql' => 'case when sf.seq is null then 1 else 0 end'),
            array('column' => 'sf.seq', 'direction' => 'ASC')
         );
      }
 
      return $q;
   }
}