SubQuery in ActiveRecord

Question

我在yii2中有以下模型：

use frontend\modules\bewerber\models\Bewerber;
use common\modules\basis\models\base\Person;
use common\modules\lookup\models\LAnrede;

如何使用ActiveRecord的方法创建以下查询？

SELECT anrede FROM L_anrede JOIN Person ON L_anrede.id=Person.id_anrede WHERE Person.id IN
(SELECT id_person FROM Bewerber WHERE Bewerber.id_person=1);

P.S.：最后一个WHERE子句不应该是固定的，而应该是这样的变量：

var_dump(LAnrede::findOne([$model->id_person])->anrede)

结果如下：先生、先生或小姐

................................................................

法布里齐奥·卡尔达雷利提示

................................................................

您的解决方案无助于me:=(

这是您的代码：

​

$idPerson = 1;

$show=LAnrede::find()->joinWith(['Person' => function($q) use($idPerson) {
       $q->andWhere([
             'Person.id' => (new \yii\db\Query())->from('Bewerber')->where(['Bewerber.id_person' => $idPerson])
       ])->anrede;
}]);

​

这是var_dump($show);

​

E:\xampp\htdocs\yii2_perswitch\frontend\modules\bewerber\views\bewerber\index.php:48:
object(common\modules\lookup\models\LAnredeQuery)[207]
  public 'sql' => null
  public 'on' => null
  public 'joinWith' => 
    array (size=1)
      0 => 
        array (size=3)
          0 => 
            array (size=1)
              ...
          1 => boolean true
          2 => string 'LEFT JOIN' (length=9)
  public 'select' => null
  public 'selectOption' => null
  public 'distinct' => null
  public 'from' => null
  public 'groupBy' => null
  public 'join' => null
  public 'having' => null
  public 'union' => null
  public 'params' => 
    array (size=0)
      empty
  private '_events' (yii\base\Component) => 
    array (size=0)
      empty
  private '_behaviors' (yii\base\Component) => 
    array (size=0)
      empty
  public 'where' => null
  public 'limit' => null
  public 'offset' => null
  public 'orderBy' => null
  public 'indexBy' => null
  public 'emulateExecution' => boolean false
  public 'modelClass' => string 'common\modules\lookup\models\LAnrede' (length=36)
  public 'with' => null
  public 'asArray' => null
  public 'multiple' => null
  public 'primaryModel' => null
  public 'link' => null
  public 'via' => null
  public 'inverseOf' => null

​

我像这样使用Gridview

​

$gridColumn = [
    [
        'attribute' => '',
        'label' => Yii::t('app', 'Anrede'),
        'format' => 'html',
        'value' => function($model) {
            return "<p><font color='green'>" . LAnrede::findOne([$model->id_person])->anrede . "</p>";
        }
    ],
    ];

​

请告诉我如何在这种情况下使用您的解决方案？

Answer 1

这应该是可行的：

$idPerson = 1;

LAnrede::find()->joinWith(['Person' => function($q) use($idPerson) {
       $q->andWhere([
             'Person.id' => (new \yii\db\Query())->from('Bewerber')->where(['Bewerber.id_person' => $idPerson])
       ]);
}])
->all();

‘'Person’是LAnrede模型中的一个关系(一个还是多个关系？)

public function getPerson()
{
    return $this->hasMany(Person::className(), ['id_anrede' => 'id']);
}