Newbie question: findAll with distinct results

rozniy · February 20, 2011, 2:22pm

Hi all,

I’ve just started with Yii for 3 weeks, and the past 3 days I have been tearing my hair out over something simple:

how to get Distinct results from a findAll() method.

My Table has "Id", "Genus", "Species", and "TextFileWithDescription".

When I use:

[color="#0000FF"]

[/color]

There are many Species in a Genus, so many entries in the table have the same Genus. The DropdownList gets many redundant Genus. I’m trying to query Distinct Genus so that the dropdown is filled with unique Genus.

I wish the Yii manuals and Class Reference file have actual examples to do this. Please help us Obi wan. I tried Googling already.

mikl · February 20, 2011, 3:04pm

You could try:




$models=Animals::model()->findAll(array(

    'select'=>'t.Genus, t.Id',

    'distinct'=>true,

));

All find*() methods also accept an array with CDbCriteria properties.

rozniy · February 20, 2011, 4:27pm

Thanks for the quick reply.

It strangely did not work though.

My table is:

Id Genus Species Description

1 Canis Vulpes Fox

2 Canis Lupus Wolf

3 Canis Dirus DireWolf

4 Felis Catus HouseCat

5 Ursus Maritimus PolarBear

6 Ursus Americanus AmericanBear

7 Panthera Tigris Tiger

When I tried the above code you provided, the dropdown only gave me "Panthera".

mikl · February 20, 2011, 4:43pm

Can you show the generated SQL? (Configure a CWebLogroute and set YII_DEBUG)

rozniy · February 20, 2011, 4:54pm

(Bear in mind I’m a newbie )

Is this what we’re looking for (generated “Application Log”)?


Timestamp 	Level 	Category 	Message

16:49:20.028965 	trace 	system.CModule 	


Loading "log" application component

in D:\yiitesting\site1\index.php (13)


16:49:20.033382 	trace 	system.CModule 	


Loading "request" application component

in D:\yiitesting\site1\index.php (13)


16:49:20.038942 	trace 	system.CModule 	


Loading "urlManager" application component

in D:\yiitesting\site1\index.php (13)


16:49:20.051378 	trace 	system.web.filters.CFilterChain 	


Running filter AnimalsController.filteraccessControl()

in D:\yiitesting\site1\index.php (13)


16:49:20.052905 	trace 	system.CModule 	


Loading "user" application component

in D:\yiitesting\site1\index.php (13)


16:49:20.055654 	trace 	system.CModule 	


Loading "session" application component

in D:\yiitesting\site1\index.php (13)


16:49:20.120265 	trace 	system.CModule 	


Loading "db" application component

in D:\yiitesting\site1\protected\models\Animals.php (20)

in D:\yiitesting\site1\protected\views\animals\dumdum.php (9)

in D:\yiitesting\site1\protected\controllers\AnimalsController.php (210)


16:49:20.122648 	trace 	system.db.CDbConnection 	


Opening DB connection

in D:\yiitesting\site1\protected\models\Animals.php (20)

in D:\yiitesting\site1\protected\views\animals\dumdum.php (9)

in D:\yiitesting\site1\protected\controllers\AnimalsController.php (210)


16:49:20.135356 	trace 	system.db.CDbCommand 	


Querying SQL: SHOW COLUMNS FROM `animals`

in D:\yiitesting\site1\protected\models\Animals.php (20)

in D:\yiitesting\site1\protected\views\animals\dumdum.php (9)

in D:\yiitesting\site1\protected\controllers\AnimalsController.php (210)


16:49:20.141094 	trace 	system.db.CDbCommand 	


Querying SQL: SHOW CREATE TABLE `animals`

in D:\yiitesting\site1\protected\models\Animals.php (20)

in D:\yiitesting\site1\protected\views\animals\dumdum.php (9)

in D:\yiitesting\site1\protected\controllers\AnimalsController.php (210)


16:49:20.141720 	trace 	system.db.ar.CActiveRecord 	


Animals.findAll()

in D:\yiitesting\site1\protected\views\animals\dumdum.php (9)

in D:\yiitesting\site1\protected\controllers\AnimalsController.php (210)

