How to show a hasMany relation in a grid

I have a table that is a hasMany relation to the main table.

Ie, Sale model hasMany saleStaff:




public function getSaleStaff()

    {

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

    }



Staff table has a column ‘staff_type’ enum that has only two possible values of ‘Lister’ or ‘Seller’.

So I would like to pull back a Lister and a Seller (initial in staff table) for each ‘sale’, something like this:

Lister | Seller


JP | FK

I’m not sure how to represent this - or how to even pull it out based on that ‘sale_type’, however if i do something like




'attribute' => 'saleStaff.staff.initials'



it gives me the ‘(not set)’ comment in the column - which i would assume is to do with it returning two rows and not just one that it can display ?

How can i do this in a gridView ?

Hope its clear :)

please has anyone seen hasMany relation in a gridview ? :)

Hello, if I understood correctly, you probably need a function to call more than one value for each row in a GridView, something like this:




//other columns

[

    'label' => 'Staff Type',

    'value' => function($model){

                return join(', ', yii\helpers\ArrayHelper::map($model->salestaff, 'id_salestaff', 'field_name_to_display'));

            },

],

//more columns



Can anyone help me ensure my relationships are correct ?

A bit more info:

I have 3 tables that i basically need to display in the single gridview.

  • property

  • sale (FK property_id)

  • sale_staff (FK sale_id, staff_id)

  • staff

Now a staff member can be a lister, a seller, or both, and each sale can have multiple lister or sellers. So table sale_staff has column ‘staff_type’ with possible enum values ‘lister’, ‘seller’.

The relationships I have:

Sale:




public function getProperty()

{

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

}


public function getSaleStaff()

{

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

}



Sale_Staff:




public function getStaff()

{

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

}


public function getSale()

{

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

}



Staff:




    public function getSaleStaff()

    {

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

    }



How do I match up the join table sale_staff with the sale table ? Is the structure ok or is DB incorrect ? I have done it this way to be flexible - any number of staff can be either a lister, seller, or both such as:

property street | sale price | sale date | lister | seller


Parkers Road | 400,000 | 22/06/2016| MJ | MJ, AB

  • where lister MJ, sellers MJ & AB come from the ‘staff’ table via junction table ‘SaleStaff’

  • SaleStaff table: property_id, sale_id, staff_id, staff_type (lister/seller)

Are these correct in order that the SaleController can have a gridview that gets the staff lister/seller members via the SaleStaff table ?

I think it’s basically OK.

But you may consider using "onCondition" to simplify the relations.

http://www.yiiframework.com/doc-2.0/yii-db-activequery.html#onCondition()-detail




/* Sale.php */


/* returns SaleStaff records including listers and sellers */

/*

public function getSaleStaff()

{

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

}

*/


/* returns only the lister SaleStaff records */

public function getListerSaleStaffs()

{

    return $this->hasMany(SaleStaff::className(), ['sale_id' => 'id'])

        ->onCondition(['staff_type' => SaleStaff::TYPE_LISTER]);

}


/* returns only the seller SaleStaff records */

public function getSellerSaleStaffs()

{

    return $this->hasMany(SaleStaff::className(), ['sale_id' => 'id'])

        ->onCondition(['staff_type' => SaleStaff::TYPE_SELLER]);

}



The same thing can be done for Staff.

Now you can write something like this:




$sales = Sale::find()->with([

    'property',

    'listerSaleStaffs',

    'listerSaleStaffs.staff', 

    'sellerSaleStaffs',

    'sellerSaleStaffs.staff', 

])->where(..)->all();


foreach($sales as $sale) {

    echo 'street:' . $sale->property->street . "\n";

    echo 'price:' . $sale->price . "\n";

    echo 'date:' . $sale->date . "\n";

    $listers = [];

    foreach($sale->listerSaleStaffs as $listerSaleStaff) {

        $listers[] = $listerSaleStaff->staff->name;

    }

    echo 'listers:' . implode(',', $listers) . "\n";

    $sellers = [];

    foreach($sale->sellerSaleStaffs as $sellerSaleStaff) {

        $sellers[] = $sellserSaleStaff->staff->name;

    }

    echo 'sellers:' . implode(',', $sellers) . "\n";

}



Thank you so much for this - very helpful in understanding the process. I have just one last question to tidy it up - can u help out or point me to example that can incorporate your code that goes through and implodes the listers/sellers for display into the gridview widget ?

thanks again

Displaying hasMany relations in GridView can be written like this:




echo GridView::widget([

    'dataProvider' => $dataProvider,  // provided by SaleSearch::search() 

    'columns' => [

        ['class' => 'yii\grid\SerialColumn'],

        'property.street',

        'price',

        'date',

        [

            'label' => 'Listers',

            'value' => function ($data) {

                $listers = [];    

                foreach($data->listerSaleStaffs as $listerSaleStaff) {

                    $listers[] = $listerSaleStaff->staff->name;

                }

                return implode(',', $listers);

            },

        ],

        [

            'label' => 'Sellers',

            'value' => function ($data) {

                $sellers = [];    

                foreach($data->sellerSaleStaffs as $sellerSaleStaff) {

                    $sellers[] = $sellerSaleStaff->staff->name;

                }

                return implode(',', $sellers);

            },

        ],

    ],

]);



