Help to get value from pivot table

Hi every one. I try for many weeks get a value from hasMany relationship through of pivot table.

See the image 1 (schema.png) I want the result as the image 2 (processo-grid.png).

My pivot table is "MacroprocessoArea" I want get the Area name through "processo" GridView and DetailView.

In GridView I try to use: ‘value’ => ‘macroprocessoAreas.NAME’, ‘value’ => ‘macroprocessoAreas.area.SIGLA’, ‘value’ => ‘macroprocesso.macroprocessoAreas.area.SIGLA’, and ‘macroprocesso.macroprocessoAreas.SIGLA’.

My model Processo is:

public function getMacroprocessoAreas()

    {

        return $this->hasMany(MacroprocessoArea::className(), ['PROCESSO_ID' => 'ID'])->via(macroprocesso);

    }

    


    /**

     * @return \yii\db\ActiveQuery

     */

    public function getMacroprocesso()

    {

        return $this->hasOne(Macroprocesso::className(), ['ID' => 'MACROPROCESSO_ID']);

    }

ProcessoSearch.php

public function search($params)

    {

        $query = Processo::find();


        $dataProvider = new ActiveDataProvider([

            'query' => $query,

        ]);

        

        $dataProvider->setSort([

            'defaultOrder' => [

                'AREA_ID' => SORT_ASC,

                'MACROPROCESSO_ID' => SORT_ASC,

                'PROCESSO' => SORT_ASC,

            ],

            'attributes' => [

                'AREA_ID' => [

                    'asc' => ['SIGLA' => SORT_ASC],

                    'desc' => ['SIGLA' => SORT_DESC],

                    'default' => SORT_ASC,

                ],

                'MACROPROCESSO_ID' => [

                    'asc' => ['MACROPROCESSO' => SORT_ASC],

                    'desc' => ['MACROPROCESSO' => SORT_DESC],

                ],

                'PROCESSO'

            ],

        ]);


        $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->select("AUT.AUT_SETORES.SIGLA as CU, SGR_PROCESSO.ID, SGR_PROCESSO.PROCESSO, SGR_PROCESSO.MACROPROCESSO_ID");

        

        /**

         * Jaz um join com o relacionamento que exite no model.

         * Dessa forma traz somente o(s) campo(s) que interessam na query

         * Caso queira trazer tudo bas usar 

         * $query->joinWith('nomeDoRelacionamento')

         */

        $query->joinWith('macroprocessoAreas');

        $query->innerJoin('AUT.AUT_SETORES', 'AUT.AUT_SETORES.ID = SGR_MACROPROCESSO_AREA.AREA_ID');

        

        $query->andFilterWhere([

            'ID' => $this->ID,

            //A chave estrangeira precisa ser retirada

            //'MACROPROCESSO_ID' => $this->MACROPROCESSO_ID,

        ]);


        $query->andFilterWhere(['like', 'UPPER(PROCESSO)', strtoupper($this->PROCESSO)])

            ->andFilterWhere(['like', 'UPPER(SGR_MACROPROCESSO.MACROPROCESSO)', strtoupper($this->MACROPROCESSO_ID)])

            ->andFilterWhere(['like', 'UPPER(AUT.AUT_SETORES.SIGLA)', strtoupper($this->AREA_ID)]);

        

        return $dataProvider;

    }

The code generated by ProcessoSearch.php runs as I want in Oracle Interface. Why I can’t show it?

I dont’t know what do anymore.

You need to use the pivot table as the model for the view. Also your mcroproccess table appears to be misspelled. It appears you also don’t need the macroprocesso_id in the processo table. However, i don’t know what exactly you’re doing with your data so it’s hard to tell.

I’d use the macroprocessare as the model for the search. You can use the others however it becomes more difficult to retrieve the information in a way that is useful.

The simplest form this is what you need to do to get it to show the data to show how you want is below. Searching and sorting will not work. Here is a wiki on how to get searching and sorting to work.

The model should be macroprocessarea (pivot table)

namespace fronntend\models;


class MacroprocessoArea extends ActiveRecord {


    public static function tableName() {

        return 'Macroprocesso_Area';

    }


    public function getArea() {

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

    }


    public function getMacroprocesso() {

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

    }


    public function getProcesso() {

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

    }


}

The Search model needs to extend the Macroprocessoarea model

use frontend\models\MacroprocessoArea;


class MacroprocessoAreaSerach extends MacroprocessoArea {

    public function rules() {

        return [

            [['macroprocesso_id','area_id','processo_id'], 'safe'],

        ];

    }


    public function search($params) {

        $query = MacroprocessoArea::find()->with('area','macroprocesso','processo');

        $dataProvider = new ActiveDataProvider([

            'query' => $query

        ]);

        return $dataProvider;

    }

}

Controller

 public function actionIndex() {

        $searchModel = new MacroprocessoAreaSerach();

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

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

                    'searchModel' => $searchModel,

                    'dataProvider' => $dataProvider

        ]);

    }

View

echo GridView::widget([

    'dataProvider' => $dataProvider,

    'filterModel' => $searchModel,

    'columns' => [

        //relation.relatedField

        'area.nome',

        'macroprocesso.macroprocesso',

        'processo.processo'

    ]

]);

Hi @skworden, I tried your example, but I need get my results form ‘ProcessoModelSearch’ instead of ‘MacroprocessoAreaSearch’. If I use MacroprocessoAreaSearch I get all values even it not saved.

Ex.: When I save a ‘Macroprocesso’ from Macroprocesso form it’s showed the register in the processo list (index.php). Processo must be show after it was saved not before.

Other thing that happen is, I lost the id parameter (pk from processo table) when I edit and delete the item.

I solve my problem with @Eric Helps.

It was needed create field in my model called $SIGLA for example.

Tks for @ll