Yii Framework Wiki
Difference between
#42 and
#54 of
Yii v2 snippet guide II
Changes
Title unchanged
Yii v2 snippet guide II
Category unchanged
Tutorials
Yii version unchanged
2.0
Tags unchanged
tutorial,beginner,yii2,snippets
Content changed
---
Hi. I had to split my article as max length was reached. Check my previous articles here (mainly the 1st one):
Articles are separated into more files as there is the max lenght for each file on wiki.
* [Yii v1 for beginners 2](https://www.yiiframework.com/wiki/
* [Yii v2
- [
* [Yii v2 snippet guide II](https://www.yiiframework.com/wiki/25
* [Začínáme s PHP frameworkem Yii2 (I) česky - YouTube](https://youtu.be/ub06hNoL8B8)
**Connection to MSSQL**
[...]
There is only one formatter in the whole of Yii and you can extend it = you can add more methods and the rest of the formatter will remain so you can use all other methods as mentioned in documentation.**Displaying SUM of child models in a GridView with parent models**
---
... can be easily done by adding a MySQL VIEW into your DB, creating a model for it and using it in the "ParentSearch" model as the base class.
Let's show it on list of invoices and their items. Invoices are in table "invoice" (model Invoice) and their items in "invoice_item" (model InvoiceItem). Now we need to join them and sort and filter them by SUM of prices (amounts). To avoid calculations in PHP, DB can do it for us if we prepare a MySQL VIEW:
```sql
CREATE VIEW v_invoice AS
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
ON (invoice.id = invoice_item.id_invoice)
GROUP BY invoice.id
```
**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
<?php
namespace app\models;
class v_Invoice extends Invoice
{
public static function primaryKey()
{
// here is specified which column(s) create the fictive primary key in the mysql-view
return ['id'];
}
public static function tableName()
{
return 'v_invoice';
}
}
```
.. and in model InvoiceSearch replace word Invoice with v_Invoice (on 2 places I guess) plus add rules for those new columns. Example:
```php
public function rules()
{
return [
// ...
[['amount'], 'number'], // decimal
[['items'], 'integer'],
];
}
```
Into method search() add condition if you want to filter by amount or items:
```php
if (trim($this->amount)!=='') {
$query->andFilterWhere([
'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).
3 followers
Viewed: 68 878 times
Version: 2.0
Category: Tutorials
Written by: rackycz
Last updated by: rackycz
Created on: Aug 26, 2020
Last updated: 2 years ago
Revisions
View all history-
2 years ago by
rackycz youtube link
-
3 years ago by
rackycz List of articles
-
3 years ago by
rackycz List of articles
-
3 years ago by
rackycz base64 stream download
-
3 years ago by
rackycz base64 stream download
-
3 years ago by
rackycz base64 stream download
-
3 years ago by
rackycz Search sort related column
-
3 years ago by
rackycz Search sort related column
-
3 years ago by
rackycz Search sort related column
-
3 years ago by
rackycz Search sort related column