http://www.yiiframework.com/doc-2.0/guide-output-data-widgets.html#gridview

http://www.yiiframework.com/doc-2.0/yii-grid-datacolumn.html#$value-detail

u are an ornament to this forum, thank you so much !

You may consider creating virtual attributes of "listerNames" and "sellerNames" via getter methods in your Sale model.




public function getListerNames()

{

    $listers = [];    

    foreach($data->listerSaleStaffs as $listerSaleStaff) {

        $listers[] = $listerSaleStaff->staff->name;

    }

    return implode(',', $listers);

}

public function getSellerNames()

{

    ...

}



Then the code for the GridView could be as simple as the following:




echo GridView::widget([

    'dataProvider' => $dataProvider,

    'columns' => [

        ['class' => 'yii\grid\SerialColumn'],

        'property.street',

        'price',

        'date',

        'listerNames',

        'sellerNames',

    ],

]);



oh wow, that is so good !

My next step is to do the reverse - take multiple names and insert those into the SaleStaff model via a form. I will tackle that and be back! :)

ok i’m going to keep this here instead of new thread to keep the flow going, but i’ve encountered my first conceptual question regarding the reverse - ie, having a form that saves data to the 3 tables; sale, property and saleStaff.

So in the _form i have easily added both the sale and property fields i need.

The question now is, in order to display a list of staff initials from the staff model, i am using




<?= $form->field($staff, 'initials')->dropDownList(

        ArrayHelper::map(Staff::find()->all(),'id','initials'),

        ['prompt' => 'Select Lister']

        )

    ?>



The problem with this obviously, is that the staff model is not where i need to save the lister / seller details - that goes to saleStaff where i need; property_id, sale_id, staff_id and staff_type (lister/seller).

So first question is, how do i display a list of the staff members, but have the form save those to the correct saleStaff model ?

Please take a look at the following wiki. It illustrates how to save/update hasMany related items via junction table using CheckboxList or ListBox.

http://www.yiiframework.com/wiki/836/how-to-use-listbox-and-checkboxlist/

thanks softark - sometimes its difficult to find things when u dont know what to look for :)

hello again - i am revisiting this issue after some time diverted to other things.

Looking thru PostCategories example, i am a bit unsure how it fits with my scenario - or whether i need to restructure my tables differently ?

Can you suggest whether i should adjust my architecture, or it can fit into the above example ?

Remembering i have:

Table: Property

Table: Sale

Table: Staff

Table: Sale_Staff: property_id, sale_id, staff_id, staff_type (lister/seller)

Does this still work ?

What would i include in the Model for the form - as in your PostCategories example has post_id, category_ids[] - mines a touch more complex as the above structure.

Well, I think you said that the tables are like the following:




Table: Property


Table: Sale: property_id


Table: Staff


Table: Sale_Staff: sale_id, staff_id, staff_type (lister/seller)



It’s not Sale_Staff but Sale that has the “property_id” FK, right?

Now you can create a model for the form without modifying these existing models and tables.

We may name it SaleStaffForm (or whatever you would like). We just need 2 attributes for HAS_MANY relations (listers/sellers) instead of 1 (categories).




class SaleStaffForm extends Model

{

    /**

     * @var integer sale ID

     */

    $sale_id;

 

    /**

     * @var array IDs of the listers

     */

    $lister_ids = [];

 

    /**

     * @var array IDs of the sellers

     */

    $seller_ids = [];




And you can modify the example code lie the following. The code gets a little longer, but still stays as simple as it was.




    /**

     * @return array the validation rules.

     */

    public function rules()

    {

        return [

            ['sale_id', 'required'],

            ['sale_id', 'exist', 'targetClass' => Sale::className(), 'targetAttribute' => 'id'],

            // each category_id must exist in category table (*1)

            ['lister_ids', 'each', 'rule' => [

                'exist', 'targetClass' => Staff::className(), 'targetAttribute' => 'id'

            ]],

            ['seller_ids', 'each', 'rule' => [

                'exist', 'targetClass' => Staff::className(), 'targetAttribute' => 'id'

            ]],

        ];

    }

 

    /**

     * @return array customized attribute labels

     */

    public function attributeLabels()

    {

        return [

            'sale_id' => 'Post',

            'lister_ids' => 'Listers',

            'seller_ids' => 'Sellers',

        ];

    }

 

    /**

     * load the sale's staffs (*2)

     */

    public function loadStaffs()

    {

        $this->lister_ids = [];

        $sls = SaleStaff::find()->where([

            'sale_id' => $this->sale_id,

            'staff_type' => SaleStaff::TYPE_LISTER

        ])->all();

        foreach($sls as $sl) {

            $this->lister_ids[] = $sl->staff_id;

        }

        

        $this->seller_ids = [];

        $sss = SaleStaff::find()->where([

            'sale_id' => $this->sale_id,

            'staff_type' => SaleStaff::TYPE_SELLER

        ])->all();

        foreach($sss as $ss) {

            $this->seller_ids[] = $ss->staff_id;

        }

    }

 

