Yii 1.1.14 SELECT query parameter is set as string not integer in ActiveRecord relation

Hi!

Basically I have one ActiveRecord that has relation inside of it.




	public function relations()

	{

		return array_merge(

			parent::relations(),

			array(

				'linkCount' => array(self::STAT, 'StatisticClicksLink', 'group_id'),

			)

		);

	}



Which then generates this query




SELECT `t`.`group_id` AS `c`, COUNT(*) AS `s` FROM `statistic_clicks_link` `t` WHERE (`t`.`group_id`='665') GROUP BY `t`.`group_id`



and explain gives this:




mysql> EXPLAIN SELECT `t`.`group_id` AS `c`, COUNT(*) AS `s` FROM `statistic_clicks_link` `t` WHERE (`t`.`group_id`='665') GROUP BY `t`.`group_id`;

+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------------------------------------+

| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                                                     |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------------------------------------+

|  1 | SIMPLE      | t     | ref  | group_id      | group_id | 4       | const |    3 | Using where; Using index; Using temporary; Using filesort |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------------------------------------+



but if i change the group_id ‘665’ to 665 then this is what EXPLAIN gives:




mysql> EXPLAIN SELECT `t`.`group_id` AS `c`, COUNT(*) AS `s` FROM `statistic_clicks_link` `t` WHERE (`t`.`group_id`=665) GROUP BY `t`.`group_id`;

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

|  1 | SIMPLE      | t     | ref  | group_id      | group_id | 4       | const |    3 | Using index |

+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+



I think that’s a huge difference between


Using where; Using index; Using temporary; Using filesort

and


Using index

statistic_clicks_link table:




CREATE TABLE `statistic_clicks_link` (

	`id` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,

	`group_id` INT(10) UNSIGNED NOT NULL,

	`hash` VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8_bin',

	`title` VARCHAR(256) NOT NULL,

	`link` VARCHAR(2048) NOT NULL,

	`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

	`http_code` INT(3) UNSIGNED NOT NULL DEFAULT '301',

	PRIMARY KEY (`id`),

	INDEX `group_id` (`group_id`, `link`(255)),

	INDEX `U_hash` (`hash`) USING BTREE,

	CONSTRAINT `FK_statistic_clicks_link_group_id` FOREIGN KEY (`group_id`) REFERENCES `statistic_clicks_group` (`id`) ON UPDATE CASCADE ON DELETE CASCADE

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB

;



So I am wondering if there is this one case then how many other cases are there that have such difference.

How to tell Yii to set parameter according to the type in database not the way thats easier for Yii (all strings)?

MySQL 5.5.31

Yii 1.1.14

bump

For 3 months no one has been able to give a single answer or suggestion? =/

So… after 3 months you still wait for an answer? You are not very dedicated coder, are you?

Anyway, sometimes new topic slips through without answers. Especially when it’s about Yii 1.1.

Yii 1.1 casts MySQL UNSIGNED INT as string https://github.com/yiisoft/yii/blob/master/framework/db/schema/mysql/CMysqlColumnSchema.php

[color="#006400"]/* Moved from Bug discussions */

[color="#000000"]Bug discussions is not a place to discuss bugs in your code…[/color][/color]

I am new to programming and especially Yii framework, I have no clue what I should be doing. =(

I understand the reason for this when you’re retrieving value from mysql into php variable, but not when you’re creating query that has a column with int data type.

Also I wouldn’t have created this thread if it didn’t cause any issues with indexes.

Am i supposed to modify Yii framework to change how queries are created to fix this index problem?

No, never modify the core files directly.

When the model is populated from db all its attributes are filled with value of type as specified by Yii according to its db type. This value is used then with your query and that is why it’s string.

Remove the unsigned type in your db, flush the app’s cache (if you are using it) and retest this query. It should be proper integer if I’m right (but I might be wrong, not the first nor last time :P)

By the way - if you are beginning your journey with Yii why not start already with Yii 2?