Yii Framework Forum: Create view in Yii2 - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Create view in Yii2 Using SQL view for filtering

#1 User is offline   Pereira 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 27-April 17
  • Location:Portugal

  Posted 27 April 2017 - 12:01 PM

Hello


In Yii2, the use of a model – extends yii\db\activRecord (table), a modeloSearsh (ActiveDataProvider) and view (GridView:: Widget), allows, in full view, filter by column.
I'd like to achieve the same using a query (yii\db\Query) rather than a table. The query will have the information from multiple tables but will have to be treated in query and not in table, and if I use the sqlDataProvider, column filters stop working no view (GridView:: Widget)
Can somebody help me?



I can't apply

“Using SQL views for filtering, sorting and displaying data
There is also another approach that can be faster and more useful - SQL views. For example, if we need to show the gridview with users and their profiles, we can do so in this way:
CREATE OR REPLACE VIEW vw_user_info AS
SELECT user.*, user_profile.lastname, user_profile.firstname
FROM user, user_profile
WHERE user.id = user_profile.user_id

//****************

I saw the following explanation

“Using SQL views for filtering, sorting and displaying data
There is also another approach that can be faster and more useful - SQL views. For example, if we need to show the gridview with users and their profiles, we can do so in this way:
CREATE OR REPLACE VIEW vw_user_info AS
SELECT user.*, user_profile.lastname, user_profile.firstname
FROM user, user_profile
WHERE user.id = user_profile.user_id"



Then you need to create the ActiveRecord that will be representing this view:

//*
namespace app\models\views\grid;

use yii\db\ActiveRecord;

class UserView extends ActiveRecord
{

/**
* @inheritdoc
*/
public static function tableName()
{
return 'vw_user_info';
}

public static function primaryKey()
{
return ['id'];
}

/**
* @inheritdoc
*/
public function rules()
{
return [
// define here your rules
];
}

/**
* @inheritdoc
*/
public static function attributeLabels()
{
return [
// define here your attribute labels
];
}


}


but I can't apply

“Using SQL views for filtering, sorting and displaying data
There is also another approach that can be faster and more useful - SQL views. For example, if we need to show the gridview with users and their profiles, we can do so in this way:
CREATE OR REPLACE VIEW vw_user_info AS
SELECT user.*, user_profile.lastname, user_profile.firstname
FROM user, user_profile
WHERE user.id = user_profile.user_id



//**********************
//My Code is

//*****

//Controllers


use app\models\ObsinqSearch;
...

public function actionIndex()
{

$searchModel = new ObsinqSearch();
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);

return $this->render('index', [



'searchModel' => $searchModel,
'dataProvider' => $dataProvider,
]);
}


//**************************************************
//Models

//****************
//ModelSerarch


<?php

namespace app\models;

use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
//use yii\data\SqlDataProvider;
use app\models\Obsinq;

