Can someone please help with the syntax necessary to create the following query in Cake 3

[heavyKevy]

This is the target query:
SELECT 
  Players.id AS `Players__id`, 
  Players.user_id AS `Players__user_id`, 
  Players.opt_out AS `Players__opt_out`, 
  Users.email AS `Users__email`, 
  Users.first_name AS `Users__first_name`, 
  s.searches AS `Players__search_count`, 
  v.views AS `view_count`, 
  p.prospects AS `prospect_count` 
FROM 
  players Players 
  LEFT JOIN ( SELECT player_id, COUNT(id) searches FROM 
player_recruiter_searches WHERE created >= '2015-12-10 11:14:48' group by 
player_id )s ON (
    s.player_id = Players.id 
  ) 
  LEFT JOIN ( SELECT player_id, COUNT(id) views FROM player_recruiter_views 
WHERE  created >= '2015-12-10 11:14:48' group by player_id) v ON (
    v.player_id = Players.id 
  ) 
  LEFT JOIN ( SELECT player_id, COUNT(id) prospects FROM 
player_recruiter_prospects WHERE  created >= '2015-12-10 11:14:48' group by 
player_id)p ON (
    p.player_id = Players.id
  ) 
  INNER JOIN users Users ON Users.id = (Players.user_id) 
WHERE 
  Players.opt_out != 1 

Here is the code that I am trying to use to get the query:

$yesterday = new DateTime('-1 days');
$SearchCount = $this->PlayerRecruiterSearches->find()
  ->select([ 'player_id'=>'player_id','searches'=> 'COUNT(id)' ])
  ->where(['created >='=> $yesterday])
  ->group('player_id');
$ViewCount = $this->PlayerRecruiterViews->find()
  ->select([ 'player_id'=>'player_id','views'=> 'COUNT(id)' ])
  ->where(['created >='=> $yesterday])
  ->group('player_id');
$ProspectCount = $this->PlayerRecruiterProspects->find()
  ->select([ 'player_id'=>'player_id','prospects'=> 'COUNT(id)' ])
  ->where(['created >='=> $yesterday])
  ->group('player_id');

$qry = $this->find('all',['contain' => ['Users']]) 
  ->join([
    's' => [$SearchCount,
      'type' => 'LEFT',
      'conditions'=> ['s.player_id = Players.id']],
    'v' => [$ViewCount,
      'type' => 'LEFT',
      'conditions'=> ['v.player_id = Players.id']],
    'p' => [$ProspectCount,
      'type' => 'LEFT',
      'conditions'=> ['p.player_id = Players.id']]
  ])
  ->select($this)
  ->select($this->Users)
  ->select(['search_count'=>'s.searches','view_count' => 'v.views', 
'prospect_count' => 'p.prospects'])
  ->where(["Players.opt_out !=" => true]);

The query spit out is missing the 3 aliased subqueries such as this one:
SELECT player_id, COUNT(id) searches FROM player_recruiter_searches WHERE 
created >= '2015-12-10 11:14:48' group by player_id

What did I miss or do wrong?

according to the 
documentation: http://book.cakephp.org/3.0/en/orm/query-builder.html#subqueries
Subqueries are accepted anywhere a query expression can be used. For 
example, in the select() and join()methods.

However, there is little or no detail instructing how this would be done...

Thanks in advance for any help...
--Kevin

-- 
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

--- 
You received this message because you are subscribed to the Google Groups 
"CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to cake-php+unsubscr...@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.