help / suggestions for proper db retreival for gridview / handsontable

billhrice · November 11, 2015, 10:38pm

I am struggling with getting data for user interface viewing and need some help to be pointed in the right direction.

The current goal at hand: - Create an excel like table from a large amount of db data oriented vertically with hover tips and conditional formatting. Oriented Vertically: IE, Table "Column Headers" are actually the first column instead of the first row.

I have chosen handsontable (javascript table utility) because of the flexible functionality. I have to convert PHP arrays to JSON per research. The table draws nicely, but for now I just want to get the data printed in my view. I’ll worry about handson later.

The current "task" at hand: Learning YII2 and getting the data I need from my models into the view! I want to do this the "Proper YII2" way as opposed to creating lots of loops manually to get through the model data. Maybe I do have to do that?

The current question: What are the differences on how a CRUD Search model works and standard db models? IE, one uses searchModel/dataProvider is not data and the other just returns a model.

I can populate data with GridView / CRUD with searchModel/dataProvider, but I just want access to the data. The searchModel/dataProvider just look like filters. Model can return an array of models, but I don’t know the proper way to access it. An array of models doesn’t appear to fit into Yii GridView or DetailView.

Controller w/ MODEL (validate only appears to work if model is not an array of objects, hence model[0]. ):




        $date = date('md');

        $model = \common\models\Gatewayevent::find()->where(['eventscheduleddate' => $date ])->all();

        if($model[0]->validate()) {

            return $this->render('index', ['model' => $model]);

        } else

            return $this->render('index');

View (DetailView doesn’t appear to work with an array of models):


DetailView::widget(['model' => $model, 'attributes' => ['eventheadendid','eventchannelid','eventfilename',],])

Controller w/ searchModel/dataProvider


        $searchModel = new GatewayeventSearch();

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


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

            'searchModel' => $searchModel,

            'dataProvider' => $dataProvider,

        ]);

View w/ searchModel (GridView appears to work with this fine):


