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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
