Thought this question justified a new topic.
I currently have an ActiveDataProvider which is being passed to a GridView, my question is, the query I have is something like this:
'query' => Customer::find()->where(['Branch' => $param['Branch']]);
In my Customer Table, I have a CustomerTypeID, which links to a CustomerType Table (they are not linked by foreign key) how can I get the query to return the type by name as opposed to its ID.
Regards
Matt
You can refer this wiki I created - which has 3 scenarios for querying/filtering and sorting by related fields in gridview.
Thanks Kartik,
Although i’m not sure that is exactly what i was looking for. I would like to return this in my table:
ID | Type | Name | Details | Stuff |
But currently the Type is being displayed as an ID, as that is what is being stored against that column, but that ID refers to a CustomerType table which is what I want to display within the table.
I’m not after a filterModel, or atleast i don’t think I am.
That should be pretty easy. One way is you can define a relation in your Customer model to your CustomerType (even if you do not have a foreign key) and return the description. The other way is to define a getter function getCustTypeName() in your model which returns the customer type name based on id.
If this data needs to be edited using tabular structure (meaning its a form) - the best way is for you to define this particular field as a dropDownList (select). You can display the list of values from your CustomerType and internally pass the id.
I have set up some sort of getter
/**
* @return \yii\db\ActiveQuery
*/
public function getCustomerType()
{
return $this->hasOne(CustomerType::className(), ['ID' => 'CustomerTypeID']);
}
But its not showing in the table, one of the ways I can get it showing is if I do this:
public function attributes()
{
// add related fields to searchable attributes
return array_merge(parent::attributes(), ['customerType.Description']);
}
But it puts it at the end of the table. The only other way I can do it is by within the GridView
Pjax::begin(['formSelector' => '#update-form']);
echo GridView::widget([
'dataProvider' => $dataProvider,
'options' => ['class' => 'table-responsive'],
'columns' => [
'ID',
'customerType.Description',
]
]);
Pjax::end();
Unless that is the correct way to display that column?
Not looking at this form allowing data to be updated or saved, its purely for presentation. Although I will need to look into at some point the form within a table, which I believe is what you are describing the tabular structure. Just cant get my head around that just yet.
I am also getting this error
Relation names are case sensitive. app\models\Customer has a relation named "customerType" instead of "CustomerType"
For my getter, the only way around it is to in my attributes function call customerType and not CustomerType.
Is there a way to blend a DB table into one HTML table column. I have an address DB table with columns for address fields etc, but i’d like to display them all in one HTML table column to keep it neat and simple.
You can create a simple getter like this
public function getCustTypeDesc()
{
return $this->customerType->description; // where customerType is the relation
}
public function attributeLabels() {
return array_merge(parent::attributes(), [
'custTypeDesc' => 'Customer Type'
]);
}
In your gridview add this to your columns
echo GridView::widget([
'dataProvider' => $dataProvider,
'options' => ['class' => 'table-responsive'],
'columns' => [
'ID',
'custTypeDesc',
]
]);
Is there a reason I am getting 41 DB queries for this. I am getting lots of SELECT’s & SHOW in the debug tool. Wouldnt of thought there would need to be this many queries.
This is what is in my main Customer Model:
/**
* @return \yii\db\ActiveQuery
*/
public function getCustomerType()
{
return $this->hasOne(CustomerType::className(), ['ID' => 'CustomerTypeID']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getCustomerAddress()
{
return $this->hasOne(Address::className(), ['ID' => 'AddressID', 'Branch' => 'Branch']);
}
public function getCustomerFullAddress()
{
$address = $this->customerAddress->Address1.' '.$this->customerAddress->PostCode;
return $address;
}
I have the two joins and then I have a getter for the full address, which I have set up an AttributeLabel for.
Was expecting 2-3 queries perhaps, not 41 seems excessive.
Enable schema caching
See here for lazy and eager loading
Thanks Alex,
I’ve now enabled the Schema Caching. Which has removed all the SHOW queries.
But can’t quite get my head around the lazy and eager loading. Tried it and just failed to load the page.
As soon as I add ->all() to the end of the query it just fails to load the page.
This is the basic query:
$sql = Customer::find();
$sql->with('address');
$sql->orderBy(['DateCreated' => SORT_DESC])->all();
But then with some parameters:
$sql = Customer::find()->where(['Customers.Branch' => $params['SourceBranch']])->andwhere(['like','Name',$params['Name']]);
$sql->with('address');
$sql->orderBy(['DateCreated' => SORT_DESC])->all();
How do I do multiple ->with()?