Is it also possible to insert the value of a field of a form in another field unrelated to another table on the mysql database at the time of saving?

Yii 2.0 General Discussions
1

Hi everyone, I state that I am not an expert so I apologize in advance if the question is not formulated correctly or worse, it is stupid.
I would need the values ​​that I enter through the form (which in turn takes the values ​​of the other models in each dropdownlist for each field) to be recorded also on each mtsql table of competence.

At the level of views the problem does not arise because everything works correctly, but on the mysql tables the values ​​that should update on the basis of the form on which I select the various fields remain empty or do not update.

The problem is relative whether it is a count (how many times I have used a value in the form) or that it must report at least the ID of the value in the column of another mysql table.

I have a ‘managementpc’ table where I inserted the function:

        public function addOs($insert, $changedAttributes){
        if($insert){
            $this->sistop->updateCounters(['qusistop' => 1]);
        }
    }

This function allows me to add, update the count in the column of the model ‘qusistop’ and in the view it works correctly.

I should enter a sql query command to do the same on the database too.

On the Yii guide, the case study of inserting values ​​also on the column of the mysql table and not related to the database level, I have not found it and I cannot find ideas for this specific problem.

I think it’s an anomalous request and I don’t know if it’s feasible.

I kindly ask for your help.

2

It’s, indeed, not clear what do you need to achieve. When handling a form you can do as many database writes as you need.

1 Like
3

A view form is usually connected to a controller action with a single model. It gathers information in terms of the fields of the form model.

But the controller action is able to handle as many other models as it needs. You can use an input value of the form model to save another model.

    if ($model->save()) {
        $otherModel = new OtherModel();
        $otherModel->some_field = $model->field;
        $otherModel->save();
    }
1 Like
4

Thanks @samdark and @softark.

Here is a portion of my mysql project:

Schema_MYSQL
Schema_MYSQL1440×1024 85.2 KB

In this represented portion (which reflects practically the whole project) the point is this: save the changes also on the database. Therefore, depending on the table, I should save the count or ID.

In the Gestionepc.php model, to count the ‘os_id’ (and it’s works)

public function addOs($insert, $changedAttributes)
{
    if ($insert) {
        $this->os->updateCounters(['qusistop' => 1]);
    }
}

and to view the fields instead of the IDs

public function getOs()
{
    return $this->hasOne(Os::className(), ['id_os' => 'os_id']);
}

public function getSistop()
{
    return $this->os->sistop;
}

public function getNumerazioni()
{
    return $this->hasOne(Numerazioni::className(), ['id_numerazioni' => 'numerazioni_id']);
}

public function getNumerazione()
{
    return $this->numerazioni->numerazione;
}

public function getIndirizzip()
{
     return $this->hasOne(Indirizzip::className(), ['id_indirizzip' => 'indirizzip_id']);
}

public function getIndirizzoip()
{
     return $this->indirizzip->indirizzoip;
}

In the form of ‘gestionepc’ I enter all the data via dropdownlist for each field (there are many):

    <?php
    echo $form->field($model, 'os_id')->widget(Select2::classname(), [
        'data' => ArrayHelper::map(Os::find()->all(), 'id_os', 'sistop'),
        'options' => ['placeholder' => 'Seleziona Sistema Operativo...'],
        'pluginOptions' => [
            'allowClear' => true
        ],
    ]);
    ?>

    <?php
    echo $form->field($model, 'numerazioni_id')->widget(Select2::classname(), [
        'data' => ArrayHelper::map(Numerazioni::find()->all(), 'id_numerazioni', 'numerazione'),
        'options' => ['placeholder' => 'Seleziona numero...'],
        'pluginOptions' => [
            'allowClear' => true
        ],
    ]);
    ?>

   <?php
   echo $form->field($model, 'indirizzip_id')->widget(Select2::classname(), [
     'data' => ArrayHelper::map(Indirizzip::find()->all(), 'id_indirizzip', 'indirizzoip'),
     'options' => ['placeholder' => 'Seleziona IP...'],
     'pluginOptions' => [
     'allowClear' => true
      ],
    ]);
    ?>

In index.php of ‘gestionepc’ I entered like this (and it works correctly):

[
    'attribute'=>'numerazioni_id',
    'value'=>'numerazioni.numerazione',
],
	
[
    'attribute'=>'os_id',
    'value'=>'os.sistop',
],

[
    'attribute'=>'indirizzip_id',
    'value'=>'indirizzip.indirizzoip',
],

