Acces Values Of Joined Tables

carlarrj · January 21, 2013, 12:06pm

Hello, i’m newbie in Yii and it’s the first time that i’m using a framework.

I already saw the guide and tutorials but there are somethings that i can’t understand how to do it. My biggest problem is how to access data from tables that I’ve joined.

I can search a value in the joined tables but i don’t know how show data from them in the search results and in the details. Can someone please help me out?

[size="4"]# So here is my DB:[/size]

produtos table

id
codigo
activo
prazos
img_p

produtos_campos table

id REFERENCES produtos.id
nome
descricao
caracteristicas

produtos_quantidade table

referencia REFERENCES produtos.codigo
preco_a
preco_b
preco_c

…

preco_n

[size="4"]# And the 3 Models:[/size]

1 // Produtos Model

public function tableName() {

return 'produtos';

}

public function relations() {

return array(

‘produtos_campos’ => array(self::HAS_MANY, ‘ProdutosCampos’, ‘id’, ‘joinType’ => ‘INNER JOIN’),

'produtos_quantidade' =&gt; array(self::HAS_ONE,    'ProdutosQuantidade', 'referencia', 'joinType' =&gt; 'INNER JOIN'),


		


);

}

public function search() {

&#036;key = &#036;this-&gt;codigo;





&#036;criteria = new CDbCriteria;


	


&#036;criteria-&gt;together = true;


&#036;criteria-&gt;select= &quot;t.id, t.codigo, t.codigo_site, t.img_p, t.prazos, tt.id as pcid, tt.nome, tt.descricao, tt.caracteristicas AS bAttribute1&quot;;


	


&#036;criteria-&gt;join .= &quot;INNER JOIN produtos_campos AS tt ON t.id = tt.id&quot;;


&#036;criteria-&gt;condition .= &quot;(t.activo = '1' AND t.id_frontend='0' AND tt.nome &#33;= '' AND tt.lingua = 'pt' AND t.img_p &#33;= '')&quot;; 


&#036;criteria-&gt;condition .= &quot; AND ( tt.nome LIKE '%&quot;.&#036;key.&quot;%' &quot;;


&#036;criteria-&gt;condition .= &quot; OR t.codigo LIKE '%&quot;.&#036;key.&quot;%'&quot;;


&#036;criteria-&gt;condition .= &quot; OR t.codigo_site LIKE '%&quot;.&#036;key.&quot;%'&quot;;


&#036;criteria-&gt;condition .= &quot; OR tt.descricao LIKE '%&quot;.&#036;key.&quot;%'&quot;;


&#036;criteria-&gt;condition .= &quot; OR tt.caracteristicas LIKE '%&quot;.&#036;key.&quot;%'&quot;;


&#036;criteria-&gt;condition .= &quot; )&quot;;


	


	


return new CActiveDataProvider(&#036;this, array( 


	'criteria'=&gt;&#036;criteria, 


));

}

2 // Produtos Campos Model

public function tableName() {

return 'produtos_campos';

}

public function relations() {

return array(


	'produtos'    =&gt; array(self::BELONGS_TO, 'Produtos', 'id'),


);

}

3 // Produtos Quantidade Model

public function tableName() {

return 'produtos_quantidade';

}

public function relations() {

return array(


	'produtos'    =&gt; array(self::BELONGS_TO, 'Produtos', 'codigo'),


);

}

[size=“4”]# This is what i’ve got in the view results[/size]

I’m displaying the values of the table produtos in the search results with the help of CListView, <?php echo GxHtml::encode($data->codigo); ?> but when i’m trying to echo the value of descricao with <?php echo GxHtml::encode($data->descricao); ?>, from table produtos_campos, i’ve got a “Cexception Property “Produtos.descricao” is not defined.”

[size=“4”]# And this is what i’ve got in the view details.[/size]