/**
* ObsinqSearch represents the model behind the search form about `app\models\Obsinq`.
*/
class ObsinqSearch extends Obsinq
{


/**
* @inheritdoc
*/
public function rules()
{
return [
[['id', 'teste1', 'teste2','submetidoObs'], 'safe'],
];
}

/**
* @inheritdoc
*/
public function scenarios()
{
// bypass scenarios() implementation in the parent class
return Model::scenarios();
}

/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{


//table in server but no working filters
//if delete table in server, no work bicause "error table not exist"
$query = Obsinq::findbySql("
SELECT
reg.id,
pop.teste1,
pop.teste2,
reg.teste1 obs
from reg
LEFT JOIN (pop)
ON (pop.id = reg.id)
where
!(reg.teste1 is null)
and char_length(reg.test2) > 1

Order by reg.teste1");


$dataProvider = new ActiveDataProvider([
'query' => $query,
]);



$this->load($params);

if (!$this->validate()) {
return $dataProvider;
}


$query->andFilterWhere([
'id' => $this->id,
'teste1' => $this->teste1,
]);

$query->andFilterWhere(['like', 'teste2', $this->teste2])
->andFilterWhere(['like', 'obs', $this->obs]);



return $dataProvider;
}

}


//****************
//Model


<?php

namespace app\models;


use Yii;
use yii\db\ActiveRecord;


/**
* This is the model class for table "vw_obsinq".
*
* @property integer $id
* @property integer $teste1
* @property string $teste2
* @property string $obs
*/

class Obsinq extends \yii\db\ActiveRecord
{

/**
* @inheritdoc
*/
public static function tableName()
{
return 'vw_obsinq';
}

public static function primaryKey()
{
return ['id'];
}


/**
* @inheritdoc
*/
public function rules()
{
return [
[['id'], 'required'],
[['id','teste1], 'integer'],
[['teste2','obs'], 'string'],
];
}

/**
* @inheritdoc
*/
public function attributeLabels()
{
return [
//'id' => 'ID',
//'teste1' => 'teste1',
//'teste2' => 'teste2',
//'obs' => 'obs',
];
}


}



//******************************************************
//views

<?php

use yii\helpers\Html;
use yii\grid\GridView;

/* @var $this yii\web\View */
/* @var $searchModel app\models\ObsinqSearch */
/* @var $dataProvider yii\data\ActiveDataProvider */

$this->title = 'Obsinqs';
$this->params['breadcrumbs'][] = $this->title;
?>
<div class="obsinq-index">

<h1><?= Html::encode($this->title) ?></h1>

<p>
<?= Html::a('Create Obsinq', ['create'], ['class' => 'btn btn-success']) ?>
</p>

<?= GridView::widget([
'dataProvider' => $dataProvider,
'filterModel' => $searchModel,
'columns' => [
['class' => 'yii\grid\SerialColumn'],

[
//o atribute permite visualizar o campo a procurar
'attribute'=>'id',
//Nome do campo
'label' => 'ID',
//'format'=>['decimal',0],
'value' => 'id',
'contentOptions' => ['align'=> 'Left', 'width' => 120],
],


[
//o atribute permite visualizar o campo a procurar
'attribute'=>'id',
//Nome do campo
'label' => 'teste1',
//'format'=>['decimal',0],
'value' => 'teste1',
'contentOptions' => ['align'=> 'Left', 'width' => 120],
],

[
//o atribute permite visualizar o campo a procurar
'attribute'=>'id',
.//Nome do campo
'label' => 'teste2',
'value' => 'teste2',
'contentOptions' => ['align'=> 'Left', 'width' => 120],
],
[
//o atribute permite visualizar o campo a procurar
'attribute'=>'id',
//Nome do campo
'label' => 'obs',
'value' => 'obs2',
'contentOptions' => ['align'=> 'Left', 'width' => 120],
],

//['class' => 'yii\grid\ActionColumn'],
],
]); ?>

</div>




Someone can help me please
Thanks
0

#2 User is offline   TAQTICA 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 13-February 10

Posted 27 April 2017 - 08:51 PM

Why do you need in a single class? Relation between User and UserProfile should work well.
0

#3 User is offline   Pereira 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 27-April 17
  • Location:Portugal

Posted 28 April 2017 - 03:54 AM

the need has to do with the way to obtain the data and server data hosting
0

#4 User is offline   Pereira 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 27-April 17
  • Location:Portugal

Posted 17 May 2017 - 11:31 AM

I solved the problem by placing the conditions directly in query


//modelSearch

public function search($params){


$this->load($params);

$query = Obsinq::findbySql("
SELECT
reg.id,
pop.teste1,
pop.teste2,
reg.teste1 obs
from reg
LEFT JOIN (pop)
ON (pop.id = reg.id)
where
($this->id = '' or
reg.id = '$this->id') and
($this->teste1 = '' or
pop.teste1 like '%$this->teste1%') and
($this->teste2 = '' or
pop.teste2 like '%$this->teste2%') and
($this->obs = '' or
reg.teste1 like '%$this->obs%') and
!(reg.teste1 is null) and
char_length(reg.test2) > 1
Order by reg.teste1");


$dataProvider = new ActiveDataProvider([
'query' => $query,
]);

return $dataProvider;

}



I need to have a model(ActiveRecord) to an existing table (empty or not empty) on the server with the tutilizar fields in sql

The search fields are parameterized in the index (attribute) and not in the model
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users