While in the view of ‘os’

    [
            'attribute' => 'qdsistop',
    ],
    [
        'attribute' => 'qusistop',
        'value' => function ($osModel) {
            return $osModel->getGestionepc()->count();
        }
    ],
    [
        'attribute' => 'qrsistop',
        'value' => function ($data) {
            return $data->qdsistop - $data->getGestionepc()->count();
        }
    ],

Everything works in the views, the counts are correct, the problem that I can not solve is that having the need that these counts and associations are also saved on the database (and therefore for example that on the ‘numbers’ table the ID of the associated IP address and the counts are also saved in the ‘os’ table) the columns (mysql) remain at 0 / Null.

5

@softark I should edit the ControllerPcController.php controller in my case (and consequently also the action update)?

public function actionCreate()
{
    $model = new Gestionepc();

    if ($model->load(Yii::$app->request->post()) && $model->save()) {
        return $this->redirect(['view', 'id' => $model->id_gestionepc]);
    }

    return $this->render('create', [
        'model' => $model,
    ]);
}
6

I would normalize OS table by dropping qusistop (qty used) and qrsistop (qty remaining) columns, because you should be able to calculate them on the fly without any performance problem in the usual use cases.

But if you do need to use qusistop column and want to update it when you write gestionepc table, you have to do the following:

  1. Create gestionepc > Increment qusistop
  2. Update gestionepc > Decrement old qusistop & Increment new qusistop
  3. Delete gestionepc > Decrement qusistop

IMO, the latter approach tends to be messy and error prone.

As for the former approach, the following section of the guide is quite useful. Please take a look at it.

Guide > Active Record > Selecting extra fields (https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#selecting-extra-fields)

7

Thanks @softark I believe that the first option is much more correct and it works like this at the moment, the problem arises when from the ‘gestionepc’ form I should enter (in this case a new PC) and I find myself in the various dropdown lists also values ​​that I previously used and that for some cases must be unique. In practice, if I was able to write to the database (but it seems to me rather cumbersome) I could place conditions directly on the fields in order to display only the unused values ​​from the dropdown lists while currently I don’t think this can be done because by entering conditions and then querying the database, these obviously would not find the results. I don’t know if I expressed myself correctly and illustrated the problem.

8

I’m afraid I don’t understand your use case.
Would you please elaborate the problem with a concrete example?

9

Yes, in gestionepc->form.php

        <?php
        echo $form->field($model, 'numerazioni_id')->widget(Select2::classname(), [
            'data' => ArrayHelper::map(Numerazioni::find()->all(), 'id_numerazioni', 'numerazione'),
            'options' => ['placeholder' => 'Seleziona numero...'],
            'pluginOptions' => [
                'allowClear' => true
            ],
        ]);
        ?>

To ensure that only unused values ​​are displayed on the dropdownlist, I should add a condition where but this works on the database.

I try to modify so:

'data' => ArrayHelper::map(Numerazioni::find()->where(['ipass'=>Null])->all(), 'id_numerazioni', 'numerazione'),

Error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ipass' in 'where clause'
The SQL being executed was: SELECT * FROM `numerazioni` WHERE `ipass` IS NULL
Error Info: Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'ipass' in 'where clause'
)

I am putting a condition by querying the database and therefore I am wrong, I am asking if the value is null in the column ‘ipass’ of numbers (which does not exist on the database).

10

You mean that ipass does not exist in numerazioni table while it does exist in Numerazioni model as an attribute?

Then you have 2 options:

  1. Construct an equivalent SQL that emulates ipass.
  2. Filter items for the dropdownlist on PHP level.

An example for the latter will go like this:

<?php
$models = Numerazioni::find()->all();
$items = [];
foreach($models as $model) {
    if ($model->ipass === NULL) {
        $items[] = $model;
    }
}
$data = ArrayHelper::map($items, 'id_numerazioni', 'numerazione');
echo $form->field($model, 'numerazioni_id')->widget(Select2::classname(), [
    'data' => $data,
    'options' => ['placeholder' => 'Seleziona numero...'],
    'pluginOptions' => [
       'allowClear' => true
   ],
]);
1 Like
11

I try this with column ‘ipass’ in ‘numerazioni’ table sql:

    <?php
        $models = Numerazioni::find()->all();
        $items = [];
        foreach($models as $model) {
            if ($model->ipass === NULL) {
                $items[] = $model;
            }
        }
        $data = ArrayHelper::map($items, 'id_numerazioni', 'numerazione');
        echo $form->field($model, 'numerazioni_id')->widget(Select2::classname(), [
            'data' => $data,
            'options' => ['placeholder' => 'Seleziona numero...'],
            'pluginOptions' => [
                'allowClear' => true
            ],
        ]);
    ?>