GridView::widget([

        'dataProvider' => $dataProvider,

        'filterModel' => $searchModel,

        'columns' => [

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


            'eventheadendid',.....

....

Please suggest the best way to get db data into an array for JSON Encode -> Handsontable. Any help would be appreciated. I think I need to take a break, been playing/looking at code too long.

fabriziocaldarelli · November 11, 2015, 10:42pm

You have to use dataProvider if you are working with GriView or ListView (your case).

Then you can take models from dataProvider using getModels().

softark · November 11, 2015, 11:09pm

A search model (GatewayeventSearch in your case) is a model that 1) conveys the search/filtering parameters and 2) implements the search/filtering logic for the main model (Gatewayevent). For a simple scenario, you could write these things within the main model, without creating a search model. But usually we introduce a search model, because it help us organize cleaner code.

A data provider is a kind of query builder that automates the "order by" and "offset/limit" parts of the query using Sort and Pagination objects. GridView and ListView use it to get an array of models using its "getModels()" method that executes the constructed query.

softark · November 12, 2015, 12:09pm

In order to get an array of models, you just have to construct an ActiveQuery and execute it.




$query = Gatewayevent::find();  // Create ActiveQuery object for Gatewayevent model

$query->where(['eventscheduleddate' => date('md')]); // set a condition

$models = $query->all();  // Executes the query to retrieve the models

It doesn’t require a search model nor a data provider.

But when you want to apply the filtering condition using user input, then the search model will come in handy. You can write something like this:





public $dateFrom;

public $dateTo;


public function retrieveModels($params)

{

    $this->load($params);


    $query = Gatewayevent::find();

    $query->andFilterWhere(['eventscheduleddate' => $this->date]);

    $query->andFilterWhere(['>=', 'eventscheduleddate', $this->dateFrom]);

    $query->andFilterWhere(['<=', 'eventscheduleddate', $this->dateTo]);

    ...

    $models = $query->all();

    return $models;

}

In the above, $dateFrom and $dateTo are additional attributes that you may want to use in search. By using this search model, you may easily write a form for searching.

And, when you want the returned array of models sorted and paginated, you will probably want to use an ActiveDataProvider. It uses Pagination and Sort objects to modify the query. They work together with the link pagers and the sort links in the view. They are usually integrated into GridView or ListView, but you may create them on your own using LinkPager and Sort.

The “search” method returns an instance of ActiveDataProvider, not the array of models retrieved by the query. It is the data widget like GridView and ListView that calls “getModels()” method of the data provider to get the resulting array of models. Of course you may call it yourself, when you don’t use a data widget.

http://www.yiiframework.com/doc-2.0/guide-output-pagination.html

http://www.yiiframework.com/doc-2.0/guide-output-sorting.html

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

billhrice · November 12, 2015, 7:32pm

Lots of great feedback here. Thank you everyone! I will research further, make some suggested adjustments and report how it went.

billhrice · January 8, 2016, 3:34am

I posted a reply from another person looking for dual list boxes, but no one has replied. I got great help here and my project has started to come together.

I’m currently trying to insert/delete based upon a dual list box selection. Seems simple, but I’m week in forms.

I couldn’t get dual list boxes to work (plugins are installed, just can’t figure out how to use them appropriately).

Here’s a screen of my homegrown interface

I have 3 tables. user table (has user id), rules table (has rule id), and a table to join them together (userid and ruleid).

I need to delete userid/ruleid from the concatenated table when unassign is clicked.

I need to insert userid/ruleid into the concatenated table when assign is clicked.

Do I need to use two forms? I’m having trouble finding Yii2 examples. How can I find the exact entry?

My view and controller code is below, but it’s so messy I’m embarrassed.

View:


<div class=”panel-body”>

…

$form = ActiveForm::begin([

                            'action' => ['index'],

                            'method' => 'get',

                        ]); 

echo $form->field($searchModel, 'contactid')->dropDownList(

                            ArrayHelper::map(User::find()->select('id, username')->all(), 'id', 'username')

                            ,['prompt' => 'SELECT USER',

                                'onchange' => 'this.form.submit()'

                            ]);

<?php  ActiveForm::end(); ?>

...

$form2 = ActiveForm::begin([

                            'id' => 'form2',

                            'action' => ['delete'],

                            'method' => 'post',

                        ]);

echo $form2->field($searchModel, 'contactruleid' )->listBox(

                            $assignedIds

                            ,[  'multiple' => false,

                                'disabled' => false,

                                'size' => 10,

                                'style' => 'width:200px'

                                ])->label('Assigned'); 

echo Html::submitButton('unassign', ['class' => 'btn btn-primary']);

<?php  ActiveForm::end(); ?>

Controller (messy and embarrassing, but initial index/view works. just can’t get insert/deletes to work):




    public function actionIndex()

    {

               

        $searchModel = new GatewaycontactruleSearch();

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


        $gwContactRules = $dataProvider->getModels();

        

        $assignedIds = array();

        $unassignedIds = array();

        $gwRulequery = NULL;

        

        // Get the user id and populate the "assigned"/"unassigned" fields for the queried user id.

        $idc = ArrayHelper::getValue(Yii::$app->request->get(), 'GatewaycontactruleSearch.contactid');

        if(!empty($idc)) {

            // get all rules.  if the models contactruleid exists in the ruleid, then it's assigned.

            $allgwRulequery = Gatewayrule::find()->select('ruleid, ruledescription')->orderBy('ruleid')->all();

            $found = FALSE;

            foreach($allgwRulequery as $allRules) {

                foreach($gwContactRules as $crules) {

                    if($crules->contactruleid == $allRules->ruleid) {

                        $found = TRUE;

                        $assignedIds[$crules->contactruleid] = $crules->contactruleid . "-" . $allRules->ruledescription;

                    }

                }

                if($found) {

                    $found = FALSE;

                } else {

                    $unassignedIds[$allRules->ruleid] = $allRules->ruleid . "-" . $allRules->ruledescription;

                }

            }

        }   

        

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

            'searchModel' => $searchModel,

            'dataProvider' => $dataProvider,

            'assignedIds' => $assignedIds,

            'unassignedIds' => $unassignedIds,

            'gwRulequery' => $gwRulequery,

            'gwContactRules' => $gwContactRules,

            'idc' => $idc,

        ]);

    }

.....

    public function actionDelete()

    {

        $model = new Gatewaycontactrule();

        $idc = $model->load(Yii::$app->request->post());

        $this->findModel($model->contactid, $model->contactruleid)->delete();


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

            'model' => $model,

            'idc' => $idc,

        ]]);

    }

Basic Table Schemas:




user table (yii2 advanced default)

        Column        |          Type          |                     Modifiers                     | Storage  | Stats target | Description

----------------------+------------------------+---------------------------------------------------+----------+--------------+-------------

 id                   | integer                | not null default nextval('user_id_seq'::regclass) | plain    |              |

 username             | character varying(255) | not null                                          | extended |              |

..

 email                | character varying(255) | not null                                          | extended |              |

..

Indexes:

    "user_pkey" PRIMARY KEY, btree (id)

    "user_email_key" UNIQUE CONSTRAINT, btree (email)

    "user_password_reset_token_key" UNIQUE CONSTRAINT, btree (password_reset_token)

    "user_username_key" UNIQUE CONSTRAINT, btree (username)

Has OIDs: no


rule table

       Column       |            Type             |                    Modifiers                     | Storage  | Stats target | Description

--------------------+-----------------------------+--------------------------------------------------+----------+--------------+-------------

 ruleid             | integer                     | not null default nextval('ruleid_seq'::regclass) | plain    |              |





gatewaycontactrule table 

    Column     |  Type   |     Modifiers      | Storage | Stats target | Description

---------------+---------+--------------------+---------+--------------+-------------

 contactid     | integer | not null default 0 | plain   |              |

 contactruleid | integer | not null default 0 | plain   |              |

softark · January 9, 2016, 12:43am