    /**

     * save the sale's staffs (*3)

     */

    public function saveStaffs()

    {

        /* clear the staffs of the sale before saving */

        SaleStaff::deleteAll(['sale_id' => $this->sale_id]);

        if (is_array($this->lister_ids)) {

            foreach($this->lister_ids as $staff_id) {

                $ss = new SaleStaff();

                $ss->sale_id = $this->sale_id;

                $ss->staff_id = $staff_id;

                $ss->staff_type = SaleStaff::TYPE_LISTER;

                $ss->save();

            }

        }

        if (is_array($this->seller_ids)) {

            foreach($this->seller_ids as $staff_id) {

                $ss = new SaleStaff();

                $ss->sale_id = $this->sale_id;

                $ss->staff_id = $staff_id;

                $ss->staff_type = SaleStaff::TYPE_SELLER;

                $ss->save();

            }

        }

    }

 

    /**

     * Get all the available staffs (*4)

     * @return array available staffs

     */

    public static function getAvailableStaffs()

    {

        $staff = Staff::find()->order('name')->asArray()->all();

        $items = ArrayHelper::map($staff, 'id', 'name');

        return $items;

    }

}



thankyou softark , i am indebted to your time.

I however am not correlating something in regards to the way it picks up the selection for the sale_staff.

I am using a dropdown box





<?= $form->field($saleStaff, 'lister_ids')

        ->dropDownList($staffs,

        ['prompt' => 'Select Lister']

        )

    ?>




$saleStaff is the model, $staffs is the array built by the function getAvailableStaffs

in order to allow selection for both lister and seller (i will in future use javascript to allow user to add a new selection to take advantage of the hasMany relation - not too sure how that will work yet either :) )

Using this, the lister_ids[] and seller_ids[] is not being populated with anything and i can’t quite correlate how the loadStaffs() function is supposed to do this ?





    /**

     * load the sale's staffs (*2)

     */

    public function loadStaffs()

    {


        $this->lister_ids = [];

        $sls = SaleStaff::find()->where([

            'sale_id' => $this->sale_id,

            'staff_type' => 'lister'

        ])->all();

        foreach($sls as $sl) {

            $this->lister_ids[] = $sl->staff_id;

        }

        

        $this->seller_ids = [];

        $sss = SaleStaff::find()->where([

            'sale_id' => $this->sale_id,

            'staff_type' => 'seller'

        ])->all();

        foreach($sss as $ss) {

            $this->seller_ids[] = $ss->staff_id;

        }

    }




Isn’t the job of this function to load in the selections from the view ?

If so, why is this doing a find() on SaleStaff since the data is not saved yet ?

the find()->where above will always return null in this case ?

So I am not correlating how the view is tied to the model saleStaffForm in this instance. I feel like the above loadStaffs() function should simply be getting the data from the _form field selected, but not sure what i’m missing ?

Is my controller logic ok ? Its slightly different to your example, but its taken from gii code i’ve done elsewhere. The property and sale models are being saved correctly.




public function actionCreate()

{

$model = new sale();

$property = new property();

$saleStaff = new saleStaffForm();


if ($model->load(Yii::$app->request->post()) && $property->load(Yii::$app->request->post())) {


            $property->save();


            $model->property_id = $property->id;

            $model->save();


            $saleStaff->sale_id = $model->id;

            $saleStaff->property_id = $property->id;

            $saleStaff->saveStaffs();

            

        } else {

            $saleStaff->loadStaffs();

            $staffs = SaleStaffForm::getAvailableStaffs();

            return $this->renderAjax('create', [

                'model'     => $model,

                'property'  => $property,

                'saleStaff' => $saleStaff,

                'staffs'    => $staffs,

            ]);

        }

}



The wiki article shows how to add/edit has_many related items to an existing model. It doesn’t show how to create a main model with its related items at once.

loadStaffs() method loads the ids of current listers and sellers into lister_ids and seller_ids. It’s natural that they will be empty for a newly created Sale model. You can ignore the method if you are trying to create new Sale model. But it will be useful when you update an existing Sale model.

You have to load $saleStaff as well as $model and $property in your controller.

oh i see, thats what confused me.

Can you help me understand how the controller can pull the selection from the dropdownlist view, so that i can feed the lister_ids[] and seller_ids[] ?

If my form contains:




<?= $form->field($saleStaffForm, 'lister_ids')

        ->dropDownList($staffs,

        ['prompt' => 'Select Lister']

        )

    ?>



is that saying that the field selected will be ‘saved’ against the ‘lister_ids’ field in the $saleStaffForm model ?

thx again

ok so digging a little further - i vardumped &#036;_POST['SaleStaffForm'] and i can actually see the correct values i’m looking for. If anyone can help me understand why i cant see any values for lister_ids/seller_ids in $saleStaffForm object, i dont really want to use the $_POST variable (or at least i don’t think i do :) )