Difference between #43 and #44 of
Yii v2 snippet guide II

Revision #44 has been created by rackycz on Oct 5, 2020, 8:00:43 PM with the memo:

Search sort related column
« previous (#43) next (#45) »

Changes

Title unchanged

Yii v2 snippet guide II

Category unchanged

Tutorials

Yii version unchanged

2.0

Tags unchanged

tutorial,beginner,yii2,snippets

Content changed

[...]
In the GridView you can now use the columns "amount" and "items" as native columns. Filtering and sorting will work.

**Danger:** Read below how to search and sort by related columns. This might stop working if you want to join your MySQL with another table.

> I believe this approach is the simplest to reach the goal. The advantage is that the MySQL VIEW is only used when search() method is called - it means in the list of invoices. Other parts of the web are not influenced because they use the original Invoice model. But if you need some special method from the Invoice model, you have it also in v_Invoice. If data is saved or changed, you must always modify the original table "invoice".
 
 
**Sort and search by related column**
 
---
 
 
Lets say you have table of invoices and table of companies. They have relation and you want to display list of Invoices plus on each row the corresponding company name. You want to filter and sort by this column.
 
 
Your GridView:
 
 
```php
 
<?= GridView::widget([
 
// ...
 
  'columns' => [
 
    // ...
 
    [
 
      'attribute'=>'company_name',
 
      'value'=>'companyRelation.name',
 
    ],
 
```
 
 
Your InvoiceSearch model:
 
 
```php
 
class InvoiceSearch extends Invoice
 
{
 
  public $company_name;
 
  
 
  // ...
 
  
 
  public function rules() {
 
    return [
 
      // ...
 
      [['company_name'], 'safe'],
 
    ];
 
  }             
 
 
  // ...
 
  
 
  public function search($params) {
 
    // ...
 
 
    // Appending new sortable column:
 
    $sort = $dataProvider->getSort(); 
 
    $sort->attributes['company_name'] = [
 
      'asc' => ['table.column' => SORT_ASC],
 
      'desc' => ['table.column' => SORT_DESC],
 
      'label' => 'Some label',
 
      'default' => SORT_ASC            
 
    ];
 
  
 
    // ...
 
  
 
    if (trim($this->company_name)!=='') {
 
      $query->andHaving(['like', 'company.name', $this->company_name]);
 
  }
 
```
4 0
3 followers
Viewed: 75 387 times
Version: 2.0
Category: Tutorials
Tags: beginner, snippets, tutorial, yii2
Written by: rackycz
Last updated by: rackycz
Created on: Aug 26, 2020
Last updated: 3 years ago
Update Article
Revert to #43 | Revert to #44

Revisions

View all history