2 tables in criteria, CActiveDataProvider

Yii 1.1 General Discussion
1

Hi!

I have in my view action:


$dataProviderColores=new CActiveDataProvider(

            'Atributos',

            array(

                'criteria'=>array(

                    'with'=>array('modelos_atributos'),

                    'condition'=>'modelos_atributos.id_modelo='.$id,

                    'together'=>true,

                ),

            )

   		 );


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

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

			'colores'=>$dataProviderColores,	

				

		));

That works fine. There are 3 tables, modelos / atributos / modelos_atributos.

But I need one condition more, atributos.tipo=color.

I try this but not work…


$dataProviderColores=new CActiveDataProvider(

            'Atributos',

            array(

                'criteria'=>array(

                    'with'=>array('modelos_atributos'),

                    'condition'=>'modelos_atributos.id_modelo='.$id.' AND atributos.tipo=color',

                    'together'=>true,

                ),

            )

   		 );

Yii returns: “Column not found: 1054 Unknown column ‘atributos.tipo’ in ‘where clause’.”

2

Show relations in model

3

Maybe this will work.




$dataProviderColores=new CActiveDataProvider(

            'Atributos',

            array(

                'criteria'=>array(

                    'with'=>array('modelos_atributos'),

-                   'condition'=>'modelos_atributos.id_modelo='.$id.' AND atributos.tipo=color',

+                   'condition'=>'modelos_atributos.id_modelo='.$id.' AND t.tipo=color',

                    'together'=>true,

                ),

            )

 );

Main table should be aliased as ‘t.’.

4

Sorry, I think I misunderstood something…I explain my case:

I have 3 models, modelos / atributos / modelos_atributos

modelos

id_modelo | name | descripcion

atributos

id_atributo | type| value

modelos_atributos

id | id_modelo | id_atributo

Ok, I need to show in a view a list of "atributos" for an "id_modelo" and atributos.type=color

For example:

modelos

1 | modelo1 | Este es la descripcion del modelo1

2 | modelo2 | Este es la descripcion del modelo2

3 | modelo3 | Este es la descripcion del modelo3

atributos

1 | color | red

2 | color | blue

3 | size | large

4 | size | medium

modelos_atributos

1 | 1 | 1

2 | 1 | 2

3 | 1 | 3

4 | 2 | 2

5 | 2 | 4

For the query id_modelo=1, the result will be:

Color: red

Color: blue

Thanks!

5

I think I solved…

works with:


'condition'=>'modelos_atributos.id_modelo='.$id.' AND tipo="color"',

and:


'condition'=>'modelos_atributos.id_modelo='.$id.' AND t.tipo="color"',

note the "" in "color".

otherwise, how can I do this query with AR?


$atributos=Atributos::model()->where('modelos_atributos')->findAll();
6

Anyone konws how do this with AR? like:


$atributos=Atributos::model()->where('modelos_atributos')->findAll();

or something similar.

Thanks!

7

Mmm? I really don’t understand. What do you want to retrieve?

8

Hi softark!

Based on this database design:

modelos

id_modelo | name | descripcion

atributos

id_atributo | type| value

modelos_atributos

id | id_modelo | id_atributo

For example:

modelos

1 | modelo1 | Este es la descripcion del modelo1

2 | modelo2 | Este es la descripcion del modelo2

3 | modelo3 | Este es la descripcion del modelo3

atributos

1 | color | red

2 | color | blue

3 | size | large

4 | size | medium

modelos_atributos

1 | 1 | 1

2 | 1 | 2

3 | 1 | 3

4 | 2 | 2

5 | 2 | 4

Ok, I need to show a dropdown with a list of "atributos" for an "id_modelo" and atributos.type=color

For example, for the query id_modelo=1, and the result will be:

  • red

  • blue

I hope this was more clear! (sorry for my english…)

9

Well you certainly need to quote ‘color’ in tipo = ‘color’, as you found out, since it is a text value.

For your AR search, You’ll need to include some CDbCriteria in your find():


$atributos=Atributos::model()->findAll(new CDbCriteria(array(

    'with' => 'modelos_atributos',

    'condition' => 'modelos_atributos.id_modelo = :id AND tipo = \'color\'',

    'params' => array(

        ':id' => $id,

    ),

)));

You will want to test to make sure that this doesn’t include attributes that don’t have an entry in the modelos_atributos table in the results though.

If you’ve got the model though, you could easily do this through relations - you could have a color attribute relation on the model:


'atributos_color' => array(self::MANY_MANY, '{{modelos_atributos}}(id_modelo, id_atributo)', 'condition' => 'tipo = \'color\''),

(it may be that you need to use ‘join’ instead if ‘condition’ for the relationship - test it!

This would then allow you to get the attributes by loading your model:


$attributes = Modelos::model()->getByPk($id)->atributos_color;
10 

'atributos_color'=>array(

                self::MANY_MANY,

                'Atributos',

                'modelos_atributos(id_modelo, id_atributo)',             

                'order'=> 'valor',

                'condition'=>'tipo="color"'

           ),


          'atributos_talla'=>array(

                self::MANY_MANY,

                'Atributos',

                'modelos_atributos(id_modelo, id_atributo)',                /

                'order'=> 'valor',

                'condition'=>'tipo="talla"'

           ),

Works fine! I did not know there was a "condition" rule in relations.

Thank you!

11

Relations are fairly well documented. Here is the list of parameters you can use, and I strongly recommend reading the whole page.