CakePHP嵌套两个选择查询 [英] CakePHP nesting two select queries

问题描述

我在cakePHP中有两个表。

  competencies 
 ----------- -  
 id 
 name 
 
 competenceRatings 
 ----------------- 
 id 
 competence_id 
 user_id 
评级
  

我需要一种方法来编写以下查询蛋糕方式：

SELECT * FROM competencies WHERE ID NOT IN（SELECT competence_id FROM competence_ratings WHERE employee_id = $ userId）

有人请帮助我！

在执行此子查询方法之前，我做了什么：

我试过Competencies-> hasMany-> competenceRatings， competenceRatings-> belongTo->能力关系。

  $ competencies = $ this-> Competence-> CompetenceRating-> find （'all'，array（'CompetenceRating.user_id'=> $ userId，'CompetenceRating.competence_id！='=>'Competence.id'））; 
  

我想要能够获得用户尚未对其进行任何评级的能力的名称competenceRatings表。即，我需要在comptenceRatings表（对于给定user_id）中没有条目的Competencies表中的名称列表。

EDIT p>

我尝试过table join：

  $ options ['joins'] = array（
 array（
'table'=>'competence_ratings'，
'alias'=>'CompetenciesRating'，
'type'=> LEFT OUTER '，
'conditions'=> array（
'Competence.id = CompetenceRating.competence_id'
）
）
）; 
 $ options ['conditions'] = array（'CompetenceRating.employee_id'=> $ employee ['Employee'] ['id']）; 
 
 $ competencies = $ this-> Competence-> find（'all'，$ options）; 
  

解决方案

您可能必须使用子查询

  $ subqueryOptions = array（'fields'=> array（'competence_id'），'conditions'=> array 'employee_id'=> $ user_id））; 
 $ subquery = $ this-> Competence-> CompetenciesRating-> subquery（'all'，$ subqueryOptions）; 
 
 $ res = $ this-> Competence-> CompetenciesRating-> find（'all'，array（
'conditions'=> array（'id NOT IN'。 $ subquery）
）） 
  

子查询的来源为：
https://github.com/dereuromark/tools/blob/2.0/Lib/MyModel.php#L405 < a>
你需要把它放在你的AppModel.php

但我认为子查询不是必需的。您可以对其进行一次简单的查询：

  $ this-> Competence-> CompetenceRating-> find（'all'，array（
'group'=>'competence_id'，
'conditions'=> array（'NOT'=>'employee_id'=> $ user_id）） ，
'contains'=> array（'Competence'）
））; 
  

如果递归设置为-1，不要忘记通过 >

I have two tables in cakePHP.

competencies
------------
id
name

competenceRatings
-----------------
id
competence_id
user_id
rating

I need a way to write the following query in the cake way:

SELECT * FROM competencies WHERE id NOT IN (SELECT competence_id FROM competence_ratings WHERE employee_id = $userId)

Someone please help me!!

What i did before going to this subquery method:

I tried competencies->hasMany->competenceRatings, competenceRatings->belongsTo->competencies relations.

$competencies = $this->Competence->CompetenceRating->find('all',array('CompetenceRating.user_id' => $userId,'CompetenceRating.competence_id !=' => 'Competence.id'));

I want to be able to get the names of competencies for which a user have NOT made any ratings into competenceRatings table. i.e., I need list of names from competencies table for which there are no entries in comptenceRatings table(for given user_id).

EDIT

I tried table join also:

$options['joins'] = array(
            array(
                'table' => 'competence_ratings',
                'alias' => 'CompetenceRating',
                'type' => 'LEFT OUTER',
                'conditions' => array(
                    'Competence.id = CompetenceRating.competence_id'
                )
            )
        );
$options['conditions'] = array( 'CompetenceRating.employee_id' => $employee['Employee']['id'] );

$competencies = $this->Competence->find('all',$options);

解决方案

you would probably have to use a subquery():

$subqueryOptions = array('fields' => array('competence_id'), 'conditions' => array('employee_id'=>$user_id));
$subquery = $this->Competence->CompetenceRating->subquery('all', $subqueryOptions);

$res = $this->Competence->CompetenceRating->find('all', array(
    'conditions' => array('id NOT IN '. $subquery)
));

the source for subquery is here: https://github.com/dereuromark/tools/blob/2.0/Lib/MyModel.php#L405 you need to put this in your AppModel.php

BUT I think the subquery is not necessary. You can probably make a single and easy query out of it:

$this->Competence->CompetenceRating->find('all', array(
    'group' => 'competence_id', 
    'conditions' => array('NOT' => 'employee_id'=>$user_id)),
    'contain' => array('Competence')
));

dont forget to include Competence via "contain" if you have recursive set to -1.

这篇关于CakePHP嵌套两个选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！