Yii v2 snippet guide II

Tutorials

2.0

tutorial,beginner,yii2,snippets

**Intro**
 
---
 
Hi. I had to split my article as max length was reached. Check my previous articles here (mainly the 1st one):## My articles
 
Articles are separated into more files as there is the max lenght for each file on wiki. -* [Yii v1 for beginners](https://www.yiiframework.com/wiki/25520/yii-v2-snippet-guidefor-beginners)
 
* [Yii v1 for beginners 2](https://www.yiiframework.com/wiki/255462/yii-for-beginners-2)
 
* [Yii v2- snippet- guide)
 
- [ I](https://www.yiiframework.com/wiki/25052/yii-for-beginnersv2-snippet-guide)
 
* [Yii v2 snippet guide II](https://www.yiiframework.com/wiki/25058/yii-for-beginnersv2-snippet-guide-ii) -* [Yii v2 snippet guide III](https://www.yiiframework.com/wiki/4622567/yii-for-beginners-2](https://www.yiiframework.com/wiki/462/yii-for-beginners-2v2-snippet-guide-iii)
 
* [Začínáme s PHP frameworkem Yii2 (I) česky - YouTube](https://youtu.be/ub06hNoL8B8)

**Connection to MSSQL**SELECT invoice.*,
SUM(invoice_item.units * invoice_item.price_per_unit) as amount,
COUNT(*invoice_item.id) as items
FROM invoice
LEFT JOIN invoice_item```

**Note:** Here you can read why it is better not to use COUNT(\*) in LEFT JOIN:
 
- [https://learnsql.com/blog/introduction-using-aggregate-functions-joins/](https://learnsql.com/blog/introduction-using-aggregate-functions-joins/)
 
- Chapter: Dealing with NULLs
 
 
This will technically clone the original table "invoice" into "v_invoice" and will append 2 calculated columns: "amount" + "items". Now you can easily use this VIEW as a TABLE (for reading only) and display it in a GridView. If you already have a GridView for table "invoice" the change is just tiny. Create this model:

```php```

Into method search() add HAVING condition if you want to filter by amount or items: ```php if (trim($this->amount)!=='') { $query->andHavingFilterWhere([
'amount' => $this->amount
]);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) {
 
    // ...
 
 
    // You must use joinWith() in order to have both tables in one JOIN - then you can call WHERE and ORDER BY on the 2nd table. 
 
    // Explanation here:
 
    // https://stackoverflow.com/questions/25600048/what-is-the-difference-between-with-and-joinwith-in-yii2-and-when-to-use-them
 
    
 
    $query = Invoice::find()->joinWith('companyRelation');
 
 
    // 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->andFilterWhere(['like', 'table.column', $this->company_name]);
 
    }
 
  }
 
```
 
 
**Sending binary data as a file to browser - decoded base64**
 
---
 
 
In my tutorial for Yii v1 I presented following way how to send headers manually and then call exit(). But calling exit() or die() is not a good idea so I discovered a better way in Yii v2.
 
See chapter [Secured (secret) file download](https://www.yiiframework.com/wiki/462/yii-for-beginners-2)
 
 
Motivation: Sometimes you receive a PDF file encoded into a string using base64. For example a label with barcodes from FedEx, DPD or other delivery companies and your task is to show the label to users. 
 
 
For me workes this algorithm:
 
 
```php
 
$pdfBase64 = 'JVBERi0xLjQ ... Y0CiUlRU9GCg==';
 
 
// First I create a fictive stream in a temporary file
 
// Read more about PHP wrappers: 
 
// https://www.php.net/manual/en/wrappers.php.php 
 
$stream = fopen('php://temp','r+');
 
 
// Decoded base64 is written into the stream
 
fwrite($stream, base64_decode($pdfBase64));
 
 
// And the stream is rewound back to the start so others can read it
 
rewind($stream);
 
 
// This row sets "Content-Type" header to none. Below I set it manually do application/pdf.
 
Yii::$app->response->format = Yii::$app->response::FORMAT_RAW;
 
Yii::$app->response->headers->set('Content-Type', 'application/pdf');
 
      
 
// This row will download the file. If you do not use the line, the file will be displayed in the browser.
 
// Details here:
 
// https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers#Downloads
 
// Yii::$app->response->headers->set('Content-Disposition','attachment; filename="hello.pdf"'); 
 
    
 
// Here is used the temporary stream
 
Yii::$app->response->stream = $stream;
 
 
// You can call following line, but you don't have to. Method send() is called automatically when current action ends:
 
// Details here:
 
// https://www.yiiframework.com/doc/api/2.0/yii-web-response#sendContentAsFile()-detail
 
// return Yii::$app->response->send(); 
 
```
 
 
**Note:** You can add more headers if you need. Check my previous article (linked above).