yii 2.02 ActiveRecord::findBySql(...)->count() - wrong result

Archives Bug Discussions
1

I found a strange behaviour of yii 2.02 when using [font="Courier New"]count()[/font] on the result of an [font="Courier New"]ActiveRecord::findBySql(…)[/font]

This is my code:


            $changedTranslations = Translation::findBySql("SELECT * FROM translation t ".

                "JOIN translation_key tk ON tk.id=t.TRANSLATION_KEY_ID ".

                "JOIN source s ON tk.SOURCE_ID=s.ID ".

                "WHERE STATUS IN ('E', 'R') " .

                "AND tk.SOURCE_ID=" . $source->ID)->count();

I can see in the logs the executed sql:

[sql]SELECT t.*

FROM translation t

JOIN translation_key tk ON tk.id = t.TRANSLATION_KEY_ID

JOIN source s ON tk.SOURCE_ID = s.ID

WHERE STATUS IN (‘E’, ‘R’)[/sql]

…which is pretty much the same.

The result of this statement in the database is:


ID	TRANSLATION_KEY_ID	LANGUAGE_ID	TRANSLATION_VALUE	TRANSLATION_SOURCE_VALUE	TRANSLATION_CONFLICT_VALUE	STATUS	

1297	1 [->]	1 [->] Portal Portal	NULL	E

1	1 [->]	2 [->] Portal Portal	NULL	E

2588	1 [->]	3 [->]	Portail	Portail	NULL	E

3796	1 [->]	4 [->]	Portale	Portale	NULL	E

I would now expect yii to return 4, since there are only 4 records matching this sql. But unfortunally, [font="Courier New"]count()[/font] returns 1297, which is the first column of the first result row.

My solution now is executing this code (adding a [font="Courier New"]COUNT(*)[/font] to the [font="Courier New"]SELECT[/font]):


$changedTranslations = Translation::findBySql("SELECT COUNT(*) FROM translation t ".

                "JOIN translation_key tk ON tk.id=t.TRANSLATION_KEY_ID ".

                "JOIN source s ON tk.SOURCE_ID=s.ID ".

                "WHERE STATUS IN ('E', 'R') " .

                "AND tk.SOURCE_ID=" . $source->ID)->count();

This returns the originally expected 4, but it seems to be somehow wrong to use the [font="Courier New"]COUNT(*)[/font] in the sql and the [font="Courier New"]count()[/font] from the yii framework as well.

If someone can enlighten me, I would be very happy…

Thanks & Regards - Silegio.

2

silegio, from YII2 documentation

3

Thanks for your reply. This might be related to this problem and this hint is also missing in the CActiveRecord-Documentation. The doc about count() as well has no info about this.

But I think the solution might be easy, I might provide a very simple patch in the next days for that.

4

silegio, you are reading the documentation for yii 1.1, but using yii 2. The documentation for yii 2 is here http://www.yiiframework.com/doc-2.0/index.html

5

You have said there query is pretty much the same but the first query has addtional


(...) "AND tk.SOURCE_ID=" . $source->ID (...)

which is missed from second query.

Your original code is:


            $changedTranslations = Translation::findBySql("SELECT * FROM translation t ".

                "JOIN translation_key tk ON tk.id=t.TRANSLATION_KEY_ID ".

                "JOIN source s ON tk.SOURCE_ID=s.ID ".

                "WHERE STATUS IN ('E', 'R') " .

                "AND tk.SOURCE_ID=" . $source->ID)->count();

What result will you have when you run this below:


            $changedTranslations = count(Translation::findBySql("SELECT * FROM translation t ".

                "JOIN translation_key tk ON tk.id=t.TRANSLATION_KEY_ID ".

                "JOIN source s ON tk.SOURCE_ID=s.ID ".

                "WHERE STATUS IN ('E', 'R') " .

                "AND tk.SOURCE_ID=" . $source->ID));

Mario