How to set result of query in GridView

Hi guys, following query will give me back result of 4 tables being invloved(see attachement)




SELECT person.vorname,person.nachname,dateianhang.dateiname,l_dateianhang_art.bezeichnung FROM 

person  JOIN e_dateianhang on e_dateianhang.id_person=person.id JOIN dateianhang ON 

dateianhang.id_e_dateianhang=e_dateianhang.id JOIN l_dateianhang_art ON 

l_dateianhang_art.id=dateianhang.id_dateianhang_art WHERE id_person=3;



id_person will be given by parameter of anonymous function!

My intention is to get this result in GridView. I have no problems defining this query in yii-Syntax, but i don’t know, how to implement index.php in Controller,which uses searchModel. at the moment, I get all values of just one table. Here is code of index.php





    public function actionIndex($id) {

        $searchModel = new DateianhangSearch();

        $dataProvider = $searchModel->search(Yii::$app->request->queryParams,$id);

        return $this->render('index', [

                    'searchModel' => $searchModel,

                    'dataProvider' => $dataProvider,

        ]);

    }



This is, GridView looks like:





<?php

$gridColumn = [

.

.

.

    ],

<?=


GridView::widget([

    'dataProvider' => $dataProvider,

    'filterModel' => $searchModel,

    'columns' => $gridColumn,

.

.

.

This is, searchModel looks like





    public function search($params)

    {

        $query = Dateianhang::find();


        $dataProvider = new ActiveDataProvider([

            'query' => $query,

        ]);


        $this->load($params);


        if (!$this->validate()) {

            // uncomment the following line if you do not want to return any records when validation fails

            // $query->where('0=1');

            return $dataProvider;

        }


        $query->andFilterWhere([

            'id' => $this->id,

            'id_e_dateianhang' => $this->id_e_dateianhang,

            'id_dateianhang_art' => $this->id_dateianhang_art,

            'angelegt_am' => $this->angelegt_am,

            'angelegt_von' => $this->angelegt_von,

            'aktualisiert_am' => $this->aktualisiert_am,

            'aktualisiert_von' => $this->aktualisiert_von,

            'optimistic_lock' => $this->optimistic_lock,

        ]);


        $query->andFilterWhere(['like', 'bezeichnung', $this->bezeichnung])

            ->andFilterWhere(['like', 'dateiname', $this->dateiname]);


        return $dataProvider;

    }



I would suggest you declare you relationships/associations in your model




<?php


// your model

class Dateianhang extends ActiveRecord {

  public function getPerson()

  {

     // code goes here

  }


  // your remaining relationships

}

then in your DateianhangSearch model you can call


$query->with('person'); // add your other relationships here as well

your controller stays lean as it is and your view can contain the gridview which will look something like


'columns' => [

  'person.name',

  'dateiname',

  'some_other_relationship.attribute_name'

]

If I code like this, I will get error:





Invalid Configuration – yii\base\InvalidConfigException

The "query" property must be an instance of a class that implements the QueryInterface e.g. 

yii\db\Query or its subclasses.



Here is code,who causes error:





    public function search($params,$id) {

        $query = (new \yii\db\Query())

                ->select('person.vorname,person.nachname,dateianhang.dateiname,l_dateianhang_art.bezeichnung')->from('person')

                ->join('LEFT JOIN', 'e_dateianhang', 'e_dateianhang.id_person=person.id ')

                ->join('LEFT JOIN', 'dateianhang', 'dateianhang.id_e_dateianhang=e_dateianhang.id')

                ->join('LEFT JOIN', 'l_dateianhang_art', 'l_dateianhang_art.id=dateianhang.id_dateianhang_art')

                ->where(['id_person' => $id])->all();

        //var_dump($query);

        //die();


        $dataProvider = new ActiveDataProvider([

            'query' => $query,

        ]);


        $this->load($params);

        return $dataProvider;

    }



loose the all() at the end of your query




$query = (new \yii\db\Query())

                ->select('person.vorname,person.nachname,dateianhang.dateiname,l_dateianhang_art.bezeichnung')->from('person')

                ->join('LEFT JOIN', 'e_dateianhang', 'e_dateianhang.id_person=person.id ')

                ->join('LEFT JOIN', 'dateianhang', 'dateianhang.id_e_dateianhang=e_dateianhang.id')

                ->join('LEFT JOIN', 'l_dateianhang_art', 'l_dateianhang_art.id=dateianhang.id_dateianhang_art')

                ->where(['id_person' => $id]);



Without method all() I will get error





 PHP Notice – yii\base\ErrorException

Trying to get property of non-object



var_dump without all shows this:





E:\xampp\htdocs\yii2_perswitch\frontend\modules\dateianhang\models\DateianhangSearch.php:48:

object(yii\db\Query)[164]

  public 'select' => 

    array (size=4)

      0 => string 'person.vorname' (length=14)

      1 => string 'person.nachname' (length=15)

      2 => string 'dateianhang.dateiname' (length=21)

      3 => string 'l_dateianhang_art.bezeichnung' (length=29)

  public 'selectOption' => null

  public 'distinct' => null

  public 'from' => 

    array (size=1)

      0 => string 'person' (length=6)

  public 'groupBy' => null

  public 'join' => 

    array (size=3)

      0 => 

        array (size=3)

          0 => string 'LEFT JOIN' (length=9)

          1 => string 'e_dateianhang' (length=13)

          2 => string 'e_dateianhang.id_person=person.id ' (length=34)

      1 => 

        array (size=3)

          0 => string 'LEFT JOIN' (length=9)

          1 => string 'dateianhang' (length=11)

          2 => string 'dateianhang.id_e_dateianhang=e_dateianhang.id' (length=45)

      2 => 

        array (size=3)

          0 => string 'LEFT JOIN' (length=9)

          1 => string 'l_dateianhang_art' (length=17)

          2 => string 'l_dateianhang_art.id=dateianhang.id_dateianhang_art' (length=51)

  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) => null

  public 'where' => 

    array (size=1)

      'id_person' => string '3' (length=1)

  public 'limit' => null

  public 'offset' => null

  public 'orderBy' => null

  public 'indexBy' => null

  public 'emulateExecution' => boolean false



var_dump with all() shows values,as they should be,like this:





E:\xampp\htdocs\yii2_perswitch\frontend\modules\dateianhang\models\DateianhangSearch.php:48:

array (size=1)

  0 => 

    array (size=4)

      'vorname' => string 'Susanne' (length=7)

      'nachname' => string 'Erhardt-Kronsberg' (length=17)

      'dateiname' => string 'woman_3.jpg' (length=11)

      'bezeichnung' => string 'Bewerberfoto' (length=12)



ActiveDataProvider expects a QueryInterface not an array, the moment you call all() it runs the query agains the database and returns an array. ActiveDataProvider calls all internally.

UPDATE: that error you getting is probably one of the attribute you did not select from the database

This line will throw out error above




<h2><?= Html::encode($model->id) ?></h2>



It will be the same, if i fetch attribute id from model(dateianhang) like this





 		$query = (new \yii\db\Query())

                ->select('dateianhang.id,person.vorname,person.nachname,dateianhang.dateiname,l_dateianhang_art.bezeichnung')->from('person')

                ->join('LEFT JOIN', 'e_dateianhang', 'e_dateianhang.id_person=person.id ')

                ->join('LEFT JOIN', 'dateianhang', 'dateianhang.id_e_dateianhang=e_dateianhang.id')

                ->join('LEFT JOIN', 'l_dateianhang_art', 'l_dateianhang_art.id=dateianhang.id_dateianhang_art')

                ->where(['id_person' => $id]);






var_dump($model);



will give back this





E:\xampp\htdocs\yii2_perswitch\frontend\modules\dateianhang\views\dateianhang\_detail.php:16:

array (size=5)

  'id' => string '16' (length=2)

  'vorname' => string 'Susanne' (length=7)

  'nachname' => string 'Erhardt-Kronsberg' (length=17)

  'dateiname' => string 'woman_3.jpg' (length=11)

  'bezeichnung' => string 'Bewerberfoto' (length=12)



This is an array, not an array of objects. So, I seems clear,why error will be thrown out.How can this be? var_dump of $query still has been an array of objects

Got it: If I will change query like this, I will get all values of table depending on parameter id





    public function search($params, $id) {

        $query = Dateianhang::find()

                ->join('LEFT JOIN', 'e_dateianhang', 'e_dateianhang.id=dateianhang.id_e_dateianhang')

                ->join('LEFT JOIN', 'person', 'person.id=e_dateianhang.id_person')

                ->join('LEFT JOIN', 'l_dateianhang_art', 'l_dateianhang_art.id=dateianhang.id_dateianhang_art')

                ->where(['id_person' => $id]);

        $dataProvider = new ActiveDataProvider([

            'query' => $query,

        ]);


        $this->load($params);

        return $dataProvider;

    }



Nevertheless, I wonder, why I got an array instead an array of objects at query before.It’d be fine, if this question colud be answered…

I think the docs are your best friend it actually explains how the dataProviders work here is a link to activeDataProvider docs

http://www.yiiframework.com/doc-2.0/guide-output-data-providers.html#active-data-provider

Is the following analysis correct?




1) person.id  1-N  e_dateianhang.id_person

