findAllBySql, which fields to select?

Yii 1.1 General Discussion
1

Hi there,

I’ve made this code some time:




User::model()->findAllBySql('

	SELECT DISTINCT `users`.`id` FROM `users`

	LEFT JOIN `threadChilds` ON (`threadChilds`.`userId` = `users`.`id`)

	LEFT JOIN `threads` ON (`threadChilds`.`threadId` = `threads`.`id`)

	WHERE (`threads`.`id` = :threadId OR `users`.`id` = :threadStarter)

' . (empty($excludeIds) ? '' : ' AND `users`.`id` NOT IN (:excludeIds)'),$params)

But it resulted in: "Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 76 bytes) in yiilite.php on line 679"

Then i tried:




User::model()->findAllBySql('

	SELECT DISTINCT `users`.* FROM `users`

	LEFT JOIN `threadChilds` ON (`threadChilds`.`userId` = `users`.`id`)

	LEFT JOIN `threads` ON (`threadChilds`.`threadId` = `threads`.`id`)

	WHERE (`threads`.`id` = :threadId OR `users`.`id` = :threadStarter)

' . (empty($excludeIds) ? '' : ' AND `users`.`id` NOT IN (:excludeIds)'),$params)

which works just fine.

Can anybody tell me why? There’s no reason to select all fields, when I only need the ID?

Thanks.

2

Selecting DISTINCT * doesn’t make any sense…

Also, you should try building the query using model methods for the ‘IN’ clause.

My #1 suggestion is to increase your PHP memory size to 256M or 384M (in php.ini). It will fix your issue.

3

The reason i use distinct, is because, i only want mySQL to give me one row per user, else it would give me several.

It still doesn’t explain why one query takes up more memory than the other. Why should i use a more expensive solution?