Grid With Join

Yii 2.0 General Discussions
1

my question is, where is the best way to build a grid with a data join from two table?

Now. I do this.

i create the crud with gii.

i perform the query with join by sql

this is my search model

public function search($params)

{


	//$query = AgenzieIndirizzo::find();


	


	$sql = 'SELECT * FROM agenzie_indirizzo LEFT JOIN agenzie_main ON agenzie_indirizzo.id_agenzia = agenzie_main.id';


	$query = AgenzieIndirizzo::findBySql($sql);


	


	$dataProvider = new ActiveDataProvider([


		'query' => $query,


	]);





	if (!($this->load($params) && $this->validate())) {


		return $dataProvider;


	}





	$this->addCondition($query, 'agenzie_main.name');


	$this->addCondition($query, 'id');


	$this->addCondition($query, 'id_agenzia');


	$this->addCondition($query, 'nome', true);


	$this->addCondition($query, 'tipo_ind');


	$this->addCondition($query, 'citta', true);


	$this->addCondition($query, 'provincia', true);


	$this->addCondition($query, 'tipo_via');


	$this->addCondition($query, 'via', true);


	$this->addCondition($query, 'numero_civ', true);


	$this->addCondition($query, 'cap', true);


	$this->addCondition($query, 'user_ins');


	$this->addCondition($query, 'data_ins');


	$this->addCondition($query, 'user_mod');


	$this->addCondition($query, 'data_mod');


	$this->addCondition($query, 'name');


	return $dataProvider;


}

after i declare the filed as var in the main model.

i think this work but is not the right method.

( how display in the grid a field if two table has same name filed ex. ‘id’ … i try db.it … and other)

anyone can help me?

2

SQL View?

3

??I don’t understand your question

4

SQL View

5

Sql View is not a real solution.

It consume a large amount of cpu and Ram with big data table, it can be done only with small data.

This is not a right best practice.

If anyone has a opinion about using big table data with view…

6

Why? It’s supported by most of RDBMs and consumes nearly the same amount of memory as it is consumed for joins.

7

i think is not the best solution for an oner reason, is not comfortable.

the project i’m working use SQL VIEW and this is not a problem for me now.

i found an good solution to use it in the crud.

When i can test Some db query i send you some benchmark with view and without.

(so a SQL VIEW is same as JOIN but Mysql use more ram using it, i don’t now what’s mysql do to do that, i hope you right so this can resolve many problem…[size=2]).[/size]