2) e_dateianhang.id  1-N  dateianhang.id_e_dateianhang

3) l_dateianhang_art.id  1-N  dateianhang.id_dateianhang_art



Then the relations among the 4 models should be like the following:




1) Person  1-N  EDateianhang

        Person has many EDdateianhangs

        EDateianhang has one Person

2) EDateianhang  1-N  Dateianhang

        EDateianhang has many Dateianhangs

        Dateianhang has one EDateianhang

3) LDateianhangArt  1-N  Dateianhang

        LDateianhangArt has many Dateianhangs

        Dateianhang has one LDateianhangArt



And, if you have used Gii to generate these models, you should already have the following definitions of the relations:




/* in Person.php */

public function getEDateianhangs()

{

    return $this->hasMany(EDateianhang::className(), ['id_person' => 'id']);

}


/* in EDateianhang.php */

public function getPerson()

{

    return $this->hasOne(Person::className(), ['id' => 'id_person']);

}

public function getDateianhangs()

{

    return $this->hasMany(Dateianhang::className(), ['id_e_dateianhang' => 'id']);

}


/* in Dateianhang.php */

public function getEDateianhang()

{

    return $this->hasOne(EDateianhang::className(), ['id' => 'id_e_dateianhang']);

}

public function getLDateianhangArt()