but in gestionepc/form.php compare this error:
Getting unknown property: app\models\Numerazioni::numerazioni_id

And if I drop column ‘ipass’ from table ‘numerazioni’ I have this in gestionepc\form.php
Getting unknown property: app\models\Numerazioni::ipass

I believe I have fallen into confusion.
On the SQL emulation I wouldn’t have gotten there, brilliant.

12

I’m confused, too.

I don’t understand why the following error occurs.

And is ipass a column in your numerazioni table or not?
If it is, I don’t understand why you got the following error.

13

Actually I can’t find the problem, I directly place the whole code concerned, obviously there is something that I have left out even if I have checked and double checked many times.

Numerazioni SQL table

numerazioni_sql_table
numerazioni_sql_table762×492 48.9 KB

Model Numerazioni 
<?php

namespace app\models;

use Da\User\Model\User;
use Yii;
use yii\behaviors\BlameableBehavior;
use yii\behaviors\TimestampBehavior;
use yii\db\Expression;

/**
 * This is the model class for table "numerazioni".
 *
 * @property int $id_numerazioni
 * @property string $numerazione Nr.
 * @property int|null $ipass IP associato
 * @property string $created_at Data inserimento
 * @property int $created_by Inserito da
 * @property string $updated_at Data modifica
 * @property int $updated_by Modificato da
 *
 * @property Gestionepc $gestionepc
 * @property User $createdBy
 * @property User $updatedBy
 */
class Numerazioni extends \yii\db\ActiveRecord
{
    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return 'numerazioni';
    }

    public function behaviors()
    {
        return [
            [
                'class' => TimestampBehavior::className(),
                'createdAtAttribute' => 'created_at',
                'updatedAtAttribute' => 'updated_at',
                'value' => new Expression('NOW()'),
            ],
            [
                'class' => BlameableBehavior::className(),
                'createdByAttribute' => 'created_by',
                'updatedByAttribute' => 'updated_by',
            ],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['numerazione'], 'required'],
            [['created_by', 'updated_by'], 'integer'],
            [['ipass', 'gestionepc.indirizzoip', 'created_at', 'updated_at'], 'safe'],
            [['numerazione'], 'string', 'max' => 255],
            [['numerazione'], 'unique'],
            [['created_by'], 'exist', 'skipOnError' => true, 'targetClass' => User::className(), 'targetAttribute' => ['created_by' => 'id']],
            [['updated_by'], 'exist', 'skipOnError' => true, 'targetClass' => User::className(), 'targetAttribute' => ['updated_by' => 'id']],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'id_numerazioni' => Yii::t('app', 'Id Numerazioni'),
            'numerazione' => Yii::t('app', 'Nr.'),
            'ipass' => Yii::t('app', 'IP associato'),
            'gestionepc.indirizzoip' => Yii::t('app', 'IP associato'),
            'created_at' => Yii::t('app', 'Data inserimento'),
            'created_by' => Yii::t('app', 'Inserito da'),
            'updated_at' => Yii::t('app', 'Data modifica'),
            'updated_by' => Yii::t('app', 'Modificato da'),
        ];
    }

    /**
     * Gets query for [[Gestionepc]].
     *
     * @return \yii\db\ActiveQuery|GestionepcQuery
     */
    public function getGestionepc()
    {
        return $this->hasOne(Gestionepc::className(), ['numerazioni_id' => 'id_numerazioni']);
    }

    /**
     * Gets query for [[Gestionepc]].
     *
     * @return \yii\db\ActiveQuery|GestionepcQuery
     */
    public function getIndirizzip()
    {
        return $this->hasOne(Gestionepc::className(), ['indirizzip_id' => 'ipass']);
    }

    /**
     * Gets query for [[CreatedBy]].
     *
     * @return \yii\db\ActiveQuery|UserQuery
     */
    public function getCreatedBy()
    {
        return $this->hasOne(User::className(), ['id' => 'created_by']);
    }

    /**
     * Gets query for [[UpdatedBy]].
     *
     * @return \yii\db\ActiveQuery|UserQuery
     */
    public function getUpdatedBy()
    {
        return $this->hasOne(User::className(), ['id' => 'updated_by']);
    }

    /**
     * {@inheritdoc}
     * @return NumerazioniQuery the active query used by this AR class.
     */
    public static function find()
    {
        return new NumerazioniQuery(get_called_class());
    }
}
Model NumerazioniSearch 
<?php

namespace app\models;

use yii\base\Model;
use yii\data\ActiveDataProvider;
use app\models\Numerazioni;