<?php $this->widget(‘zii.widgets.CDetailView’, array(

    'data' =&gt; &#036;model,


    'attributes' =&gt; array(


   	'descricao',


    	'caracteristicas',


'prazo',


'tipo_impressao',


    	'tipo_impressao_letra',


    ),


)); ?&gt;

My question is how can i access to the values nome, descricao, caracteristicas of the table produtos_campos and the values from preco_a, preco_b, …, preco_n from the table produtos_quantidade to display in the search results and detail of the product?

I appreciate any help because i don’t know what to do more.

Thanks in advance,

ragua · January 21, 2013, 12:50pm

Hi,

you must use the relations you have declared.

For instance, for a field:




		array(

			'label'=>'Filière', 

			'value'=>$model->stage->stage_name

			),

softark · January 21, 2013, 2:13pm

Hi akira_lee, welcome to the forum!

$data is an instance of CActiveRecord Produtos. It has attributes like id, codigo, active, prazos, img_p … they are the columns of produtos table. But it doesn’t have any attribute for produtos_campos table and produtos_quantidade table.

You are fetching produtos_compos.descricao as ‘tt.descricao’ with your SQL, but the retrieved value will be lost because there is no ‘descricao’ attribute in Produtos model to store it.

But, Produtos has ‘produtos_campos’ and ‘produtos_campos’ relations. We use these relations to store the retrieved values from the joined table. Then we can access the retrieved ‘descricao’ as $data->produtos_campos->descricao.

In order to fetch column values of the joined table into the relation, we usually do something like the following:




	$criteria = new CDbCriteria;

	$criteria->with = array('produtos_campos');

	$criteria->condition = "(t.activo = '1' AND t.id_frontend='0' AND produtos_campos.nome != '' AND produtos_campos.lingua = 'pt' AND t.img_p != '')";

	...

	return new CActiveDataProvider($this, array( 

		'criteria'=>$criteria, 

	));

}

Usually you don’t need to specify ‘JOIN’ clauses manually. ‘with’ will do it for you. And we use the relation name as the table alias.

Now, you can access the relation’s data like this:




<?php $this->widget('zii.widgets.CDetailView', array(

	'data' => $model,

	'attributes' => array(

		'produtos_campos.descricao',

		'produtos_campos.caracteristicas',

		...

	),

)); ?>

carlarrj · January 21, 2013, 3:40pm

Hello, thanks for the reply and explanation, now i understand why the values from others tables don’t appear.

I’ve made some changes in the $criteria, but now i can’t search the $key in the table produtos_campos, columns descricao or caracteristicas.

I’ve tried $criteria->condition .= " OR produtos_campos.descricao LIKE ‘%".$key."%’"; but it gives me the error “CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found:”

The actual $criteria:


$criteria = new CDbCriteria;

      $criteria->with = array('produtos_campos');

	$criteria->condition .= "( ";

	$criteria->condition .= "codigo LIKE '%".$key."%'";

	$criteria->condition .= " OR codigo_site LIKE '%".$key."%'";

	$criteria->condition .= " )";

	

	$criteria->addcondition("activo = 1");

	$criteria->addcondition("id_frontend = 0");

        $criteria->addcondition("img_p != ''");

		

	return new CActiveDataProvider($this, array( 

		'criteria'=>$criteria, 

		'pagination' => array( 

			'pageSize' => 30,

		), 

	));

In the view the code <?php echo GxHtml::encode($data->produtoscampos->descricao); ?> still don’t work, shows the error “Property “Produtos.produtoscampos” is not defined.” and i already have a var public $descricao;.

What can be done now in each case?

Thanks a lot for your help i really appreciated,

softark · January 21, 2013, 11:39pm

Would you please try "$criteria->together = true"?

When the pagination is applied, you will need to set "together" to true in order to force the join.

According to your source, the relation is not “produtoscampos”, but “produtos_campos”.

carlarrj · January 22, 2013, 12:17pm

Hi softark,

The query works perfectly with $criteria->together = true;, thank you!

You’re right, my mistake, the relation is produtos_campos, now i can echo those values

In the Detail View i’ve tried this:


