Count Unique records with relations()?

Yii 1.1 General Discussion
1

Hi,

I’m trying to use relations() to get the number of rows (using self::STAT) with specific conditions.

For example, something like,




'ignoredEmails'=>array(self::STAT, 'EmailReport', 'campaign_id', 'condition'=>'activity_type="suspended"')

I now want to get the count of unique records. There is another column along with activity_type named ‘target_id’. And the result should be the count of rows, where ‘activity_type’=‘suspended’ and ‘target_id’ is unique i.e. the rows should have the same value in their ‘target_id’ column.

How can this be achieved with the relations() method i.e. without having to make write method to do that?

2 



'ignoredEmails'=>array(self::STAT, 'EmailReport', 'campaign_id', 'condition'=>'activity_type="suspended"','group' => 'target_id')

if I understand correctly what you need than group by is the answer. see the code above.

3

No, that’s not what I was looking for.

This gives me unexpected results. If, for example, there are 3 rows with ‘activity_type=suspended’, two of them have their ‘target_id=foo’ and one of them has ‘target_id=bar’. I should get ‘2’ as the answer when I output ‘$object->ignoredEmails’. In the example that you provided, I’m getting ‘1’.

If all the three have the same ‘target_id’ then the answer should be ‘1’. If all the three have different ‘target_id’, the answer should be ‘3’.

I’m looking to try something with ‘SELECT DISTINCT …’

4

now I see what you mean. I think this won’t be possible by a STAT relation. Instead of that I would create a scope with the appropriate criteria.

5

Okay. Can you please elaborate that? Maybe I can use it.

6

I guess your msql should look like this:

[sql]SELECT count( DISTINCT target_id )

FROM EmailReport

WHERE activity_type = ‘suspended’[/sql]

if that’s correct than you can set the select property of the stat relation like this:


'ignoredEmails'=>array(self::STAT, 'EmailReport', 'campaign_id', 'condition'=>'activity_type="suspended"','select' => 'count( DISTINCT target_id )')

hope it will work like you expect

7

Awesome! Worked like a charm. Thanks Greg!

8

I was using ‘SELECT DISTINCT target_id’ in a separate function earlier and doing ‘count()’ on the array returned by CDbCommand->readAll(). My question is, using relations(), why do I get an error if I use only ‘select’ => ‘DISTINCT target_id’ instead of ‘select’ => ‘count( DISTINCT target_id )’, since I’m already using ‘self::STAT’? Is my question clear?

9

Because the STAT relation needs to have a “count” select at the executed SQL. DISTINCT target_id would return all the rows with the unique target_id instead of the numbers of the rows. If you see what I mean :).