/**
 * NumerazioniSearch represents the model behind the search form of `app\models\Numerazioni`.
 */
class NumerazioniSearch extends Numerazioni
{
    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['id_numerazioni'], 'integer'],
            [['numerazione', 'gestionepc.indirizzoip'], 'safe'],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {
        $query = Numerazioni::find();
        // add conditions that should always apply here
        $query->joinWith(['gestionepc']);

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

        // grid filtering conditions
        $query->andFilterWhere([
            'id_numerazioni' => $this->id_numerazioni,
            'created_at' => $this->created_at,
            'created_by' => $this->created_by,
            'updated_at' => $this->updated_at,
            'updated_by' => $this->updated_by,
        ]);

        $query->andFilterWhere(['like', 'numerazione', $this->numerazione])
              ->andFilterWhere(['like', 'gestionepc.indirizzoip', $this->getAttribute('ipass')])
              ->andFilterWhere(['like', 'ipass', $this->getAttribute('ipass')]);

        return $dataProvider;
    }
}
Model Gestionepc 
    public function getNumerazioni()
    {
        return $this->hasOne(Numerazioni::className(), ['id_numerazioni' => 'numerazioni_id']);
    }

    public function getNumerazione()
    {
        return $this->numerazioni->numerazione;
    }

    public function getIndirizzip()
    {
        return $this->hasOne(Indirizzip::className(), ['id_indirizzip' => 'indirizzip_id']);
    }

    public function getIndirizzoip()
    {
        return $this->indirizzip->indirizzoip;
    }
14

Ah, I’m very sorry, it’s my fault. I had overwritten $model variable in the sample code.

The correct sample code should be:

<?php
$models = Numerazioni::find()->all();
$items = [];
foreach($models as $m) {  // can not use $model here
    if ($m->ipass === NULL) {
        $items[] = $m;
    }
}
$data = ArrayHelper::map($items, 'id_numerazioni', 'numerazione');
echo $form->field($model, 'numerazioni_id')->widget(Select2::classname(), [
    'data' => $data,
    'options' => ['placeholder' => 'Seleziona numero...'],
    'pluginOptions' => [
       'allowClear' => true
   ],
]);

BTW, I still don’t understand the following error:

1 Like
15

@softark once again thank you for your availability. Keeping in mind that on the ‘numerazioni’ table the ‘ipass’ column is present and it is INT (11) Null, with the correct code it does not return any errors, but in the drop-down menu of the ‘gestionepc’ form I continue to see the values ​​already inserted. Actually he goes to check if ‘ipass’ is null, and he will find them all null in the database, even if in the view instead the column ‘ipass’ (numerazioni) is valued.

Fundamental aspect that I think I have omitted is that ‘ipass’ on ‘numerazioni’ table does not have a foreign key/relation db to the ‘gestionepc’ table, the relation is made through the function

from Numerazioni.php 
    /**
     * Gets query for [[Gestionepc]].
     *
     * @return \yii\db\ActiveQuery|GestionepcQuery
     */
    public function getIndirizzip()
    {
        return $this->hasOne(Gestionepc::className(), ['indirizzip_id' => 'ipass']);
    }

I have not put any relation in the db between ‘ipass’ and ‘pp management’ because if I have to add another value on the ‘numbers’ table it would return the sql error

#1452 - Cannot add or update a child row: a foreign key constraint fails (db_gesin.numerazioni, CONSTRAINT fk_ipass_numerazioni FOREIGN KEY (ipass) REFERENCES gestionepc (indirizzip_id) ON DELETE CASCADE ON UPDATE CASCADE)

16

@photowebstyle

IMO, the problem seems to lie in the design of business logic and db schema, but I’m sorry I can’t say something meaningful until I fully understand what you are trying to achieve as a whole. That’s something too big for a thread in a forum.

17

I understand, I believe that the solution is using the second option and that is by modifying the GestionepcController.php controller in the functions in order to force it to write also on the other tables as well as on the ‘gestionepc’ table.
And therefore:

public function actionCreate()
{
    $model = new Gestionepc();

    if ($model->load(Yii::$app->request->post()) && $model->save()) {
        return $this->redirect(['view', 'id' => $model->id_gestionepc]);
    }

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

public function actionUpdate($id)
{
    $model = $this->findModel($id);

    if ($model->load(Yii::$app->request->post()) && $model->save()) {
        return $this->redirect(['view', 'id' => $model->id_gestionepc]);
    }

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

public function actionDelete($id)
{
    $this->findModel($id)->delete();

    return $this->redirect(['index']);
}