<?php $this->widget('zii.widgets.CDetailView', array(

        'data' => $model,

        'attributes' => array(

			'produtos_campos.descricao',

			'produtos_campos.caracteristicas',

			'prazos',

			'tipo_impressao',

			'tipo_impressao_letra',


        ),

    )); ?>

It doesn’t return any error, only shows Descricao Not set and Caracteristicas Not set

Again in the Detail view i’ve tried:


<?php $this->widget('zii.widgets.CDetailView', array(

        'data' => $model,

        'attributes' => array(

			'prazos',

			'tipo_impressao',

			'tipo_impressao_letra',

			array(

				'name' => 'id',

				'value' => $data->produtos_campos->descricao,

									

		         ),

        ),

    )); ?>

But it gives me PHP notice Undefined variable: data , so how can be done next? What’s the best way to get the values from the related tables to display in the detail view? Can it be done like the <?php echo GxHtml::encode($data->produtos_campos->descricao); ?> or it have to be a array?

Thanks for your help,

softark · January 22, 2013, 1:48pm

Ah, I’m sorry.

"produtos_campos" is a HAS_MANY relation. So you have to access it as an array.




foreach($model->produtos_campos as $campo)

{

    echo $campo->descricao;

}

And for CDetailView widget, $data doesn’t work. Use $model instead.

carlarrj · January 22, 2013, 2:39pm

Works like a charm

Thank you so much for helping me out, you’re the best!

carlarrj · January 29, 2013, 1:37pm

Hello again,

I’m trying to get data from other tables to show in the product details, the database and the models are the same. I already got data from one table because the PK it’s the same with the controller code but i’m having trouble getting data with other columns:

This is my ProdutosControler:


public function actionView($id) {

$produtosquantidade=ProdutosQuantidade::model()->find( array("condition"=>'referencia="JRBANDEIRAS12"') ); // works but it only shows the [i]"JRBANDEIRAS12"[/i] details, how can i say that the  should be the column codigo from the table products?


// i've tried the next options, with $data, $model, etc, but it's not working:

//$produtosquantidade=ProdutosQuantidade::model()->findByAttributes( array('referencia='=>$this->codigo) ); // don't work, it shows up the error [i]CException Property "ProdutosController.codigo" is not defined. [/i]


//$produtosquantidade=ProdutosQuantidade::model()->find( array("condition"=>'referencia='.$this->loadModel($codigo)) ); // don't work, it shows up the error [i]PHP notice  Undefined variable: codigo [/i]


$categorias = Categorias::model()->findAll( array("condition"=>'id="139"') ); 


$produtosimagens=ProdutosImagens::model()->findAll( array("condition"=>'idproduto='.$id, "order"=>"codigo") ); //this one works 100%

		

        $this->render('view', array(

		'model' => $this->loadModel($id, 'Produtos'),

		'produtosquantidade' => $produtosquantidade,

	        'produtosimagens' => $produtosimagens,

		'categorias' => $categorias,

	));	

}

How can i do this? Can you please help me out?

Thanks in advance,

softark · January 29, 2013, 2:58pm

Hi,

Your Produtos model may have an attribute named ‘codigo’, but your ProdutosControler doesn’t.

You have to use a Produtos model instance.




$model = $this->loadModel($id, 'Produtos');

$produtosquantidae = ProdutosQuantidade::model()->findByAttributes(array('referencia' => $model->codigo));

...

$this->render('view', array(

	'model' => $model,

	'produtosquantidade' => $produtosquantidade,

	'produtosimagens' => $produtosimagens,

	'categorias' => $categorias,

));

In a method of a class, $this refers to the instance of the class object. In a controller method, it is the instance of the controller class. But it will be an instance of the model class when it is used in a model class method.

carlarrj · January 29, 2013, 4:28pm

Hi softark,

Ok i now get it it’s really hard for me to do some stuff because i’m a newbie and i can’t make it work like i wish thank you very much for the code and the excellent explanation you’re great!