Here is my scenario:
I have the following tables:
Employees
ServiceOrders
DriversActivity.
The relationships are as follows:
1 DriversActivty can have 0 or 1 Employees. (A DriversActivity refers to an employee that is a driver that is either active or inactive depending on if the ‘end_time’ field in the table is not NULL. You can think of a DriversActivity record as a shift, with a start_time and end_time. If the end_time is not set it means the driver is active).
1 DriversActivity can have many ServiceOrders.
Here is the a representation of the ERD for these 3 tables:
7400
So here is what I am struggling with. And this is not just for this relationship set, but for dealing with relations in Yii2 in general. I think Yii2 is pretty amazing, but the documentation doesn’t really provide enough context to be able to just run with things. My big problem is that the models as generated by Gii are not enough for working with relations that span more than one table, so in both the search forms (gridview) and the create forms (_form.php in the views) I can’t figure out how to do the damn joins to get the queries that I need.
For these 3 tables I have here, I want to – within the ‘CREATE’ view for a ServiceOrder, to provide the user with a dropdown that lets them select drivers that have no end_time set.
The problem is that the relationship from the service order to the drivers_activity table is through the employees table. So I can’t figure out how to get to the ‘end_time’ in the drivers activity table from the service_orders CREATE view.
I hope I am making sense here. I have spent days and days trying to figure this out to no avail.
Here is the code in the _form.php file. I am using a widget that lets me provide a dropdown for fields that are foreign keys to related tables (kartik\select2\Select2).
For the Drivers selection, I want to filter the query so that only drivers from the DriversActivity table that have an ‘end_time’ field of NULL are displayed. I don’t know how to connect the table relation. The service_orders_table is connected to the employees table via ‘id’ field, and these id’s also match with ‘drivers_id’ field in the DriversActivity table. Since the DriversActivity table is not connected to the service_orders table via its model file, I can’t figure how how or where to do it.
use yii\helpers\Html;
use yii\helpers\ArrayHelper;
use yii\widgets\ActiveForm;
// Our own models
// use app\models\Trucks;
use app\models\Employees; // for connecting with drivers from the employee table
use app\models\BatteryTypes;
use app\models\PayTypes;
use app\models\Regions;
use app\models\DriversActivity;
// Widgets
use kartik\select2\Select2;
/* @var $this yii\web\View */
/* @var $model app\models\ServiceOrders */
/* @var $form yii\widgets\ActiveForm */
?>
<div class="service-orders-form">
<?php $form = ActiveForm::begin(); ?>
<?= $form->field($model, 'SVONumber')->textInput() ?>
<?= $form->field($model, 'date')->textInput() ?>
[b] <?php // Driver selection
echo $form->field($model, 'driver_id')->widget(Select2::classname(), [
'data' => ArrayHelper::map(DriversActivity::find()
->asArray()->all(),
'id',
function($model, $defaultValue) {
return $model['first'].' '.$model['last'];
}
),
'language' => 'en',
'options' => ['placeholder' => 'Select Driver'],
'pluginOptions' => [
'allowClear' => true
],
]);
?>
[/b]
<?= $form->field($model, 'amount')->textInput(['maxlength' => true]) ?>
<?php // Pay Type selection
echo $form->field($model, 'pay_type_id')->widget(Select2::classname(), [
'data' => ArrayHelper::map(PayTypes::find()->asArray()->all(),
'id',
function($model, $defaultValue) {
return $model['pay_type'];
}
),
'language' => 'en',
'options' => ['placeholder' => 'Select Pay Type'],
'pluginOptions' => [
'allowClear' => true
],
]);
?>
<?php // Battery Type selection
echo $form->field($model, 'battery_type_id')->widget(Select2::classname(), [
'data' => ArrayHelper::map(BatteryTypes::find()->asArray()->all(),
'id',
function($model, $defaultValue) {
return $model['manufacturer'] . ' - ' . $model['model'];
}
),
'language' => 'en',
'options' => ['placeholder' => 'Select Battery Type'],
'pluginOptions' => [
'allowClear' => true
],
]);
?>
<?= $form->field($model, 't7_destination')->textInput(['maxlength' => true]) ?>
<?php // Region selection
echo $form->field($model, 'region_id')->widget(Select2::classname(), [
'data' => ArrayHelper::map(Regions::find()->asArray()->all(),
'id',
function($model, $defaultValue) {
return $model['region_number'] . ' - ' . $model['region_name'];
}
),
'language' => 'en',
'options' => ['placeholder' => 'Select Region For This SVO'],
'pluginOptions' => [
'allowClear' => true
],
]);
?>
<?= $form->field($model, 'status')->textInput(['maxlength' => true]) ?>
<?= $form->field($model, 'svo_notes')->textArea(['maxlength' => true]) ?>
<div class="form-group">
<?= Html::submitButton($model->isNewRecord ? 'Create' : 'Update', ['class' => $model->isNewRecord ? 'btn btn-success' : 'btn btn-primary']) ?>
</div>
<?php ActiveForm::end(); ?>
</div>
I am close to desparate now on this. Please, if you are a Yii2 pro, help me out here. I’m ready to provide any information you need to help me solve this. This is also quite urgent for me. If I can’t figure out how to easily work with relations across multiple tables related by foreign keys with Yii, I may have to completely drop Yii2 and use something else. That’s how urgent/desparate I am on this one.
Many thanks in advance.
psr123
