How to use Query Builder to fetch a tables' fields, as well as aggregated max() and min() timestamps from HBTM associated table?

Need Help
1

CakePHP 3.1

Please provide a sample Custom Finder table method to retrieve all fields in the current table, as well as some aggregated fields from associated HBTM associated table. My PK names do not follow the CAKEPHP convention. What I want is the query to hand-off to my index() action’s paginate function. I want all the Bird records and the MAX, and MIN sighting times to display on INDEX view.

Hypothetical Example:
Birds belongsToMany Sightings
Sightings belongsToMany Birds

Foods: { food_id, food_name }
Birds_Foods: { food_id, bird_id }
Birds: { bird_id, bird_name }
Birds_Sightings: { bird_id, sighting_id }
Sightings: { sighting_id, sighting_timestamp, sighting_location }

This is similar to what I have working, so far…

// In Model/Table/BirdsTable.php
public function findIndexViewData ($query) {
return $query
->contain([
‘Foods’ => function ($q) {
return $q->select([‘food_name’])->autoFields(false);
},
‘Sightings’ => function ($q) {
return $q->select([‘sighting_timestamp’])
}
]);
}

I figured-out the solution I was looking for. Answer is:

 /**
  * Index method
  *
  * @return \Cake\Network\Response|null
  */
 public function index()
 {
     $query = $this->Birds
         ->find()
          ->join([
              'BirdsSightings' => [
                  'table' => 'birds_sightings',
                  'type' => 'LEFT',
                  'conditions' => 'BirdsSightings.bird_id = sightings.sightings_id',
                  ],
              'Sightings' => [
                  'table' => 'sightings',
                  'type' => 'LEFT',
                  'conditions' => 'BirdsSightings.sightingds_id = sightings.sightings_id',
                  ],
              ])
          ->select($this->Birds)
     $query->select([
         'min_date' => $query->func()->min('Sightings.sighting_timestamp'),
         'max_date' => $query->func()->max('Sightings.sighting_timestamp')
         ])
             ->group('Birds.bird_id');

     $birds= $this->paginate($query);
     $this->set(compact('birds'));
     $this->set('_serialize', ['birds']);
 }

Thanks.