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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

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

heavyKevy
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: <a href="http://book.cakephp.org/3.0/en/orm/query-builder.html#subqueries" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\75http%3A%2F%2Fbook.cakephp.org%2F3.0%2Fen%2Form%2Fquery-builder.html%23subqueries\46sa\75D\46sntz\0751\46usg\75AFQjCNFRLAPeUP_uPqD06ByO4KEdibnLwg&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\75http%3A%2F%2Fbook.cakephp.org%2F3.0%2Fen%2Form%2Fquery-builder.html%23subqueries\46sa\75D\46sntz\0751\46usg\75AFQjCNFRLAPeUP_uPqD06ByO4KEdibnLwg&#39;;return true;">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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.