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
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.
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.
No comments:
Post a Comment