in D:\yiitesting\site1\index.php (13)


16:49:20.146147 	trace 	system.db.CDbCommand 	


Querying SQL: SELECT DISTINCT t.Genus, t.Id FROM `animals` `t`

in D:\yiitesting\site1\protected\views\animals\dumdum.php (9)

in D:\yiitesting\site1\protected\controllers\AnimalsController.php (210)

in D:\yiitesting\site1\index.php (13)


16:49:20.156436 	trace 	system.CModule 	


Loading "widgetFactory" application component

in D:\yiitesting\site1\protected\views\layouts\column2.php (1)

in D:\yiitesting\site1\protected\controllers\AnimalsController.php (210)

in D:\yiitesting\site1\index.php (13)


16:49:20.165560 	trace 	system.CModule 	


Loading "clientScript" application component

in D:\yiitesting\site1\protected\controllers\AnimalsController.php (210)

in D:\yiitesting\site1\index.php (13)

rozniy · February 20, 2011, 5:05pm

OK nevermind, I found my mistake. It was in the ListData(). I was supposed to put ListData($model, ‘Genus’, ‘Genus’); not ListData($model, ‘Genus’, ‘Id’)…

And it gives the Distinct answers.

Truly a newbie mistake.

But thank you for your time.

mikl · February 20, 2011, 5:17pm

Glad to hear that you solved it.

BTW the application log is a true helper when you debug SQL queries (and more!). In your case you see that this SQL was generated:


Querying SQL: SELECT DISTINCT t.Genus, t.Id FROM `animals` `t`

So if you still had a problem you could use e.g. phpMyAdmin to test the generated SQL.

rozniy · February 20, 2011, 7:47pm

If you don’t mind another question…

I’m trying to submit the data by clicking on the Genus, and a text is supposed to appear under the dropdownlist indicating my choice. But I’m not sure how to go about it. (The php file is called dumdum, I wanted the text to appear in the same file.)




$choice="";

echo CHtml::dropDownList('Animals', $choice, $list, 'submit'=>'dumdum' ) ;

if(isset($_POST['choice']))

		{

		echo "<br>" . $choice;

		}

That didn’t work, nothing came out.

Actually I am puzzled how to pass the information (the Genus string) from the form to anywhere. The code generated by Gii does pass information about the Animals model well enough.

mikl · February 21, 2011, 7:45am

Not sure, what you try to achieve, but some notes:

Your code should submit to a controller action. If you’re using custom PHP, you’re leaving Yii and thus are a little on your own
The dropdown list will submit the Id of an Animal - so you need to retrieve the according name from DB after submission

firefox360 · June 1, 2015, 2:13pm

Hi,

I want to ask something smimilar. I have a link-table between poule, team and match. The results of this table are:

|Poule | Team | Match|

| 1 | 1 | 1 |

| 1 | 2 | 1 |

| 1 | 3 | 2 |

| 1 | 4 | 2 |

| 1 | 1 | 3 |

| 1 | 3 | 3 |

| 1 | 2 | 4 |

| 1 | 4 | 4 |

| 2 | 1 | 5 |

| 2 | 2 | 5 |

| 2 | 3 | 6 |

| 2 | 4 | 6 |

| 2 | 1 | 7 |

| 2 | 3 | 7 |

| 2 | 2 | 8 |

| 2 | 4 | 8 |

etc.

            &#036;pouleVar = Poule::model()-&gt;findByAttributes(array('poule_name' =&gt; 'CupD-A'));


	&#036;theTeamList = PouleTeamWedstrijden::model()-&gt;findAllByAttributes(array('poule_id' =&gt; &#036;pouleVar-&gt;poule_id,));


	&#036;this-&gt;render('index',array(


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


	));

View:

    foreach (&#036;model as &#036;record){


 echo &quot;&lt;tr&gt;&lt;td&gt;&quot;;


 print_r( &#036;record-&gt;team_id );


 echo&quot;&lt;/td&gt;&lt;/tr&gt;&quot;;


 echo &quot;&lt;br/&gt;&quot;;


}

It’s obvious that I get more then one team when I print the result in the View. Because of the fact that the team_id is more then once in the table. What should I do to get every team_id once?