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

Never mind... I got it.

I discovered that the join ('table') must be set
changed the following:

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']]

to:

join([

    's' => ['table' => $SearchCount,

      'type' => 'LEFT',

      'conditions'=> ['s.player_id = Players.id']],

    'v' => ['table' => $ViewCount,

      'type' => 'LEFT',

      'conditions'=> ['v.player_id = Players.id']],

    'p' => ['table' => $ProspectCount,

      'type' => 'LEFT',

      'conditions'=> ['p.player_id = Players.id']]

and it is now working.

On Friday, December 11, 2015 at 8:43:16 PM UTC+7, heavyKevy wrote:

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+unsubscribe@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.