{

    return $this->hasOne(LDateianhangArt::className(), ['id' => 'id_dateianhang_art']);

}


/* in LDateianhangArt.php */

public function getDateianhangs()

{

    return $this->hasMany(Dateianhang::className(), ['id_dateianhang_art' => 'id']);

}



Am I correct so far?

And you want to list all Dateianhangs that belongs to a certain Person, don’t you?

You should know that it’s quite easy like the following when you are familiar with the Yii way of querying data from database:




$dateianhangs = Dateianhang::find()

    ->joinWith(['eDateianhang.person'])

    ->where(['person.id' => $id_person])

    ->with(['lDateianhangArt'])

    ->all();

foreach($dateianhangs as $d) {

    echo $d->eDateianhang->person->vorname . ' '

        . $d->eDateianhang->person->nachname . ' '

        . $d->dateiname . ' '

        . $d->lDateianhangArt->bezeichnung;

}



If I want to use an ActiveDataProvider and a Gridview for it, I would write a dedicate search function in DateianhangSearch model like the following:




/* DateianhangSearch.php */

public function searchByPerson($id_person)

{

    $query = Dateianhang::find()

        ->joinWith(['eDateianhang.person'])

        ->where(['person.id' => $id_person])

        ->with(['lDateianhangArt']);

    return new ActiveDataProvider(['query' => $query]);

}



And in the view:




GridView::widget([

    'dataProvider' => $dataProvider,

    'filterModel' => $searchModel,

    'columns' => [

        'eDateianhang.person.vorname',

        'eDateianhang.person.nachname',

        'dateiname',

        'lDateianhangArt.bezeichnung'

    ]);



The relational data can be very easily and effectively handled by ActiveRecord. I strongly recommend you to take your time to learn it reading the guide.

Guide > Active Record > Working with Relational Data

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#relational-data

So, I did.

'cause of that, I already got solution,as I posted above.

Thx a lot for ur efforts helping me

This thread can be closed as succesfully solved