Although I haven’t use it yet, Dual Listbox looks very smart and gives a good experience to the end users.

But I agree that it lacks a good documentation.

As far as I understand, you can treat it as a single Listbox with multiple selection.

For example, you construct a single listbox like this:




    <select multiple="multiple">

      <option value="option1">Option 1</option>

      <option value="option2">Option 2</option>

      <option value="option3" selected="selected">Option 3</option>

      <option value="option4">Option 4</option>

      <option value="option5">Option 5</option>

      <option value="option6" selected="selected">Option 6</option>

      <option value="option7">Option 7</option>

      <option value="option8">Option 8</option>

      <option value="option9">Option 9</option>

      <option value="option0">Option 10</option>

    </select>

And then the dual listbox will convert it to a pair of listboxes like the following:




    <select>

      <option value="option1">Option 1</option>

      <option value="option2">Option 2</option>

      <option value="option4">Option 4</option>

      <option value="option5">Option 5</option>

      <option value="option7">Option 7</option>

      <option value="option8">Option 8</option>

      <option value="option9">Option 9</option>

      <option value="option0">Option 10</option>

    </select>

    <select>

      <option value="option3">Option 3</option>

      <option value="option6">Option 6</option>

    </select>

And in the server side, you can still treat the posted values of them as those of a single listbox … probably … I’m not very sure.

softark · January 9, 2016, 12:54am

In the view, the second form for deletion uses $searchModel, that is "GatewaycontactruleSearch".

But you are trying to get the posted values as "Gatewaycontactrule" model in "actionDelete".

That’s why the deletion won’t work.

If you are to add/delete rules to/from a user one by one, then using "Gatewaycontactrule" model for the forms will do the job.

You will need 2 forms, one for the adding and the other for the deleting, if you want to stick to the current UI without using the Dual listbox.

billhrice · January 11, 2016, 4:23am

Thanks I got it working and simplified everything. I put in hidden fields so Contact ID could be used for insert/deletes in the separate fields, not sure if I needed them…

Controller.


    

public function actionDelete()

    {

        // Create new rule, load in values, delete and redirect to keep from new page render.

        $model = new Gatewaycontactrule();

        $model->load(Yii::$app->request->post());


        if($this->findModel($model->contactid, $model->contactruleid)->delete() ) {

            return $this->redirect(['index', 'Gatewaycontactrule[contactid]' => $model->contactid]);

        } else {

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

            'model' => $model,

        ]]);

        }

    }


    public function actionIndex()

    {

        $model = new Gatewaycontactrule();        

        $model->contactid = ArrayHelper::getValue(Yii::$app->request->get(), 'Gatewaycontactrule.contactid');


        $assignedIds = array();

        $unassignedIds = array();

        

        if(!empty($model->contactid)) {

            // logic for building assigned/unassigned lists.

        }

        

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

            'model' => $model,

            'assignedIds' => $assignedIds,

            'unassignedIds' => $unassignedIds,

        ]);

    }

View has 3 forms




$form = ActiveForm::begin([

                            'action' => ['index'],

                            'method' => 'get',

                        ]); 

                     

                     echo $form->field($model, 'contactid')->dropDownList(

                         ArrayHelper::map(User::find()->select('id, username')->all(), 'id', 'username')

                         ,['prompt' => 'SELECT USER',

                                'onchange' => 'this.form.submit()',

                          ]);

                     ?>

                    <?php  ActiveForm::end(); ?>


$form2 = ActiveForm::begin([

                            'id' => 'form2',

                            'action' => ['delete'],

                            'method' => 'post',

                        ]);

                            echo $form2->field($model, 'contactid')->hiddenInput()->label(false);

                            echo $form2->field($model, 'contactruleid' )->listBox(

                            $assignedIds

                            ,[  'multiple' => false,

                                'disabled' => false,

                                'size' => 10,

                                'style' => 'width:200px',

                                ])->label('Assigned');    

echo Html::submitButton('unassign', ['class' => 'btn btn-primary']); 

ActiveForm::end(); 


$form3 = ActiveForm::begin([

                                'action' => ['createtwo'],

                                'method' => 'post',

                            ]);


echo $form3->field($model, 'contactid')->hiddenInput()->label(false);

                        echo $form3->field($model, 'contactruleid' )->listBox(

                        $unassignedIds

                        ,[  'multiple' => false,

                            'disabled' => false,

                            'size' => 10,

                            'style' => 'width:200px',

                        ])->label('Unassigned');

                         // end form3 

                         ActiveForm::end();

Hope this helps someone else in the future.

softark · January 12, 2016, 4:49pm

So, I just wrote a wrapper for Bootstrap Dual Listbox.

yii2-dual-listbox

Note that the underlying javascript differs from the one that maksyutin/yii2-dual-list-box is using. Probably the one I chose is more widely used.

I tried to make my widget as simple as possible, and also tried to give a good documentation. I hope you can easily use it in your work.

I’d be glad if you would take time to try it.