0

I have cakephp4 project
having 1 to many relationship between Portfolios and PSnaps
I want to show all 'Portfolios' with one associated record from PSnaps where its PSnaps.status=1 and order=>['PSnap.order_at'=>'ASC']

I tried many things but getting the correct result

below is giving 90% correct result only ordering on PSnaps.order_at is not working.

along with hasmany() i have created hasOne() association as shown in below model

Model

class PortfoliosTable extends Table
{ 
   public function initialize(array $config): void
   {
    parent::initialize($config);

    $this->setTable('portfolios');
    $this->setDisplayField('id');
    $this->setPrimaryKey('id');

    $this->hasOne('FirstPSnaps', [
        'className' => 'PSnaps',
        'foreignKey' => 'portfolio_id',
        'strategy' => 'select',//also tried join
        //'joinType'=>'LEFT',//also tried inner,left,right
        //'sort' => ['FirstPSnaps.order_at' => 'ASC'], //*******this is not working
        'conditions' => function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
            $query->order(['FirstPSnaps.order_at' => 'ASC']);//*******also not working
            return [];
        } 
         
    ]) 
    ; 

    $this->hasMany('PSnaps', [
        'foreignKey' => 'portfolio_id',
    ]); 
}

Controller

$pfolios = $this->Portfolios->find('all')
        ->select(['Portfolios.id','Portfolios.client','Portfolios.country','Portfolios.url'])
        ->where(['Portfolios.status'=>1])
        ->order(['Portfolios.order_at'=>'asc','Portfolios.id'=>'asc'])
        ->limit(8)
        ->contain([
            'FirstPSnaps'=>function($q){
                return $q
                    ->select(['FirstPSnaps.portfolio_id','FirstPSnaps.snap'])
                    //->where(['FirstPSnaps.status'=>1])
                    //->order(['FirstPSnaps.order_at'=>'asc'])
                    ;
            } 
        ])
        ->toArray();

it is returning correct porfolios with 1 p_snap record but ordering/sorting is not correct as I need first p_snap something like where p_snap.status=1 and p_span.portfolio_id= portfolios.id limit 1.

alamnaryab
  • 1,480
  • 3
  • 19
  • 31
  • You might be better off writing a dedicated query as part of the model, then just echoing out the first item in each of the return objects. – joshv2 May 24 '21 at 14:58
  • Check the `queryBuilder` capabilities in the [documentation](https://book.cakephp.org/3/en/orm/query-builder.html#passing-conditions-to-contain). – Greg Schmidt May 24 '21 at 16:17
  • I tried queryBuilder(), getting more errors – alamnaryab May 25 '21 at 05:52
  • You need a little more trickery for that to work properly, using just sorting and loading all records, and then relying on the right record to be picked afterwards on PHP level, isn't overly advised, see **https://stackoverflow.com/questions/30241975/how-to-limit-contained-associations-per-record-group**. Also - shameless plug ahead - check out **https://github.com/icings/partitionable**, it's not stable yet, but you might find it useful and want to give it a try. – ndm May 25 '21 at 08:54

0 Answers0