SubQuery in ActiveRecord

Question

i have following models in yii2:

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

How to create following query using methods of 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.: The last WHERE clause should be not fix but variable like this:

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

which will put out following result:Mister or Miss

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

Hint for Fabrizio Caldarelli

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

Ur solution won't help me:=(

This is ur code:

$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;
}]);

and this is 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

I use Gridview like this

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

Colud u show me up how to use ur solution in this context?

Answer 1

This should work:

$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' is a relation in LAnrede model (one or many relation?)

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